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

Wrong result on 2nd execution of PS with aggregate function, FROM SQ or MERGE view

    XMLWordPrintable

Details

    Description

      The problem appeared in 10.0 tree with the following revision:

         revno: 4339.1.6
          revision-id: sergii@pisem.net-20140807194554-mdg1i1y7tz2r01q1
          parent: sergii@pisem.net-20140807160850-j48c9l19lwbkxia7
          committer: Sergei Golubchik <sergii@pisem.net>
          branch nick: 10.0
          timestamp: Thu 2014-08-07 21:45:54 +0200
          message:
            crash in main.views (and other view + PS tests)
            
            When a view is merged, mark its select_lex as already optimized, otherwise
            its where clause (which doesn't have to be a valid Item after merging)
            might be accessed later. But don't do that for inserts (where a view cannot
            be simply merged, if one later needs to insert into it).

      Test case:

      CREATE TABLE t1 (i INT NOT NULL DEFAULT 0);
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (j INT NOT NULL DEFAULT 0);
      INSERT INTO t2 VALUES (11),(12);
       
      CREATE ALGORITHM=MERGE VIEW v3 AS SELECT t1.* FROM t2 LEFT JOIN t1 ON (t2.j = t1.i);
       
      PREPARE stmt FROM 'SELECT COUNT(v3.i) FROM t1, v3';
       
      # Same problem with a subquery, without a view
      # PREPARE stmt FROM 'SELECT COUNT(sq.i) FROM t1, ( SELECT t1.* FROM t2 LEFT JOIN t1 ON (t2.j = t1.i) ) AS sq';
       
      EXECUTE stmt;
      EXECUTE stmt;
       
      DROP TABLE IF EXISTS t1, t2;
      DROP VIEW IF EXISTS v3;

      Actual result:

      EXECUTE stmt;
      COUNT(v3.i)
      0
      EXECUTE stmt;
      COUNT(v3.i)
      4

      Attachments

        Activity

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.