| Bug #60217 | Bug with union and date | ||
|---|---|---|---|
| Submitted: | 23 Feb 2011 8:11 | Modified: | 24 Feb 2011 12:04 |
| Reporter: | alexandre fontfreyde | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.0.51 and 5.1.53 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[23 Feb 2011 8:11]
alexandre fontfreyde
[23 Feb 2011 9:17]
Valeriy Kravchuk
Please, check with recent versions, 5.0.92 and 5.1.55. it works for me even in 5.1.54 already:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.1.54-community MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> (SELECT SUBTIME(ADDTIME(DATE_FORMAT(current_date(), '%Y-%m-%d %H:%i:%s'),
'1
'> 6:0:0.000000'),'2 0:0:0.000000') AS TEST)
-> ;
+---------------------+
| TEST |
+---------------------+
| 2011-02-22 06:00:00 |
+---------------------+
1 row in set (0.16 sec)
mysql> (SELECT SUBTIME(SUBTIME(ADDTIME(DATE_FORMAT(current_date(), '%Y-%m-%d %H:
%i:%s'), '1
'> 6:0:0.000000'),'2 0:0:0.000000'),'1 0:0:0.000000') AS TEST)
-> ;
+---------------------+
| TEST |
+---------------------+
| 2011-02-21 06:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> (SELECT SUBTIME(ADDTIME(DATE_FORMAT(current_date(), '%Y-%m-%d %H:%i:%s'),
'1
'> 6:0:0.000000'),'2 0:0:0.000000') AS TEST)
-> UNION
-> (SELECT SUBTIME(SUBTIME(ADDTIME(DATE_FORMAT(current_date(), '%Y-%m-%d %H:
%i:%s'), '1
'> 6:0:0.000000'),'2 0:0:0.000000'),'1 0:0:0.000000') AS TEST)
-> ;
+-------------------------------+
| TEST |
+-------------------------------+
| 2011-02-22 06:00:00 |
| 2011-02-21 06:00:00 |
+-------------------------------+
2 rows in set (0.03 sec)
[23 Feb 2011 9:29]
Peter Laursen
I see the problem on 5.1.55 but not 5.5.9. On 5.5.9 result is perfect. If I wrap HEX inside each SELECT I see no difference with the two server versions! (SELECT HEX(SUBTIME(ADDTIME(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-%d %H:%i:%s'), '1 6:0:0.000000'),'2 0:0:0.000000')) AS TEST) UNION (SELECT HEX(SUBTIME(SUBTIME(ADDTIME(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-%d %H:%i:%s'), '1 6:0:0.000000'),'2 0:0:0.000000'),'1 0:0:0.000000')) AS TEST); So HEX will eliminate this bug ???? Peter (not a MySQL person)
[23 Feb 2011 10:07]
Peter Laursen
We did a little debugging on this as it affects our program (it believes that the result must be binary data). Our conclusion is the 5.1.x pads 10 (!) NULL-bytes to the result. Length of result is "29" in 5.1 and "19" in 5.5. 19 is correct. I will attach an image illustrating this.
[23 Feb 2011 10:10]
Peter Laursen
5.1 versus 5.5 column width
Attachment: diff.jpg (image/jpeg, text), 205.63 KiB.
[23 Feb 2011 10:22]
Peter Laursen
@Valeriy: In command line client you will only see the problem as empty space after the result in each line. But for other clients this may cause failures. So what client is Ekta using?
[23 Feb 2011 11:37]
Peter Laursen
To pin it out: "2011-02-22 06:00:00" is a correct result. "2011-02-22 06:00:00NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL" is an incorrect result.
[23 Feb 2011 14:22]
Peter Laursen
@Valeriy: In my opinion there is no need to ask for feedback. What feedback do you need? It is not clear at all either.
[23 Feb 2011 14:37]
Valeriy Kravchuk
Peter, Original bug reporter claimed that result set of UNION is empty. I see it is NOT empty on 5.1.54. That's why I asked original reporter to check on current version. What you later noted (extra bytes in non-empty result set of UNION) is different from what was originally reported and, I think, is a topic for a different bug report. I even think we have several related reports: http://bugs.mysql.com/bug.php?id=30886 http://bugs.mysql.com/bug.php?id=35818 http://bugs.mysql.com/bug.php?id=40217 http://bugs.mysql.com/bug.php?id=47483 http://bugs.mysql.com/bug.php?id=56350 http://bugs.mysql.com/bug.php?id=45429 (this is what you report here, by the way, verified by me long time ago) and some more. So, UNION is broken in many ways. It just does not look broken (to me) in the way described here, as I understand it (on recent versions). I want to clarify my understanding and, thus, ask user to check. Maybe my binaries are broken, who knows... P.S. If you think that you can easily do my job better than me, you know what to do: send CV to Oracle :) Or do the same job for any of MySQL forks...
[23 Feb 2011 14:40]
Peter Laursen
OK .. that explains. But personally I rather think that this happens for Ekta because his client fails to display anything with this result. (and sorry if the status should change to 'Open' again).
[23 Feb 2011 14:46]
Valeriy Kravchuk
Peter, Then your question to bug reporter: "So what client is Ekta using?" is also a valid reason for "Need feedback" status :)
[24 Feb 2011 10:06]
alexandre fontfreyde
Hi, Thanks for answer. I upgrade my MySQL version and work fine with last version. Sorry for disturbe. (for information i used phpMyAdmin client...) ------- I forgot the result of last test in my first post. I copy him now (just for information). So bad return but not empty return. The result obtain in 5.0.51 (in phpMyadmin) : (SELECT SUBTIME(ADDTIME(DATE_FORMAT(current_date(), '%Y-%m-%d %H:%i:%s'), '1 6:0:0.000000'),'2 0:0:0.000000') AS TEST) UNION (SELECT SUBTIME(SUBTIME(ADDTIME(DATE_FORMAT(current_date(), '%Y-%m-%d %H:%i:%s'), '1 6:0:0.000000'),'2 0:0:0.000000'),'1 0:0:0.000000') AS TEST) Result : TEST 323031312d30322d32332030363a30303a303000000000000000000000 323031312d30322d32322030363a30303a303000000000000000000000
[24 Feb 2011 12:04]
Valeriy Kravchuk
So, this is a duplicate of bug #45429.
