MySQL Bugs: #59883: NOT IN clause with LIMIT

Bug #59883	NOT IN clause with LIMIT
Submitted:	2 Feb 2011 8:16	Modified:	11 Apr 2011 0:09
Reporter:	Ishita Tailor	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.44	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If we have 22 records in a table & if we use "not in" as well as "limit" clause on a query then it shows some duplicate results.

let say table name=test, field=id...etc

select * from test where id not in(7) limit 10,10;
select * from test where id not in(7) limit 20,10;

If this is the case then it will show perfect results for 1st query but for 2nd one, it will duplicate the rows.
The last record of 1st query resultset & first record of 2nd query resultset are same. The seconf query resultset overwrittes the 1 original row with the last record of 1st resultset, as a result 1 record is missing.

Works fine with in, but creates problem with not in().

How to repeat:
In any table with records greater than limit try out these 2 queries

let say table name=test, field=id...etc

select * from test where id not in(7) limit 10,10;
select * from test where id not in(7) limit 20,10;

This will generate the scenario

Please, check if the same problem still happens with a newer version of server, 5.1.54. If it does, please, upload complete test case, with exact CREATE TABLE and INSERTs to reproduce it.

Yes sure.. I am currently using a lower version. But till then please have a check & let me know if possible.

To check myself with 5.1.54 I need complete test case. I do not want to guess what data types are used, what indexes you may have on the table etc.

Okay. Then I'll download the latest version & will check it on my own.
What if still this bug or a problem exist??

Then you should copy/paste the results of your SELECTs that demonstrate the problem on 5.1.54, send SHOW CREATE TABLE results for the table used and wait for me to create my own complete test case. Or just send your complete test case, to speed up the process a bit.

Same problem still exist.
I installed the new version, still it is the same.

Okay I am uploading the whole test case here :

Create database Temp;

CREATE TABLE Stands(
Standard NUMERIC(5) PRIMARY KEY,
Descp VARCHAR(20));

CREATE TABLE Students(
Id NUMERIC(5) PRIMARY KEY,
Standard NUMERIC(5) REFERENCES Stand(Standard),
NAME VARCHAR(20));

INSERT INTO stand VALUES(10,"abc");
INSERT INTO stand VALUES(20,"def");
INSERT INTO stand VALUES(30,"ghi");
INSERT INTO stand VALUES(40,"jkl");

INSERT INTO students VALUES(1,10,"ghhhhv");
INSERT INTO students VALUES(2,20,"sjjjj");
INSERT INTO students VALUES(3,30,"yujjjv");
INSERT INTO students VALUES(4,40,"uuuu");
INSERT INTO students VALUES(5,20,"rrrv");
INSERT INTO students VALUES(6,10,"tttdv");
INSERT INTO students VALUES(7,30,"ghhhhv");
INSERT INTO students VALUES(8,40,"sjjjj");
INSERT INTO students VALUES(9,40,"yujjjv");
INSERT INTO students VALUES(10,30,"uuuu");
INSERT INTO students VALUES(11,20,"rrrv");
INSERT INTO students VALUES(12,10,"tttdv");
INSERT INTO students VALUES(13,30,"ghhhhv");
INSERT INTO students VALUES(14,40,"sjjjj");
INSERT INTO students VALUES(15,40,"yujjjv");
INSERT INTO students VALUES(16,30,"uuuu");
INSERT INTO students VALUES(17,20,"rrrv");
INSERT INTO students VALUES(18,10,"tttdv");
INSERT INTO students VALUES(19,30,"ghhhhv");
INSERT INTO students VALUES(20,40,"sjjjj");
INSERT INTO students VALUES(21,40,"yujjjv");
INSERT INTO students VALUES(22,30,"uuuu");
INSERT INTO students VALUES(23,20,"rrrv");
INSERT INTO students VALUES(24,10,"tttdv");
INSERT INTO students VALUES(25,10,"tttdv");
INSERT INTO students VALUES(26,30,"ghhhhv");
INSERT INTO students VALUES(27,40,"sjjjj");
INSERT INTO students VALUES(28,40,"yujjjv");

SELECT * FROM students WHERE standard NOT IN(20) LIMIT 0,10;
SELECT * FROM students WHERE standard NOT IN(20) LIMIT 10,10;
SELECT * FROM students WHERE standard NOT IN(20) LIMIT 20,10;

Here the student with Id=26 is duplicated.
Please have a look & reply as soon as possible.

I do not see anything duplicated with 5.1.54 on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE Stands(
    -> Standard NUMERIC(5) PRIMARY KEY,
    -> Descp VARCHAR(20));
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE Students(
    -> Id NUMERIC(5) PRIMARY KEY,
    -> Standard NUMERIC(5) REFERENCES Stand(Standard),
    -> NAME VARCHAR(20));
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO stands VALUES(10,"abc");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO stands VALUES(20,"def");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO stands VALUES(30,"ghi");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO stands VALUES(40,"jkl");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO students VALUES(1,10,"ghhhhv");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO students VALUES(2,20,"sjjjj");
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO students VALUES(3,30,"yujjjv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(4,40,"uuuu");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(5,20,"rrrv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(6,10,"tttdv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(7,30,"ghhhhv");
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO students VALUES(8,40,"sjjjj");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO students VALUES(9,40,"yujjjv");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO students VALUES(10,30,"uuuu");
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO students VALUES(11,20,"rrrv");
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO students VALUES(12,10,"tttdv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(13,30,"ghhhhv");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO students VALUES(14,40,"sjjjj");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(15,40,"yujjjv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(16,30,"uuuu");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(17,20,"rrrv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(18,10,"tttdv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(19,30,"ghhhhv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(20,40,"sjjjj");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(21,40,"yujjjv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(22,30,"uuuu");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO students VALUES(23,20,"rrrv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(24,10,"tttdv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(25,10,"tttdv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(26,30,"ghhhhv");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(27,40,"sjjjj");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students VALUES(28,40,"yujjjv");
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 0,10;
+----+----------+--------+
| Id | Standard | NAME   |
+----+----------+--------+
|  1 |       10 | ghhhhv |
|  3 |       30 | yujjjv |
|  4 |       40 | uuuu   |
|  6 |       10 | tttdv  |
|  7 |       30 | ghhhhv |
|  8 |       40 | sjjjj  |
|  9 |       40 | yujjjv |
| 10 |       30 | uuuu   |
| 12 |       10 | tttdv  |
| 13 |       30 | ghhhhv |
+----+----------+--------+
10 rows in set (0.02 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 10,10;
+----+----------+--------+
| Id | Standard | NAME   |
+----+----------+--------+
| 14 |       40 | sjjjj  |
| 15 |       40 | yujjjv |
| 16 |       30 | uuuu   |
| 18 |       10 | tttdv  |
| 19 |       30 | ghhhhv |
| 20 |       40 | sjjjj  |
| 21 |       40 | yujjjv |
| 22 |       30 | uuuu   |
| 24 |       10 | tttdv  |
| 25 |       10 | tttdv  |
+----+----------+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 20,10;
+----+----------+--------+
| Id | Standard | NAME   |
+----+----------+--------+
| 26 |       30 | ghhhhv |
| 27 |       40 | sjjjj  |
| 28 |       40 | yujjjv |
+----+----------+--------+
3 rows in set (0.02 sec)

If you see something different, please copy/paste entire session as I did it above.

Database changed
mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 0,10;
+----+----------+--------+
| Id | Standard | Name   |
+----+----------+--------+
|  1 |       10 | ghhhhv |
|  6 |       10 | tttdv  |
| 12 |       10 | tttdv  |
| 18 |       10 | tttdv  |
| 24 |       10 | tttdv  |
| 25 |       10 | tttdv  |
|  3 |       30 | yujjjv |
|  7 |       30 | ghhhhv |
| 10 |       30 | uuuu   |
| 13 |       30 | ghhhhv |
+----+----------+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 10,10;
+----+----------+--------+
| Id | Standard | Name   |
+----+----------+--------+
| 16 |       30 | uuuu   |
| 19 |       30 | ghhhhv |
| 22 |       30 | uuuu   |
| 26 |       30 | ghhhhv |
|  4 |       40 | uuuu   |
|  8 |       40 | sjjjj  |
|  9 |       40 | yujjjv |
| 14 |       40 | sjjjj  |
| 15 |       40 | yujjjv |
| 20 |       40 | sjjjj  |
+----+----------+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 20,10;
+----+----------+--------+
| Id | Standard | Name   |
+----+----------+--------+
| 26 |       30 | ghhhhv |
| 27 |       40 | sjjjj  |
| 28 |       40 | yujjjv |
+----+----------+--------+
3 rows in set (0.00 sec)

This is the o/p I am getting. where id=26 is repeated

& yes I am using Windows 7 Professional licensed version.

Unlike in my example, I do NOT see server version used in your case... Anyway, please, send the output of:

show create table students\G

show table status like 'students'\G

mysql> show create table Students\G
*************************** 1. row ***************************
       Table: Students
Create Table: CREATE TABLE `Students` (
  `Id` decimal(5,0) NOT NULL,
  `Standard` decimal(5,0) DEFAULT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show table status like 'Students'\G
*************************** 1. row ***************************
           Name: Students
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 28
 Avg_row_length: 20
    Data_length: 560
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-02-02 19:18:43
    Update_time: 2011-02-02 19:20:11
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

This is the thing. I doubt that something is within the behavior of where clause.
Due to cardinalities & indexes this problem is coming.
When I run the same thing from command prompt, it works.
But id I am doing this same with some MySQL client like SQL YOG, then it gives such undesired output.

@Ishita

Please come to our/Webyog/SQLyog Forums instead: http://www.webyog.com/forums/ or send a mail to support@webyog.com (this will create a support ticket).

Peter (from Webyog).

@ Peter

This is not an issue with SQL YOG. This is a common problem for mysql.
I just want to know what is behavior of not in clause along with where() & limit().
Kindly provide me with answers if possible.

Thank you

1 more information

SELECT * FROM students WHERE standard NOT IN(20) LIMIT 0,10;
SELECT * FROM students WHERE standard NOT IN(20) LIMIT 10,10;
SELECT * FROM students WHERE standard NOT IN(20) LIMIT 20,10;

In the above queries, if I use IN clause instead of NOT IN(), it does not give any bulky result. It gives the desired one.
for eg, in place of NOT IN(20), I use IN(10,30,40), it solves the problem. Nut its not feasible with my system.
Pls suggest some way out or explain their workings atleast. :-s

Now I see the difference with my case. Your table is MyISAM, while my was InnoDB (and, probably, you wanted yours to be InnoDB also because of that REFERENCES clause...).

Anyway, I do not see the problem with MyISAM table also on recent version. Please, send the output of:

check table students extended;

Database changed
mysql> check table students extended;
+---------------+-------+----------+-------------------------------------+
| Table         | Op    | Msg_type | Msg_text                            |
+---------------+-------+----------+-------------------------------------+
| temp.students | check | Error    | Table 'temp.students' doesn't exist |
| temp.students | check | status   | Operation failed                    |
+---------------+-------+----------+-------------------------------------+
2 rows in set (0.00 sec)

Here it is...

Sorry.. its dis..

Database changed
mysql> check table Students extended;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| temp.Students | check | status   | OK       |
+---------------+-------+----------+----------+
1 row in set (0.00 sec)

Its just perfect.
Everything is fine.. there is some issue with the behavior of where clause with LIMIT

As a last chunk of information, please, copy/paste the result of 3 SELECTs from mysql command line client, like I did.

This is not repeatable for me neither with 5.1.54 on Windows, nor with current sources on Mac OS X:

macbook-pro:5.1 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.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE `Students` (
    ->   `Id` decimal(5,0) NOT NULL,
    ->   `Standard` decimal(5,0) DEFAULT NULL,
    ->   `NAME` varchar(20) DEFAULT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO students VALUES(1,10,"ghhhhv");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO students VALUES(2,20,"sjjjj");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(3,30,"yujjjv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(4,40,"uuuu");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(5,20,"rrrv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(6,10,"tttdv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(7,30,"ghhhhv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(8,40,"sjjjj");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(9,40,"yujjjv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(10,30,"uuuu");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(11,20,"rrrv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(12,10,"tttdv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(13,30,"ghhhhv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(14,40,"sjjjj");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(15,40,"yujjjv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(16,30,"uuuu");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(17,20,"rrrv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(18,10,"tttdv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(19,30,"ghhhhv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(20,40,"sjjjj");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(21,40,"yujjjv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(22,30,"uuuu");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(23,20,"rrrv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(24,10,"tttdv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(25,10,"tttdv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(26,30,"ghhhhv");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(27,40,"sjjjj");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students VALUES(28,40,"yujjjv");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 0,10;
+----+----------+--------+
| Id | Standard | NAME   |
+----+----------+--------+
|  1 |       10 | ghhhhv |
|  3 |       30 | yujjjv |
|  4 |       40 | uuuu   |
|  6 |       10 | tttdv  |
|  7 |       30 | ghhhhv |
|  8 |       40 | sjjjj  |
|  9 |       40 | yujjjv |
| 10 |       30 | uuuu   |
| 12 |       10 | tttdv  |
| 13 |       30 | ghhhhv |
+----+----------+--------+
10 rows in set (0.04 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 10,10;
+----+----------+--------+
| Id | Standard | NAME   |
+----+----------+--------+
| 14 |       40 | sjjjj  |
| 15 |       40 | yujjjv |
| 16 |       30 | uuuu   |
| 18 |       10 | tttdv  |
| 19 |       30 | ghhhhv |
| 20 |       40 | sjjjj  |
| 21 |       40 | yujjjv |
| 22 |       30 | uuuu   |
| 24 |       10 | tttdv  |
| 25 |       10 | tttdv  |
+----+----------+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE standard NOT IN(20) LIMIT 20,10;
+----+----------+--------+
| Id | Standard | NAME   |
+----+----------+--------+
| 26 |       30 | ghhhhv |
| 27 |       40 | sjjjj  |
| 28 |       40 | yujjjv |
+----+----------+--------+
3 rows in set (0.00 sec)

Let's wait for somebody more lucky to repeat this behavior.

Can u see the difference between ur's & mine outputs?
My Id is not sorted, while ur's is in the sorted manner.
Something is wrong with the behaviour of limit with where.
If u know can u please explain the behaviour & internal working of LIMIT clause??

Thank you for the feedback.

Yes, we see difference between yours and Valeriy's output. Please send output of queries including create and inserts like Valeriy did in comment "[2 Feb 15:45] Valeriy Kravchuk"

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".