| Bug #39283 | Date returned as VARBINARY to client for queries with COALESCE and JOIN | ||
|---|---|---|---|
| Submitted: | 5 Sep 2008 22:41 | Modified: | 10 Nov 2008 20:09 |
| Reporter: | Lawrence Holtsclaw | ||
| Status: | Closed | ||
| Category: | 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 | Target Version: | 5.0+ |
| Tags: | result, date, COALESCE, varbinary, types | ||
| Triage: | Triaged: D2 (Serious) | ||
[5 Sep 2008 22:45]
Lawrence Holtsclaw
Test case source code ('C')
Attachment: bugtest.c (text/x-csrc), 3.43 KiB.
[6 Sep 2008 15:36]
Sveta Smirnova
Thank you for the report. Verified as described. Workaround: remove ORDER BY baddate
[6 Sep 2008 16: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 22: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 23: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 9: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 16:13]
Gleb Shchepa
Please ignore previous commentary.
[3 Oct 2008 16: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 16:50]
Mattias Jonsson
OK to push by me.
[6 Oct 2008 18: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 13: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 10: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 22: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 11: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 12: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 20:09]
Paul DuBois
Note in 5.1.31, 6.0.8 changelogs.
[11 Nov 2008 17:30]
Paul DuBois
6.0.9 changelog, not 6.0.8.
[19 Jan 12: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 14: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 17: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)
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?