List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 23 2013 11:08pm
Subject:RE: Bug in BETWEEN same DATETIME
View as plain text  
Watch out for CAST(), DATE(), and any other function.  In a WHERE clause, if you hide an
indexed column inside a function, the index cannot be used for optimization.

  INDEX(datetime_col)
  ...
  WHERE DATE(datetime_col) = '2013-01-01'
will not use the index!

The workaround is messy, but worth it (for performance):
  WHERE datetime_col >= '2013-01-01'
    AND datetime_col <  '2013-01-01' + INTERVAL 1 DAY
(or any of a zillion variants)

(Yeah, it seems like the optimizer could do the obvious transformation for you.  Hint,
hint, Shawn.)

> -----Original Message-----
> From: shawn green [mailto:shawn.l.green@stripped]
> Sent: Thursday, May 23, 2013 3:50 PM
> To: mysql@stripped
> Subject: Re: Bug in BETWEEN same DATETIME
> 
> 
> 
> On 5/23/2013 4:55 PM, Daevid Vincent wrote:
> > I just noticed what I consider to be a bug; and related, has this
> been
> > fixed in later versions of MySQL?
> >
> > We are using:
> > mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
> > 5.2
> >
> > If you use BETWEEN and the same date for both parts (i.e. you want a
> > single
> > day) it appears that the operator isn't smart enough to consider the
> > full day in the cases where the column is a DATETIME
> >
> > http://dev.mysql.com/doc/refman/5.0/en/comparison-
> operators.html#opera
> > tor_be
> > tween
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
> >
> > I actually have to format it like this to get results
> >
> > WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-
> 16
> > 11:59:59'
> >
> 
>  From the Fine Manual...
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-
> conversion.html
> ###############
>   Conversion of DATE values:
> 
>      Conversion to a DATETIME or TIMESTAMP value adds a time part of
> '00:00:00' because the DATE value contains no time information.
> ...
>   Prior to MySQL 5.0.42, when DATE values are compared with DATETIME
> values, the time portion of the DATETIME value is ignored, or the
> comparison could be performed as a string compare. Starting from MySQL
> 5.0.42, a DATE value is coerced to the DATETIME type by adding the time
> portion as '00:00:00'. To mimic the old behavior, use the CAST()
> function to cause the comparison operands to be treated as previously.
> For example:
> 
> date_col = CAST(datetime_col AS DATE)
> 
> ###############
> 
> That seems pretty clear to me as not a bug.
> --
> Shawn Green
> MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
> and Software, Engineered to Work Together.
> Office: Blountville, TN
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

Thread
Bug in BETWEEN same DATETIMEDaevid Vincent23 May
  • RE: Bug in BETWEEN same DATETIMETimothy R Peterson23 May
    • Re: Bug in BETWEEN same DATETIMEMichael Dykman23 May
      • RE: Bug in BETWEEN same DATETIMERick James23 May
  • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
    • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
  • Re: Bug in BETWEEN same DATETIMEshawn green23 May
    • RE: Bug in BETWEEN same DATETIMERick James23 May
      • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • RE: Bug in BETWEEN same DATETIMERick James24 May
          • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • Re: Bug in BETWEEN same DATETIMEhsv24 May
          • RE: Bug in BETWEEN same DATETIMERick James29 May