Hi all, Unbeknownst to most (including myself until tonight when I was re-enabling test cases in Drizzle), there is a MySQL SQL_MODE called 'high_not_precedence' which affects the behaviour of NOT comparisons in the following way (shown from MySQL 5.0.67):
mysql> create table t1 (a int); Query OK, 0 rows affected (0.07 sec) mysql> insert into t1 values (0),(1),(NULL); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> set sql_mode='high_not_precedence'; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where not a between 2 and 3; Empty set (0.00 sec) mysql> set sql_mode=default; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where not a between 2 and 3; +------+ | a | +------+ | 0 | | 1 | +------+ 2 rows in set (0.00 sec) Technically, the SQL Standard calls for NOT to have high precedence, meaning that the evaluation of "NOT a" occurs before the filter on whether the result of that evaluation is between 2 and 3. The previous MySQL behaviour, as shown above, applies the NOT to the filter on BETWEEN 2 and 3. Currently, Drizzle implements the *old* behaviour (i.e. non-SQL standard) behaviour. Should we keep it this way or switch to the SQL standard way? The code shouldn't be too much of a change. Here are the options, though: If we stick with the current behaviour of Drizzle, there is a simple workaround to enable the SQL Standard behaviour: use parentheses around the "NOT a" in the example above. If we implement the SQL standard behaviour in Drizzle, there is no workaround to enable the old behaviour. Thoughts and votes on this are appreciated, even if that though is "I couldn't care less; people should learn to write SQL in a manner that is not idiotic." Cheers, Jay _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
