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

LP:702322 - Wrong result / HAVING not observed in maria-5.3

        Details

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

          Description

          If the HAVING clause contains a redundant clause, e.g. "t1.f1 AND t1.f1", it is not observed and rows are returned for which f1 = 0

          reproducible in maria-5.3 , maria-5.3-wl128. Not reproducible in maria-5.2 and mysql 5.5.8

          EXPLAIN:

          id select_type table type possible_keys key key_len ref rows Extra
          1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort
          1 SIMPLE t2 ref f1 f1 5 test.t1.f2 1 Using index

          test case:

          --source include/have_innodb.inc

          CREATE TABLE t2 ( f1 int(11), KEY (f1)) ENGINE=InnoDB;
          CREATE TABLE t1 ( f2 int(11), f1 int(11), PRIMARY KEY (f2), KEY (f1)) ENGINE=InnoDB;

          INSERT IGNORE INTO t1 VALUES (18,0),(9,10),(8,11),(2,15),(7,19),(1,20);

          SET SESSION join_cache_level = 0;
          SELECT t1.f1 FROM t1 LEFT JOIN t2 FORCE KEY (f1) ON t1.f2 = t2.f1 WHERE t1.f2 >= 6 HAVING t1.f1 AND t1.f1 ORDER BY t1.f1;

          returns:

          f1
          0
          10
          11
          19

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 702322

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

                  People

                  • Assignee:
                    igor Igor Babaev
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: