I concur. On Thu, Sep 11, 2008 at 6:34 PM, Chris Esquibel <[EMAIL PROTECTED]>wrote:
> I am new to the thread, but I think if the goal of Drizzle is aimed at > being optimized and accurate, we should follow the SQL standard. > Optimize the DB, not the user. > > 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<https://launchpad.net/%7Edrizzle-discuss> > > Post to : [email protected] > > Unsubscribe : > > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > > More help : https://help.launchpad.net/ListHelp > > > > _______________________________________________ > Mailing list: > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > Post to : [email protected] > Unsubscribe : > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > More help : https://help.launchpad.net/ListHelp > -- val tirman [EMAIL PROTECTED]
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
