MySQL Bugs: #12266: GROUP BY expression on DATE column gives wrong results

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:	None
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

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)

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

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.

Fixed in 4.1.14, cset 1.2352.14.1

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>