MySQL Bugs: #40519: Subselect query using bigint fails

Bug #40519	Subselect query using bigint fails
Submitted:	5 Nov 2008 6:46	Modified:	21 Nov 2008 9:48
Reporter:	Rob Nielsen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.67, 5.0.70	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
It appears that certain queries with subselects won't return values if bigints are used but will if integers or a different form of query is used. 

How to repeat:
drop table if exists item;
drop table if exists link;
create table item(id bigint);
create table link(id1 bigint, id2 bigint);
insert into item values (1),(2),(3);
insert into link values (2,1),(3,1);
select i.* from item i join link l on i.id=l.id1 where l.id2=1;
/* returns [2,3] */

select * from item i where 1 in (select l.id2 from link l where i.id=l.id1);
/* INCORRECTLY returns empty set in MYSQL 5.0.67a-0ubuntu6 on Ubuntu 8.10 32 and 64 bit and MYSQL 5.0.67 on Win32 */
/* CORRECTLY returns [2,3] on MYSQL 5.0.51a-3ubuntu5.1 on Ubuntu 8.04.1 */

alter table item modify id integer;
alter table link modify id1 integer;
alter table link modify id2 integer;

select * from item i where 1 in (select l.id2 from link l where i.id=l.id1);
/* returns [2,3] */

Thank you for a bug report. Verified just as described with 5.0.70:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -P3308 -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists item;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> drop table if exists link;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table item(id bigint);
Query OK, 0 rows affected (0.27 sec)

mysql> create table link(id1 bigint, id2 bigint);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into item values (1),(2),(3);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into link values (2,1),(3,1);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select i.* from item i join link l on i.id=l.id1 where l.id2=1;
+------+
| id   |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.08 sec)

mysql> select * from item i where 1 in (select l.id2 from link l where i.id=l.id
1);
Empty set (0.03 sec)

mysql> alter table item modify id integer;
Query OK, 3 rows affected (0.84 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table link modify id1 integer;
Query OK, 2 rows affected (0.34 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table link modify id2 integer;
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from item i where 1 in (select l.id2 from link l where i.id=l.id
1);
+------+
| id   |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

5.1.29 is NOT affected.

duplicated with bug#34760

Hi Gluh, are you certain it's really a dup?
The other bug deals with a character set problem, whereas this one is about a BIGINT.
Or did you verify that the newly committed fix for that bug also resolves this one?
If the latter is the case, please do make sure the testcase also contains a BIGINT test so a future regression is not possible.

And, please note here what the story is! Thanks

Arjen,

Yes, the patch for bug #34760 (bug #20835) fixes the problem.
Please post your suggestion about test case at bug #34760 page.

Thank you.

Gleb - done.