MySQL Bugs: #39447: Error with NOT NULL condition and LIMIT 1

Bug #39447	Error with NOT NULL condition and LIMIT 1
Submitted:	15 Sep 2008 8:57	Modified:	22 Nov 2010 1:16
Reporter:	Victor Pyankov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0.5, 6.0 BZR	OS:	Any (Linux, MS Windows XP SP2)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	DS-MRR, index condition pushdown

Description:
Following statements give incorrect results under MySQL v6.0.5 and v6.0.6:

D:\MySQL6\bin>mysql.exe -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 6.0.6-alpha-community-log MySQL Community Server (GPL)

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

mysql> use test;
Database changed
mysql> select id from errtab where parent_id is not null order by id desc limit 1;
+----+
| id |
+----+
| 60 |
+----+
1 row in set (0.00 sec)

mysql> select * from errtab where parent_id is not null order by id desc limit 1;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
| 70 |      NULL | J    |
+----+-----------+------+
1 row in set (0.00 sec)

============================================
Under MySQL v5.1.25 the results are correct:
============================================

D:\MySQL\bin>mysql.exe -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.25-rc-community-log MySQL Community Server (GPL)

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

mysql> use test;
Database changed
mysql> select id from errtab where parent_id is not null order by id desc limit 1;
+----+
| id |
+----+
| 60 |
+----+
1 row in set (0.00 sec)

mysql> select * from errtab where parent_id is not null order by id desc limit 1;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
| 60 |        40 | F    |
+----+-----------+------+
1 row in set (0.00 sec)

How to repeat:
Run "mysql.exe -u root -p < errtab.sql" (using attached file).

Script for create tables, fill data and execute test statements

Attachment: errtab.sql (application/octet-stream, text), 616 bytes.

Thank you for the report.

I can not repeat described behavior with test data. Also version 6.0.5 is a bit old. Please upgrade to current version 6.0.6, try with it and if problem still exists provide output of SHOW CREATE TABLE errtab and, if possible, dump of table errtab

Please look at mysql output above: 

"Server version: 6.0.6-alpha-community-log MySQL Community Server (GPL)"

Initially I have found this bug under version 6.0.6.

Steps to reproduce:

1. Download mysql-noinstall-6.0.6-alpha-win32.zip and copy to the root of drive C.

2. Unzip using command "7za x mysql-noinstall-6.0.6-alpha-win32.zip".

3. cd mysql-6.0.6-alpha-win32

4. Start server using command "start bin\mysqld.exe --defaults-file="C:\mysql-6.0.6-alpha-win32\my-small.ini""

5. Copy errtab.sql (for example from D) using command "copy d:\errtab.sql"

6. Execute test script using command "bin\mysql.exe -u root < errtab.sql"

You can see screenshot with result on attached screenshot.png.

Error reproduced.

Screenshot

Attachment: screen_short.png (image/png, text), 17.54 KiB.

C:\mysql-6.0.6-alpha-win32\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.6-alpha-community MySQL Community Server (GPL)

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

mysql> use test
Database changed
mysql> show create table errtab;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| errtab | CREATE TABLE `errtab` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

C:\mysql-6.0.6-alpha-win32\bin>mysqldump.exe -u root test errtab
-- MySQL dump 10.13  Distrib 6.0.6-alpha, for Win32 (ia32)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       6.0.6-alpha-community

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 `errtab`
--

DROP TABLE IF EXISTS `errtab`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `errtab` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `errtab`
--

LOCK TABLES `errtab` WRITE;
/*!40000 ALTER TABLE `errtab` DISABLE KEYS */;
INSERT INTO `errtab` VALUES (10,NULL,'A'),(20,10,'B'),(30,10,'C'),(40,NULL,'D'),(50,40,'E'),(60,40,'F'),(70,NULL,'J');
/*!40000 ALTER TABLE `errtab` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 */;

-- Dump completed on 2008-09-15 16:54:41

Thank you for the feedback.

Verified as described. Bug is only repeatable with InnoDB storage engine.

Not repeatable on the latest 6.0-bzr.  Repeatable on 6.0.7 build.

In both latest 6.0.10 and 6.0.7 the EXPLAIN is incorrect: it has neither
"Using where" nor "Using condition pushdown", while the query has the IS NOT
NULL predicate which needs to be checked somewhere.

mysql> explain SELECT * FROM errtab WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: errtab
         type: index
possible_keys: ind_parent_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: 
1 row in set (0.01 sec)

The problem is not related to InnoDB's MRR implementation. It's not
repeatable on MyISAM because it doesn't have clustered primary keys.

The problem is here:

sql_select.cc:

static bool
test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
			bool no_changes, const key_map *map)
{
  ...
      if (tab->pre_idx_push_select_cond)
      {
        tab->select_cond= tab->pre_idx_push_select_cond;
        if (tab->select)
          tab->select->cond= tab->select_cond;
      }
  ...

          if (tab->pre_idx_push_select_cond)
          {
            if (tab->select)
              tab->select->cond= tab->select_cond;
            tab->select_cond= tab->pre_idx_push_select_cond;
          }
   ... 

The first instance is correct. The second is not - tab->select->cond doesn't end up
set to tab->pre_idx_push_select_cond. 

The testcase is not repeatable with corrent tree because it was fixed by
fix for BUG#37851 and this: http://lists.mysql.com/commits/54000.

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/commits/62392

2714 Sergey Petrunia	2008-12-29
      BUG#39447: Error with NOT NULL condition and LIMIT 1
      - in test_if_skip_sort_order(): if we first decided to use one index and use index 
        condition pushdown but then re-considered and picked another index, undo the index 
        condition pushdown properly.

Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20090110105650-he9xy4221tbn5y70) (version source revid:sergefp@mysql.com-20090110105650-he9xy4221tbn5y70) (merge vers: 6.0.10-alpha) (pib:6)

Note for the changelog: 

Queries with that end with "... WHERE cond ORDER BY index_columns LIMIT n" could produce wrong results (records that do not match the WHERE would show up in the output) for certain kinds of conditions and table data distributions

Noted in 6.0.10 changelog.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.