MySQL Bugs: #13535: Incorrect result from SELECT statement

Bug #13535	Incorrect result from SELECT statement
Submitted:	27 Sep 2005 19:35	Modified:	20 Oct 2005 6:25
Reporter:	Olav Vitters	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.14/BK source.	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The SELECT statement:
  SELECT 1234 FROM logincookies WHERE cookie IS NULL;

returns 1234 for the following table (called logincookies):
  +--------+--------+-----------+---------------------+
  | cookie | userid | ipaddr    | lastused            |
  +--------+--------+-----------+---------------------+
  |      1 |      1 | 127.0.0.1 | 2005-09-27 20:48:13 |
  +--------+--------+-----------+---------------------+

Note that cookie is never NULL, so it shouldn't return 1234.

This strangely can only be reproduced if:
1. Two tables are created (logincookies and whine_events)
2. SHOW TABLE STATUS; is used

Per connection only the first 'SELECT 1234...' statement returns the incorrect result. Repeating the SELECT will make it return the correct result.

Database structure is actually 2 tables from Bugzilla CVS.

How to repeat:
-- Load this dumpfile. SHOW TABLE STATUS + SELECT 1234 statement is at the end
-- Should NOT produce 1234 on the screen!
--

-- MySQL dump 10.9
--
-- Host: localhost    Database: bugscvs
-- ------------------------------------------------------
-- Server version       4.1.14-log

DROP DATABASE IF EXISTS bugscvs;
CREATE DATABASE bugscvs;
USE bugscvs;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `logincookies`
--

DROP TABLE IF EXISTS `logincookies`;
CREATE TABLE `logincookies` (
  `cookie` mediumint(9) NOT NULL auto_increment,
  `userid` mediumint(9) NOT NULL default '0',
  `ipaddr` varchar(40) NOT NULL default '',
  `lastused` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`cookie`),
  KEY `logincookies_lastused_idx` (`lastused`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `logincookies`
--

/*!40000 ALTER TABLE `logincookies` DISABLE KEYS */;
LOCK TABLES `logincookies` WRITE;
INSERT INTO `logincookies` VALUES (1,1,'127.0.0.1','2005-09-27 20:48:13');
UNLOCK TABLES;
/*!40000 ALTER TABLE `logincookies` ENABLE KEYS */;

--
-- Table structure for table `whine_events`
--

-- WARNING:
-- Can't reproduce if this table doesn't exist.
DROP TABLE IF EXISTS `whine_events`;
CREATE TABLE `whine_events` (
  `id` mediumint(9) NOT NULL auto_increment,
  `owner_userid` mediumint(9) NOT NULL default '0',
  `subject` varchar(128) default NULL,
  `body` mediumtext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `whine_events`
--

/*!40000 ALTER TABLE `whine_events` DISABLE KEYS */;
LOCK TABLES `whine_events` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `whine_events` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- WARNING:
-- Leave this out and the SELECT statement below works correctly
SHOW TABLE STATUS;

-- Should NOT return 1234!!!
SELECT 1234 FROM logincookies WHERE cookie IS NULL;

Suggested fix:
Ensure 
  SELECT 1234 FROM logincookies WHERE cookie IS NULL;

does NOT return anything.

How to reproduce as a file

Attachment: dump.sql (text/x-sql), 2.56 KiB.

Thank you for the bug report.
This bug not affects 5.0.

mysql> -- Should NOT return 1234!!!
mysql> SELECT 1234 FROM logincookies WHERE cookie IS NULL;
+------+
| 1234 |
+------+
| 1234 |
+------+
1 row in set (0.04 sec)

mysql> SELECT 1234 FROM logincookies WHERE cookie IS NULL;
Empty set (0.00 sec)

mysql> SELECT 1234 FROM logincookies WHERE cookie IS NULL;
Empty set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.01 sec)

mysql> 
mysql> 
mysql> -- Should NOT return 1234!!!
mysql> SELECT 1234 FROM logincookies WHERE cookie IS NULL;
Empty set (0.01 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.14-rc-debug |
+-----------------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30652

Fixed in 4.1.16, cset 1.2458.5.1

After SHOW TABLE STATUS last_insert_id wasn't cleaned, and next select
erroneously rewrites WHERE condition and returs a row;
5.0 isn't affected because of different SHOW TABLE STATUS handling.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 4.1.16 changelog. Closed.