I am not sure if the database forum is the right place for this topic, but it might be.
I am selecting from a view which contains several OLAP functions. If the JDBC connection is set to Teradata Transaction Mode (BTET) , I receive the correct result set (about 30.000 rows). Switching to ANSI transaction mode in the JDBC settings causes that only 218 rows are returned, which is wrong.
The execution plan is in both cases exactly the same (only that the ANSI Mode has an additional "ET" step at the end, which seem logical).
I was going deeper into details, checking the query log. While all estimations are almost identical, the Result Row Count reflects the differences (30.000 vs. 218).
At first, i thought it might be a bug in the JDBC driver, but as the QueryLog shows as well a different number of result rows i am confused. Maybe i am wrong, but if Teradata returns only 218 rows, having the same execution plan, could this be a Teradata bug? Or could it still be the JDBC driver?
Thanks in advance
The most common cause of a query returning a different number of rows in ANSI mode versus TERA mode is due to character comparison operations in the query's WHERE clause, such as the equal ( = ) operator and the LIKE predicate.
In ANSI mode, character comparisons are typically case-sensitive, whereas in TERA mode, character comparisons are typically case-insensitive.
To ensure that the query returns the same results in both ANSI and TERA modes, you must explicitly specify whether you want each character comparison to be case-sensitive or case-insensitive.
For example, to force a case-sensitive comparison regardless of mode:
select * from my table where VarcharColumn1 (casespecific) = 'Some literal' (casespecific)
For example, to force a case-insensitive comparison regardless of mode:
select * from my table where VarcharColumn1 (not casespecific) = 'Some literal' (not casespecific)