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

Wrong result on 2nd execution with outer reference

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6
    • None
    • None
    • None

    Description

      create table t1 (t1a int, t1b int, t1c int) engine=myisam;
      insert into t1 values (1,1,1),(2,2,2);
       
      create table t2 (t2a int, t2b int, t2c int) engine=myisam;
      insert into t2 values (1,1,1),(2,2,2),(3,3,3);
       
      create table t3 (t3a int, t3b int, t3c int) engine=myisam;
      insert into t3 values (1,1,1),(2,2,2),(3,3,3),(4,4,4);
       
      create view v2 (v2a, v2b) as
        select t7a*t5c, t7b*t5c from t7, t5 where t7a = t5a and t7c > 4;
      

      prepare s from 'select * from 
      (
        select * from t1, v2 where t1a<=v2a and t1b in
        (
          select t3b from t3 
           where t3b <= v2b
        )
      ) dt';
      execute s;
      

      produces the correct answer,

      +------+------+------+------+------+
      | t1a  | t1b  | t1c  | v2a  | v2b  |
      +------+------+------+------+------+
      |    1 |    1 |    1 |   25 |   25 |
      |    1 |    1 |    1 |   36 |   36 |
      |    2 |    2 |    2 |   25 |   25 |
      |    2 |    2 |    2 |   36 |   36 |
      +------+------+------+------+------+
      

      but a further

      execute s;
      

      produces

      Empty set (0.021 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              Johnston Rex Johnston
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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