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

2nd execution of a prepared statement returns wrong results

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 5.3.12, 5.5.34, 10.0.6
    • 5.5.35, 10.0.8, 5.3.13
    • None
    • None
    • All

    Description

      When executing a prepared statement twicce, second execution returns invalid results.

      How to repeat:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1));
       
      INSERT INTO t1 VALUES (30,300),(40,400);
       
      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 (i2 INT);
      INSERT INTO t2 VALUES (50),(60);
       
      DROP TABLE IF EXISTS t3;
      CREATE TABLE t3 (c3 VARCHAR(20), i3 INT);
      INSERT INTO t3 VALUES ('a',10),('b',2);
       
      DROP TABLE IF EXISTS t4;
      CREATE TABLE t4 (i4 INT);
      INSERT INTO t4 VALUES (1),(2);
       
      DROP VIEW IF EXISTS v1;
      CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 );
       
      DROP VIEW IF EXISTS v2;
      CREATE VIEW v2 AS select v1_field1 from t4 join v1;
       
      prepare my_stmt from "select v1_field1 from v2";
      execute my_stmt;
      execute my_stmt;
      deallocate prepare my_stmt;
       
      DROP TABLE t1,t2,t3,t4;
      DROP VIEW v1,v2;

      Output:

      1st run
      +-----------+
      | v1_field1 |
      +-----------+
      |        10 |
      |        10 |
      |        10 |
      |        10 |
      |         2 |
      |         2 |
      |         2 |
      |         2 |
      +-----------+
       
      2nd run
       
      MySQL 5.3
      +-----------+
      | v1_field1 |
      +-----------+
      |         0 |
      |         0 |
      |         0 |
      |         0 |
      |         0 |
      |         0 |
      |         0 |
      |         0 |
      +-----------+
       
      MariaDB 10.0
       
      +-------------+
      | v1_field1   |
      +-------------+
      | -1886417009 |
      | -1886417009 |
      | -1886417009 |
      | -1886417009 |
      | -1886417009 |
      | -1886417009 |
      | -1886417009 |
      | -1886417009 |
      +-------------+

      Attachments

        Activity

          eliminate_tables() got wrong used tables map from SELECT item list (6 instead of 12) on second execution

          sanja Oleksandr Byelkin added a comment - eliminate_tables() got wrong used tables map from SELECT item list (6 instead of 12) on second execution

          it looks like lack of update_used_tables() after handling derived tables (worked for usual queries because there was other call only for firest execution)

          sanja Oleksandr Byelkin added a comment - it looks like lack of update_used_tables() after handling derived tables (worked for usual queries because there was other call only for firest execution)

          Patch sent for review.

          sanja Oleksandr Byelkin added a comment - Patch sent for review.

          Debugging the example, I see:

          (gdb) p join->fields_list.head()
          $39 = (Item_direct_view_ref *) 0x7fffcd4eefb8

          (gdb) p *join->fields_list.head()->ref
          $44 = (Item_direct_view_ref *) 0x7fffcd4eed78

          (gdb) p *(*join->fields_list.head()>ref)>ref
          $49 = (Item_func_coalesce *) 0x7fffcd4683d0

          This is probably not related to this bug, but why is one Item_direct_view_ref
          wrapped within the other Item_direct_view_ref.

          psergei Sergei Petrunia added a comment - Debugging the example, I see: (gdb) p join->fields_list.head() $39 = (Item_direct_view_ref *) 0x7fffcd4eefb8 (gdb) p *join->fields_list.head()->ref $44 = (Item_direct_view_ref *) 0x7fffcd4eed78 (gdb) p *(*join->fields_list.head() >ref) >ref $49 = (Item_func_coalesce *) 0x7fffcd4683d0 This is probably not related to this bug, but why is one Item_direct_view_ref wrapped within the other Item_direct_view_ref.
          dbart Daniel Bartholomew added a comment - http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.182

          People

            sanja Oleksandr Byelkin
            georg Georg Richter
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.