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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    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

      Attachments

        Activity

          People

            igor Igor Babaev
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.