Roy Lyseng wrote: > 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...
I second that. Just my few cents, Mats Kindahl > > 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 -- Mats Kindahl Lead Software Developer Replication Team MySQL AB, www.mysql.com
begin:vcard fn:Mats Kindahl n:Kindahl;Mats org:Sun Microsystems adr;quoted-printable:;;Tegv=C3=A4gen 3;Storvreta;SE;74334;Sweden email;internet:[EMAIL PROTECTED] title:Lead Replication Software Developer x-mozilla-html:FALSE version:2.1 end:vcard
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
