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

Wrong result with aggregate with join with no result set

    XMLWordPrintable

Details

    Description

      Hi,

      I reported a bug on MySQL and MariaDB 5.5.39 is affected.

      http://bugs.mysql.com/bug.php?id=73946

      >>
      When you join 2 tables with no resultset and put a min/max on child table, in some case Parent's fields is not null.

      In the test-case bellow, PARENT_ID must be null, not "1"

      Test case :

      use test;
       
      drop table if exists _Parent;
      create table _Parent
      (
      	PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
      	PARENT_FIELD VARCHAR(10),
      	PRIMARY KEY (PARENT_ID)
      ) engine=innodb;
       
      drop table if exists _Child;
      create table _Child
      (
      	CHILD_ID INT NOT NULL AUTO_INCREMENT,
      	PARENT_ID INT NOT NULL,
      	CHILD_FIELD varchar(10),
      	PRIMARY KEY (CHILD_ID)
      ) engine=innodb;
       
      INSERT INTO _Parent (PARENT_FIELD)
      SELECT 'AAAA';
       
      INSERT INTO _Child (PARENT_ID, CHILD_FIELD)
      SELECT 1, 'BBBB';
       
      select 
       _Parent.PARENT_ID,
       min(CHILD_FIELD)
       from _Parent straight_join _Child
       where _Parent.PARENT_ID = 1
       and _Parent.PARENT_ID = _Child.PARENT_ID
       and _Child.CHILD_FIELD = "ZZZZ";
      

      +-----------+------------------+
      | PARENT_ID | min(CHILD_FIELD) |
      +-----------+------------------+
      |         1 | NULL             |
      +-----------+------------------+
      

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              Marc Marc T.
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.