>>>>> "Vivek" == Vivek Khera <khera@stripped> writes:
>>>>> "MW" == Michael Widenius <monty@stripped> writes:
MW> What may confuse someone is that the simple operators: >=, >, = , <
MW> and <= does some 'extra magic' when one compares a 'date/datetime/time
MW> type' column to a constant strings to make date handling easier. We
MW> didn't add this behaviour to the BETWEEN clause as it's harder to make
MW> a simple rule when one have 3 arguments to take care compared to two
MW> arguments.
Vivek> So, when one wants to do a "between" with dates, which is the
Vivek> preferred form? Below, date_value is a column of type DATE.
Vivek> a)
Vivek> where date_value BETWEEN '1999-05-01' AND '1999-05-31'
Vivek> b)
Vivek> where date_value <= '1999-05-31' AND date_value >= '1999-05-01'
Vivek> Which one does the faster compares?
a) (because the first argument is only evaluated once)
Vivek> Why not just have the parser
Vivek> internally rewrite form (a) as (b) then apply the necessary rules for
Vivek> optimizing date/time compares?
The problem is that according to the ODBC (and probably ANSI SQL)
rules, it's the first argument in BETWEEN that decides how the
comparison is done. (ODBC/ANSI SQL only have to handle the automatic
translation between float/integer; MySQL has the additional 'burden' of
automatic string/date/number translations, but it follows the same
rules in this case)
The problem is not implementing it; The problem is to not have too
many conflicts with the SQL standard.
Regards,
Monty
