There is avery good chance that the same query will return different results in different modes.
ANSI mode has to declared before the login of the session. Following are some of my obsrvaions for ANSI mode -
1> Transactions are always implicit.
2> Either an explicitly submitted COMMIT statement or an explicitly submitted ROLLBACK statement terminates a transaction.
3> Errors roll back only the request that causes them, not the entire transaction.
4> Control of character truncation of trailing non-blank characters causes errors.
5> By default, character comparisons are always CASESPECIFIC.
6> The default table type semantics for the CREATE TABLE statement is MULTISET
7> The two-phase commit protocol is not valid
8> Because only explicit transactions are valid, you cannot mix implicit and explicit transactions within the same script.
9> Even if you have completed the eecution of a statement within a session & if you have a lock, for ex - write lock, on a table, then the other sessions will wait unless & until that transaction commits.
10> There can only be one DDL request in a transaction, and it must be the last sequential request.
Definitely there will be many other features.