| Bug #29655 | GREATEST()/LEAST() functions drop date results in between join | ||
|---|---|---|---|
| Submitted: | 9 Jul 2007 18:24 | Modified: | 13 Jul 2007 17:24 |
| Reporter: | John Miller | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0.42 | OS: | Linux (gentoo amd64) |
| Assigned to: | CPU Architecture: | Any | |
[9 Jul 2007 18:40]
MySQL Verification Team
Thank you for the bug report. I was not able to repeat with source server:
[miguel@light 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.46-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t1(a date, b date)
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values ('2007-03-01', '2007-04-01')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> create table t2(c date)
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values ('2007-03-01'), ('2007-04-01')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> -- returns 1 row
mysql> select * from t1
-> join t2 on c = a
-> ;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| 2007-03-01 | 2007-04-01 | 2007-03-01 |
+------------+------------+------------+
1 row in set (0.00 sec)
mysql> -- returns 1 row
mysql> select * from t1
-> join t2 on c = b
-> ;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| 2007-03-01 | 2007-04-01 | 2007-04-01 |
+------------+------------+------------+
1 row in set (0.00 sec)
mysql> -- returns no rows
mysql> select * from t1
-> join t2 on c = least(a, b)
-> ;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| 2007-03-01 | 2007-04-01 | 2007-03-01 |
+------------+------------+------------+
1 row in set (0.00 sec)
mysql>
[10 Jul 2007 1:33]
John Miller
I have reproduced it on three separate servers: an AMD Sempron, Intel Core Duo, and a VMWare virtual server all running Gentoo 64-bit. Was not able to reproduce it on 5.0.41 Windows 32 bit (from binaries). I may be able to test 5.0.42 on a Gentoo 32 bit install in the next couple of days.
[10 Jul 2007 2:17]
MySQL Verification Team
Thank you for the feedback. Still I am not able to repeat on Suse 10.2 64-bit:
miguel@luar:~/dbs> cd 5.0
miguel@luar:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t1(a date, b date)
-> ;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t1 values ('2007-03-01', '2007-04-01')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> create table t2(c date)
-> ;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t2 values ('2007-03-01'), ('2007-04-01')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> -- returns 1 row
mysql> select * from t1
-> join t2 on c = a
-> ;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| 2007-03-01 | 2007-04-01 | 2007-03-01 |
+------------+------------+------------+
1 row in set (0.02 sec)
mysql> -- returns 1 row
mysql> select * from t1
-> join t2 on c = b
-> ;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| 2007-03-01 | 2007-04-01 | 2007-04-01 |
+------------+------------+------------+
1 row in set (0.00 sec)
mysql> -- returns no rows
mysql> select * from t1
-> join t2 on c = least(a, b)
-> ;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| 2007-03-01 | 2007-04-01 | 2007-03-01 |
+------------+------------+------------+
1 row in set (0.00 sec)
mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.0.46-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
miguel@luar:~/dbs/5.0> file libexec/mysqld
libexec/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.4, statically linked, for GNU/Linux 2.6.4, not stripped
miguel@luar:~/dbs/5.0>
[13 Jul 2007 17:24]
John Miller
More testing: 5.0.40 32 bit and 64 bit - does not occur 5.0.42 32 bit and 64 bit - does occur 5.0.44 32 bit and 64 bit - does not occur Looks like it was specific to .42, so I'll upgrade to the .44. Thanks
Description: Using least or greatest in the on clause (see how to repeat) comparing a value in one table to the least or greatest of values in the other table fails to return rows. Using a left join to inspect the least (select *, least(a, b) from t1 left join t2 on c = least(a, b)) shows that least is being calculated correctly but the join is not working. The same code works correctly in 5.0.38. Sorry if this is duplicate. I couldn't search for bugs in the least function. The search page ignores that word. How to repeat: create table t1(a date, b date) ; insert into t1 values ('2007-03-01', '2007-04-01') ; create table t2(c date) ; insert into t2 values ('2007-03-01'), ('2007-04-01') ; -- returns 1 row select * from t1 join t2 on c = a ; -- returns 1 row select * from t1 join t2 on c = b ; -- returns no rows select * from t1 join t2 on c = least(a, b) ;