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

LP:884657 - Wrong result with exists2in , correlated subquery

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          Description

          This query:

          SELECT *
          FROM t1, t2
          WHERE EXISTS (
          SELECT a
          FROM t3
          WHERE t3.b = t1.a
          AND t3.b <> t2.b
          );
          returns no rows in exists2in , even though the correct result is:

          ----------+

          a b

          ----------+

          c v
          c v
          c x
          c i
          c e
          c p
          c s
          c j
          c z
          c a
          c q
          c y
          c r
          c v
          c r

          ----------+

          explain with exists2in:

          id select_type table type possible_keys key key_len ref rows filtered Extra

          ------------------------------------------------------------------------------

          1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00  
          1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00  
          2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 100.00  
          Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
          Note 1276 Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1
          Note 1003 select 'c' AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where <expr_cache><'c',`test`.`t2`.`b`>(<in_optimizer>('c',<exists>(select 'c' from `test`.`t3` where (('c' <> `test`.`t2`.`b`) and (<cache>('c') = 'c')))))

          bzr version-info:

          revision-id: <email address hidden>
          date: 2011-10-31 13:44:24 +0200
          build-date: 2011-11-01 11:23:44 +0200
          revno: 3256
          branch-nick: maria-5.3-exists2in

          test case:

          CREATE TABLE t1 ( a varchar(1)) ;
          INSERT INTO t1 VALUES ('c');

          CREATE TABLE t2 ( b varchar(1)) ;
          INSERT INTO t2 VALUES ('v'),('v'),('c'),(NULL),('x'),('i'),('e'),('p'),('s'),('j'),('z'),('c'),('a'),('q'),('y'),(NULL),('r'),('v'),(NULL),('r');

          CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ;
          INSERT INTO t3 VALUES (29,'c');

          SELECT *
          FROM t1, t2
          WHERE EXISTS (
          SELECT a
          FROM t3
          WHERE t3.b = t1.a
          AND t3.b <> t2.b
          );

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 884657

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 884657

                  People

                  • Assignee:
                    sanja Oleksandr Byelkin
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: