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)

        Attachments

          Activity

            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: