Get rid of it. You may even simplify the parser to directly transform
'x BETWEEN a AND b' to 'x >= a AND x <= b'. Fewer structures and less
work in transformation/optimization stages...
Roy
Jay Pipes wrote:
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
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
