MySQL Bugs: #44716: select (1 in (select key0 from one_bigint)) returns 0 where key0=1 exists

Bug #44716	select (1 in (select key0 from one_bigint)) returns 0 where key0=1 exists
Submitted:	7 May 2009 12:29	Modified:	7 May 2009 14:24
Reporter:	Dmitry Mamonov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.67-community-nt-log	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	in select bigint

Description:
Query returns different results depends on attribute type - primary key or not.
See How to repeat for details.

How to repeat:
create table one_bigint (key0 bigint); -- type of attribute key0 - bigint
insert into one_bigint(key0) values(1); -- insert one value

select (1 in (select key0 from one_bigint));
-------------
result is: 0    <--- 0 is wrong, it must be 1, because 1 presented in subquery

-- now run alter table to make key0 attribute primary
alter table one_bigint add primary key(key0);

select (1 in (select key0 from one_bigint));
-----------
result is: 1 <--- now all fine, looks like result depends on attribute 
             <--- type (primary key) or indexes over attribute.

Suggested fix:
Workaround is to change attribute type from bigint to varchar:

select (1 in (select concat(key0) from one_bigint)); << now key0 type is varchar
-----------
result is: 1 in both tests, all fine

note: after dropping primary key:
alter table one_bigint drop primary key;
query still works fine.

Looks like something wrong with execution plan, but it is just a suggestion.

Thank you for the problem report. Please, try to repeat with a newer version, 5.0.81, and inform about the results. I can't repeat this:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.82-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table one_bigint (key0 bigint); -- type of attribute key0 - bigintQuery OK, 0 rows affected (0.01 sec)

mysql> insert into one_bigint(key0) values(1); -- insert one value
Query OK, 1 row affected (0.00 sec)

mysql> select (1 in (select key0 from one_bigint));
+--------------------------------------+
| (1 in (select key0 from one_bigint)) |
+--------------------------------------+
|                                    1 | 
+--------------------------------------+
1 row in set (0.00 sec)

With version: 5.0.81-community-nt-log

create table one_bigint3 (key0 bigint); -- type of attribute key0 - bigint
insert into one_bigint3(key0) values(1); -- insert one value

select (1 in (select key0 from one_bigint3));
-----------
result is: 1 -- OK!

So, this bug is not repeatable with recent versions.