[MDEV-7625] SELECT FOUND_ROWS() returns wrong value where using NOT IN in subquery Created: 2015-02-24  Updated: 2015-02-25  Resolved: 2015-02-25

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.0.11
Fix Version/s: 10.0.12

Type: Bug Priority: Major
Reporter: Lay András Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux guest 3.2.66-grsec-sma #1 SMP Fri Jan 2 19:09:18 CET 2015 x86_64 GNU/Linux

Attachments: File test.sql    

 Description   

With the attached dump, under 10.0.11 the following query result 49403 is wrong:

mysql> select version();
 
+--------------------------+
 
| version()                |
 
+--------------------------+
 
| 10.0.11-MariaDB-1~wheezy |
 
+--------------------------+
 
1 row in set (0.00 sec)
 
 
 
mysql> SELECT SQL_CALC_FOUND_ROWS ugyfelid FROM table1 WHERE ugyfelid IN(SELECT ugyfelid FROM table2 WHERE tagid!=2970)ORDER BY osszeg ASC LIMIT 100,1;SELECT FOUND_ROWS();
 
+----------+
 
| ugyfelid |
 
+----------+
 
|     7042 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
+--------------+
 
| FOUND_ROWS() |
 
+--------------+
 
|        49403 |
 
+--------------+
 
1 row in set (0.00 sec)

The good value under 5.5.41 is 12328:

(root@localhost) [pcszerviz]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 5.5.41-MariaDB |
 
+----------------+
 
1 row in set (0.02 sec)
 
 
 
(root@localhost) [pcszerviz]> SELECT SQL_CALC_FOUND_ROWS ugyfelid FROM table1 WHERE ugyfelid IN(SELECT ugyfelid FROM table2 WHERE tagid!=2970)ORDER BY osszeg ASC LIMIT 100,1;SELECT FOUND_ROWS();
 
+----------+
 
| ugyfelid |
 
+----------+
 
|     7042 |
 
+----------+
 
1 row in set (0.30 sec)
 
 
 
+--------------+
 
| FOUND_ROWS() |
 
+--------------+
 
|        12328 |
 
+--------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2015-02-25 ]

We had several iterations of fixing issues related to FOUND_ROWS().
Your particular flavor was fixed in 10.0.12 by the following revision:

revno: 4226
 
revision-id: sergii@pisem.net-20140605135941-ytyt69i971b3syle
 
parent: sergii@pisem.net-20140605135935-vlj5qhr4yr8lp2fn
 
committer: Sergei Golubchik <sergii@pisem.net>
 
branch nick: 10.0
 
timestamp: Thu 2014-06-05 15:59:41 +0200
 
message:
 
  revert the fix for MDEV-5898, restore the fix for MDEV-5549.
 
  simplify test case for MDEV-5898

It works okay on the current tree, too:

MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS ugyfelid FROM table1 WHERE ugyfelid IN(SELECT ugyfelid FROM table2 WHERE tagid!=2970)ORDER BY osszeg ASC LIMIT 100,1;
 
+----------+
 
| ugyfelid |
 
+----------+
 
|     7042 |
 
+----------+
 
1 row in set (3.95 sec)
 
 
 
MariaDB [test]> SELECT FOUND_ROWS();
 
+--------------+
 
| FOUND_ROWS() |
 
+--------------+
 
|        12328 |
 
+--------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT @@version;
 
+-----------------------+
 
| @@version             |
 
+-----------------------+
 
| 10.0.17-MariaDB-debug |
 
+-----------------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 07:21:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.