| Bug #12266 | GROUP BY expression on DATE column gives wrong results | ||
|---|---|---|---|
| Submitted: | 29 Jul 2005 12:12 | Modified: | 7 Aug 2005 1:39 |
| Reporter: | Valeriy Kravchuk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.12-nt, 4.1.13, 4.1.14 | OS: | Linux (Linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[4 Aug 2005 14:05]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/27895
[4 Aug 2005 14:05]
Evgeny Potemkin
When temporary field created for DATE(LEFT(column,8)) expression, max_length value is taken from Item_date_typecast, and it is getting it from underlaid Item_func_left and it's max_length is 8 in given expression. And all this results in stripping last 2 digits.
[4 Aug 2005 21:05]
Evgeny Potemkin
Fixed in 4.1.14, cset 1.2352.14.1
[7 Aug 2005 1:39]
Mike Hillyer
Documented in 4.1.14 changelog: <listitem><para> Performing <literal>DATE(LEFT(column,8))</literal> on a <literal>DATE</literal> column produces incorrect results. (Bug #12266) </para></listitem>
Description: Simple bug was found when trying to reproduce customer's issue (related to grouping by DATE column). Looks like date(left(<date column>+0, 8)) expression evaluates erroniously when used in GROUP BY list. How to repeat: Create table with date column: CREATE TABLE i6072 ( c1 char(3) NOT NULL default '', c2 date NOT NULL default '0000-00-00', c3 time NOT NULL default '00:00:00', c4 char(1) NOT NULL default '', c5 varchar(13) NOT NULL default '', c6 char(3) NOT NULL default '', c7 char(2) NOT NULL default '', c8 char(1) NOT NULL default '', other char(30), primary key (c1, c2, c3, c4, c5, c6, c7, c8)) engine=MyISAM; Populate it with some data: INSERT INTO i6072 VALUES("001", "2005-06-06", "20:30:00", "I", "0101010102", "000", "01", "0", "other 1"); INSERT INTO i6072 VALUES("001", "2005-06-06", "20:30:00", "I", "0101010302", "000", "01", "0", "other 2"); INSERT INTO i6072 VALUES("001", "2005-06-07", "11:30:00", "I", "04020101", "000", "01", "0", "other 3"); INSERT INTO i6072 VALUES("001", "2005-06-07", "10:30:00", "I", "04020101", "000", "01", "0", "other 4"); Then try: mysql> select c1,c2 from i6072 group by 1,2; +-----+------------+ | c1 | c2 | +-----+------------+ | 001 | 2005-06-06 | | 001 | 2005-06-07 | +-----+------------+ 2 rows in set (0.00 sec) mysql> select c1,left(c2+0,8) from i6072 group by 1,2; +-----+--------------+ | c1 | left(c2+0,8) | +-----+--------------+ | 001 | 20050606 | | 001 | 20050607 | +-----+--------------+ 2 rows in set (0.00 sec) mysql> select c1,date(left(c2+0,8)) from i6072; +-----+--------------------+ | c1 | date(left(c2+0,8)) | +-----+--------------------+ | 001 | 2005-06-06 | | 001 | 2005-06-06 | | 001 | 2005-06-07 | | 001 | 2005-06-07 | +-----+--------------------+ 4 rows in set (0.01 sec) mysql> select c1,date(left(c2+0,8)) from i6072 group by 1,2; +-----+--------------------+ | c1 | date(left(c2+0,8)) | +-----+--------------------+ | 001 | 2005-06- | +-----+--------------------+ 1 row in set (0.00 sec) Just wanted to get properly formatted results... This is a BUG. Suggested fix: It seems that fucntions are evaluated in wrong order here (date(c2+0) and then left(...,8)