Bug #142 bug of random expression in WHERE clause (Bug with IN and subselect)

• Submitted: 10 Mar 2003 16:31
• Modified: 11 Mar 2003 8:48
• Reporter: jocelyn fournier (Silver Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23
• OS: Any (all)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[10 Mar 2003 16:31] jocelyn fournier

Description:
MySQL returns the wrong result with the following IN subquery :

How to repeat:
CREATE TABLE `inscrit` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `pseudo` (`pseudo`),
) TYPE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

INSERT INTO inscrit (pseudo) VALUES ('test');
SELECT 0 IN (SELECT 1 FROM taist.inscrit a);
+-------------------------------------------------------------+
| 0 IN (SELECT 1 FROM taist.inscrit a) |
+-------------------------------------------------------------+
|                                                           0 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

=> OK

INSERT INTO inscrit (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM taist.inscrit a);
+-------------------------------------------------------------+
| 0 IN (SELECT 1 FROM taist.inscrit a) |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

=> Wrong, should still return 0

mysql> EXPLAIN SELECT 0 IN (SELECT 1 FROM taist.inscrit a);
+----+---------------------+-------+-------+---------------+---------+---------+------+------+----------------+
| id | select_type         | table | type  | possible_keys | key     | key_len | ref  | rows | Extra          |
+----+---------------------+-------+-------+---------------+---------+---------+------+------+----------------+
|  1 | PRIMARY             | NULL  | NULL  | NULL          | NULL    |    NULL | NULL | NULL | No tables used |
|  2 | DEPENDENT SUBSELECT | a     | index | NULL          | PRIMARY |       3 | NULL |    3 | Using index    |
+----+---------------------+-------+-------+---------------+---------+---------+------+------+----------------+

MySQL seems to use the wrong index (the PRIMARY KEY here).

[11 Mar 2003 7:03] Oleksandr Byelkin

this is bug of random expression in WHERE clause, which is 
never chacked (impossible to get random set of record) 
 
here is test of it: 
 
CREATE TABLE t1 ( 
  id mediumint(8) unsigned NOT NULL auto_increment, 
  pseudo varchar(35) NOT NULL default '', 
  PRIMARY KEY  (id), 
  UNIQUE KEY pseudo (pseudo) 
); 
INSERT INTO t1 (pseudo) VALUES ('test'); 
INSERT INTO t1 (pseudo) VALUES ('test1'); 
SELECT 1 from t1 where rand() > 1;