| Bug #8562 | Join using bigint unsigned columns fails | ||
|---|---|---|---|
| Submitted: | 17 Feb 2005 3:38 | Modified: | 9 Mar 2005 3:16 |
| Reporter: | Michael Benzinger | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.10 | OS: | Linux (Linux Fedora Core 3 AMD64) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[17 Feb 2005 4:21]
MySQL Verification Team
Thank you for the bug report. Below how behaves on 4.0.XX:
mysql> select * from A, B where A.value64=17156792991891826145 and
-> B.value64=17156792991891826145;
+----------------------+---------+----------------------+---------+
| value64 | value32 | value64 | value32 |
+----------------------+---------+----------------------+---------+
| 17156792991891826145 | 1 | 17156792991891826145 | 3 |
+----------------------+---------+----------------------+---------+
1 row in set (0.03 sec)
mysql> select * from A, B where A.value64=17156792991891826145 and
-> B.value64=A.value64;
+----------------------+---------+----------------------+---------+
| value64 | value32 | value64 | value32 |
+----------------------+---------+----------------------+---------+
| 17156792991891826145 | 1 | 17156792991891826145 | 3 |
+----------------------+---------+----------------------+---------+
1 row in set (0.00 sec)
mysql>
mysql> select * from A, B where A.value64= 9223372036854775807 and
-> B.value64=9223372036854775807;
+---------------------+---------+---------------------+---------+
| value64 | value32 | value64 | value32 |
+---------------------+---------+---------------------+---------+
| 9223372036854775807 | 2 | 9223372036854775807 | 4 |
+---------------------+---------+---------------------+---------+
1 row in set (0.00 sec)
mysql> select * from A, B where A.value64= 9223372036854775807 and
-> B.value64=A.value64;
+---------------------+---------+---------------------+---------+
| value64 | value32 | value64 | value32 |
+---------------------+---------+---------------------+---------+
| 9223372036854775807 | 2 | 9223372036854775807 | 4 |
+---------------------+---------+---------------------+---------+
1 row in set (0.00 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 4.0.24-debug-log |
+------------------+
1 row in set (0.00 sec)
[17 Feb 2005 13:08]
Michael Benzinger
I realize it works with 4.0.xx. Unfortunately, this is what is keeping me from moving to 4.1.x.
[28 Feb 2005 20:50]
Sergey Petrunya
New cset with post-review fixes (previous cset is recalled)
[3 Mar 2005 2:03]
Sergey Petrunya
Patch approved by Sergei
[3 Mar 2005 2:14]
Sergey Petrunya
Fix pushed into 4.1.11
[9 Mar 2005 3:16]
Paul DuBois
Noted in 4.1.11 changelog.
Description: When attempting to join two tables on columns defined as "bigint unsigned" and the value exceeds 2 to the power of 63 minus 1, the columns do not match and no rows are returned. The script below will reproduce this behavior. Here are the results: mysql> \. test64.sql Query OK, 2 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Database changed Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected, 1 warning (0.02 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.02 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) +----------------------+---------+ | value64 | value32 | +----------------------+---------+ | 9223372036854775807 | 2 | | 17156792991891826145 | 1 | +----------------------+---------+ 2 rows in set (0.00 sec) +----------------------+---------+ | value64 | value32 | +----------------------+---------+ | 9223372036854775807 | 4 | | 17156792991891826145 | 3 | +----------------------+---------+ 2 rows in set (0.01 sec) +----------------------+---------+----------------------+---------+ | value64 | value32 | value64 | value32 | +----------------------+---------+----------------------+---------+ | 17156792991891826145 | 1 | 17156792991891826145 | 3 | +----------------------+---------+----------------------+---------+ 1 row in set (0.01 sec) Empty set (0.01 sec) +---------------------+---------+---------------------+---------+ | value64 | value32 | value64 | value32 | +---------------------+---------+---------------------+---------+ | 9223372036854775807 | 2 | 9223372036854775807 | 4 | +---------------------+---------+---------------------+---------+ 1 row in set (0.02 sec) +---------------------+---------+---------------------+---------+ | value64 | value32 | value64 | value32 | +---------------------+---------+---------------------+---------+ | 9223372036854775807 | 2 | 9223372036854775807 | 4 | +---------------------+---------+---------------------+---------+ 1 row in set (0.01 sec) How to repeat: drop database if exists test64; create database test64; use test64; drop table if exists A; create table A ( value64 bigint unsigned not null , value32 integer not null ) ; alter table A add primary key ( value64 , value32 ) ; drop table if exists B; create table B ( value64 bigint unsigned not null , value32 integer not null ) ; alter table A add primary key ( value64 , value32 ) ; insert into A values(17156792991891826145, 1); insert into A values( 9223372036854775807, 2); insert into B values(17156792991891826145, 3); insert into B values( 9223372036854775807, 4); select * from A; select * from B; select * from A, B where A.value64=17156792991891826145 and B.value64=17156792991891826145; select * from A, B where A.value64=17156792991891826145 and B.value64=A.value64; select * from A, B where A.value64= 9223372036854775807 and B.value64=9223372036854775807; select * from A, B where A.value64= 9223372036854775807 and B.value64=A.value64; Suggested fix: None