Major Description
When using "IN" clause with limit in a sub query, the results are not coming correctly. The result is bringing some of the records that are not valid as part of the sub query result.
eg:
In the given example, the first four(always four records and only on second request in the limit) records in the first limit are copied to second page and last 4 records are not displayed.
select ATTR_ID, NAME from TEST where ID = 289024 and DIM_ID = 0 and NAME is not null and NAME NOT IN (select NAME from TEST where ID = 289024 and DIM_ID = 0 and NAME is not null order by NAME limit 0 ) order by NAME limit 10;
----------------------------------------------------------------------------------+
| ATTR_ID | NAME |
----------------------------------------------------------------------------------+
| 289039 | black |
| 292055 | black1 |
| 292056 | black10 |
| 292057 | black100 |
| 292058 | black101 |
| 292059 | black103 |
| 292060 | black11 |
| 292061 | black12 |
| 292062 | black13 |
| 292063 | black14 |
----------------------------------------------------------------------------------+
10 rows selected (1.04 seconds)
select ATTR_ID, NAME from TEST where ID = 289024 and DIM_ID = 0 and NAME is not null and NAME NOT IN (select NAME from TEST where ID = 289024 and DIM_ID = 0 and NAME is not null order by NAME limit 10 ) order by NAME limit 10;
----------------------------------------------------------------------------------+
| ATTR_ID | NAME |
----------------------------------------------------------------------------------+
| 292060 | black11 |
| 292061 | black12 |
| 292062 | black13 |
| 292063 | black14 |
| 292064 | black15 |
| 292065 | black16 |
| 292066 | black17 |
| 292067 | black18 |
| 292068 | black19 |
| 292069 | black2 |
----------------------------------------------------------------------------------+
10 rows selected (1.683 seconds)