Bug #39283 Date returned as VARBINARY to client for queries with COALESCE and JOIN
Submitted: 5 Sep 2008 20:41 Modified: 10 Nov 2008 19:09
Reporter: Lawrence Holtsclaw Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.67-community, 4.1, 5.0, 5.1, 6.0 bzr OS:Linux (CentOS 5.2)
Assigned to: Gleb Shchepa
Tags: COALESCE, date, result, types, varbinary
Triage: Triaged: D2 (Serious)

[5 Sep 2008 20:41] Lawrence Holtsclaw
Description:
Some queries using COALESCE with a join and a nested sub-query return a field type of VARBINARY to the client instead of DATE. A test-case demonstrating the problem is available.

When the test-case is executed, the type of field #7 ("baddate") is returned as MYSQL_TYPE_VAR_STRING with the BINARY flag set. Removing the JOIN in the offending query will return a type of MYSQL_TYPE_NEWDATE (though one does wonder why MYSQL_TYPE_DATE isn't used here like fields #1 and #2).

How to repeat:
Compile the test-case and run it as follows:

[dholtsclaw@dev2 poc_mysql_date_bug]$ g++ bugtest.c -lmysqlclient -ggdb3
[dholtsclaw@dev2 poc_mysql_date_bug]$ ./a.out
Bugtest: Client="5.0.67", Server="5.0.67-community", Protocol=10
Field#0 "f1" from "f1", type 3, flags 36865, charset 63
Field#1 "f2" from "f2", type 10, flags 4225, charset 63
Field#2 "f3" from "f3", type 10, flags 4225, charset 63
Field#3 "f4" from "f4", type 3, flags 36865, charset 63
Field#4 "f5" from "f5", type 3, flags 36865, charset 63
Field#5 "f6" from "f6", type 253, flags 0, charset 8
Field#6 "f7" from "f7", type 253, flags 0, charset 8
Field#7 "baddate" from "baddate", type 253, flags 128, charset 63
Field#8 "COALESCE(t3.f3,t1.f8)" from "COALESCE(t3.f3,t1.f8)", type 246, flags 0, charset 63

Suggested fix:
Beefier type checking?
[5 Sep 2008 20:45] Lawrence Holtsclaw
Test case source code ('C')

Attachment: bugtest.c (text/x-csrc), 3.43 KiB.

[6 Sep 2008 13:36] Sveta Smirnova
Thank you for the report.

Verified as described.

Workaround: remove ORDER BY baddate
[6 Sep 2008 14:28] Lawrence Holtsclaw
Some additional observations:

Using this query as the source of a CREATE TABLE query results in the proper type being assigned to the column.

Wrapping the entire COALESCE in a CAST((...) AS DATE) returns the proper type but wrapping just the sub-query does not. This is probably a better workaround than removing the ORDER BY since it does not affect the results (other than fixing them, that is).
[1 Oct 2008 20:30] Gleb Shchepa
Also functions IFNULL, IF, CASE, GREATEST and LEAST are affected by this bug.

Reduces test case is:

CREATE TEMPORARY TABLE t1 (f1 INT, f2 DATE);
INSERT INTO t1 VALUES (1,'2008-01-01'), (2,'2008-01-02'), (3,'2008-01-03');
CREATE TEMPORARY TABLE t2 SELECT * FROM t1;
SELECT COALESCE(t1.f2, t2.f2) AS baddate FROM t1 
  JOIN t2 ON t2.f1 = t1.f1 ORDER BY baddate;
[1 Oct 2008 21:12] 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/commits/54991

2691 Gleb Shchepa	2008-10-02
      Bug #39283: Date returned as VARBINARY to client for queries
                  with COALESCE and JOIN
      
      The server returned to a client the VARBINARY column type
      instead of the DATE type for a result of the COALESCE,
      IFNULL, IF, CASE, GREATEST or LEAST functions if that result
      was filesorted in an anonymous temporary table during
      the query execution.
      
      For example:
        SELECT COALESCE(t1.date1, t2.date2) AS result 
          FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;
      
      
      To create a column of various date/time types in a
      temporary table the create_tmp_field_from_item() function
      uses the Item::tmp_table_field_from_field_type() method
      call. However, fields of the MYSQL_TYPE_NEWDATE type were
      missed there, and the VARBINARY columns were created
      by default.
      Necessary condition has been added.
[2 Oct 2008 7:36] Gleb Shchepa
Not only listed functions are affected (COALESCE, IFNULL etc) but any DATE column that goes through an anonymous temporary table.

Will update commentaries & tests.
[3 Oct 2008 14:13] Gleb Shchepa
Please ignore previous commentary.
[3 Oct 2008 14:14] 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/commits/55263

2691 Gleb Shchepa	2008-10-03
      Bug #39283: Date returned as VARBINARY to client for queries
                  with COALESCE and JOIN
      
      The server returned to a client the VARBINARY column type
      instead of the DATE type for a result of the COALESCE,
      IFNULL, IF, CASE, GREATEST or LEAST functions if that result
      was filesorted in an anonymous temporary table during
      the query execution.
      
      For example:
        SELECT COALESCE(t1.date1, t2.date2) AS result
          FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;
      
      
      To create a column of various date/time types in a
      temporary table the create_tmp_field_from_item() function
      uses the Item::tmp_table_field_from_field_type() method
      call. However, fields of the MYSQL_TYPE_NEWDATE type were
      missed there, and the VARBINARY columns were created
      by default.
      Necessary condition has been added.
[3 Oct 2008 14:50] Mattias Jonsson
OK to push by me.
[6 Oct 2008 16:59] 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/commits/55471

2691 Gleb Shchepa	2008-10-06
      Bug #39283: Date returned as VARBINARY to client for queries
                  with COALESCE and JOIN
      
      The server returned to a client the VARBINARY column type
      instead of the DATE type for a result of the COALESCE,
      IFNULL, IF, CASE, GREATEST or LEAST functions if that result
      was filesorted in an anonymous temporary table during
      the query execution.
      
      For example:
        SELECT COALESCE(t1.date1, t2.date2) AS result
          FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;
      
      
      To create a column of various date/time types in a
      temporary table the create_tmp_field_from_item() function
      uses the Item::tmp_table_field_from_field_type() method
      call. However, fields of the MYSQL_TYPE_NEWDATE type were
      missed there, and the VARBINARY columns were created
      by default.
      Necessary condition has been added.
[10 Oct 2008 11:31] 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/commits/56018

2702 Gleb Shchepa	2008-10-10
      Bug #39283: Date returned as VARBINARY to client for queries
                  with COALESCE and JOIN
      
      The server returned to a client the VARBINARY column type
      instead of the DATE type for a result of the COALESCE,
      IFNULL, IF, CASE, GREATEST or LEAST functions if that result
      was filesorted in an anonymous temporary table during
      the query execution.
      
      For example:
        SELECT COALESCE(t1.date1, t2.date2) AS result
          FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;
      
      
      To create a column of various date/time types in a
      temporary table the create_tmp_field_from_item() function
      uses the Item::tmp_table_field_from_field_type() method
      call. However, fields of the MYSQL_TYPE_NEWDATE type were
      missed there, and the VARBINARY columns were created
      by default.
      Necessary condition has been added.
[24 Oct 2008 8:42] Bugs System
Pushed into 5.0.72  (revid:gshchepa@mysql.com-20081010101312-7t9sew3y35au19jh) (version source revid:gshchepa@mysql.com-20081010101312-7t9sew3y35au19jh) (pib:5)
[24 Oct 2008 20:12] Paul Dubois
Noted in 5.0.72 changelog.

The server returned a column type of VARBINARY rather than DATE as
the result from the COALESCE(), IFNULL(), IF(), GREATEST(), or
LEAST() functions or CASE expression if the result was obtained using
filesort in an anonymous temporary table during the query execution.

Setting report to NDI pending push into 5.1.x., 6.0.x.
[10 Nov 2008 10:53] Bugs System
Pushed into 6.0.8-alpha  (revid:gshchepa@mysql.com-20081010101312-7t9sew3y35au19jh) (version source revid:kgeorge@mysql.com-20081010120446-ax2khl3pcsghoeew) (pib:5)
[10 Nov 2008 11:35] Bugs System
Pushed into 5.1.30  (revid:gshchepa@mysql.com-20081010101312-7t9sew3y35au19jh) (version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)
[10 Nov 2008 19:09] Paul Dubois
Note in 5.1.31, 6.0.8 changelogs.
[11 Nov 2008 16:30] Paul Dubois
6.0.9 changelog, not 6.0.8.
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:14] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)