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

Wrong result with agregate with join with no resultset

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.14
        • Fix Version/s: 10.0, 5.5
        • Component/s: None
        • Labels:

          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)
);

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)
);

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             |
+-----------+------------------+

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link Bug #73946 - Wrong result with agregate with join with no resultset

                  Activity

                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment - - edited

                  Marc Trouwaert
                  Thank you for the report.

                  Oleksandr Byelkin

                  The problem is reproducible on InnoDB tables, but not on MyISAM! If you put the provided test case into MTR, make sure it uses InnoDB for the tables.
                  Please also note that it's an upstream bug; if you think it's appropriate, we can wait and see if it gets fixed in MySQL.

                  Show
                  elenst Elena Stepanova added a comment - - edited Marc Trouwaert Thank you for the report. Oleksandr Byelkin The problem is reproducible on InnoDB tables, but not on MyISAM! If you put the provided test case into MTR, make sure it uses InnoDB for the tables. Please also note that it's an upstream bug; if you think it's appropriate, we can wait and see if it gets fixed in MySQL.

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      Marc Marc Trouwaert
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated: