Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7625

SELECT FOUND_ROWS() returns wrong value where using NOT IN in subquery

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.11
        • Fix Version/s: 10.0.12
        • Component/s: Data Manipulation - Subquery
        • Labels:
          None
        • Environment:
          Linux guest 3.2.66-grsec-sma #1 SMP Fri Jan 2 19:09:18 CET 2015 x86_64 GNU/Linux

          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)

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                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)
                Show
                elenst Elena Stepanova added a comment - 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)

                  People

                  • Assignee:
                    serg Sergei Golubchik
                    Reporter:
                    LaySoft Lay András
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Time Tracking

                      Estimated:
                      Original Estimate - 2 hours
                      2h
                      Remaining:
                      Remaining Estimate - 1 hour
                      1h
                      Logged:
                      Time Spent - Not Specified Time Not Required
                      Not Specified