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

Wrong result on execution of a query as a PS (both 1st and further executions)

        Details

        • Sprint:
          5.5.44

          Description

          The problem appeared on 5.5 tree with the following revision:

          revno: 4030
revision-id: monty@askmonty.org-20140124125018-qwln430o4bu3pued
parent: monty@askmonty.org-20140124123019-2ts6q2ip6076gmqg
committer: Michael Widenius <monty@askmonty.org>
branch nick: maria-5.5
timestamp: Fri 2014-01-24 14:50:18 +0200
message:
  Fix for MDEV-5531: double call procedure in one session - hard shutdown the server
  Main fix was to not cache derivied tables as they may be temporary tables that are deleted before the next query.
  This was a bit tricky as Item_field::fix_fields depended on cached_tables to be set to resolve some columns.
          Test case
          CREATE TABLE t (a INT NOT NULL) ENGINE=MyISAM;
INSERT INTO t VALUES (0),(8);

PREPARE stmt FROM "
SELECT a FROM ( SELECT DISTINCT t1.* FROM t AS t1, t AS t2 ) AS sq 
WHERE a IN ( SELECT MIN(t3.a) FROM ( t AS t3 INNER JOIN t AS t4 ON (t3.a = t4.a) ) ) ORDER BY a LIMIT 10
";

execute stmt;
execute stmt;
          Results
          MariaDB [test]> execute stmt;
+---+
| a |
+---+
| 0 |
| 8 |
+---+
2 rows in set (0.01 sec)

MariaDB [test]> execute stmt;
Empty set (0.00 sec)

          Both results are incorrect.
          When the query is executed directly, without a PS, it produces the expected result:

          MariaDB [test]> SELECT a FROM ( SELECT DISTINCT t1.* FROM t AS t1, t AS t2 ) AS sq 
    -> WHERE a IN ( SELECT MIN(t3.a) FROM t AS t3 INNER JOIN t AS t4 ON (t3.a = t4.a) ) ORDER BY a LIMIT 10
    -> ;
+---+
| a |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                monty Michael Widenius added a comment -

                Problem was that for cases like:
                SELECT ... WHERE XX IN (SELECT YY)
                this was transformed to something like:
                SELECT ... WHERE IF_EXISTS(SELECT ... HAVING XX=YY)

                The bug was that for normal execution XX was fixed in the original outer SELECT context while in PS it was fixed in the sub query context and this confused the optimizer.

                Fixed by ensuring that XX is always fixed in the outer context.

                Show
                monty Michael Widenius added a comment - Problem was that for cases like: SELECT ... WHERE XX IN (SELECT YY) this was transformed to something like: SELECT ... WHERE IF_EXISTS(SELECT ... HAVING XX=YY) The bug was that for normal execution XX was fixed in the original outer SELECT context while in PS it was fixed in the sub query context and this confused the optimizer. Fixed by ensuring that XX is always fixed in the outer context.
                Hide
                Permalink
                monty Michael Widenius added a comment -

                Fix pushed into 5.5 tree

                Show
                monty Michael Widenius added a comment - Fix pushed into 5.5 tree

                  People

                  • Assignee:
                    monty Michael Widenius
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Agile