On Thu, Sep 11, 2008 at 6:19 PM, Jay Pipes <[EMAIL PROTECTED]> 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 I vote keep previous (non standard) behavior. I think Drizzle should only break with MySQL behavior when there is a clear win, and I fail to see significant win here. -- Rob Wultsch
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
