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

Wrong result (missing row) with LEFT JOIN, HAVING and ORDER BY

        Details

          Description

          Test case:

          CREATE TABLE t1 (a INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (3),(6);

CREATE TABLE t2 (b INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (8),(9);

SELECT * FROM t1 LEFT JOIN t2 ON ( a = b ) HAVING a NOT IN ( SELECT 6 ) ORDER BY a ;

DROP TABLE t1, t2;

          On current MariaDB 5.1, 5.2, 5.3, MySQL 5.1, 5.5 it returns the wrong result (empty set):

          a	b

          On current MariaDB 5.5, 10.0, MySQL 5.6, 57 it returns the expected result (1 row):

          a	b
3	NULL

          Story:

          The bug was introduced in MySQL 5.1 (and merged up) by the following revision:

          revno: 4005
revision-id: sergey.glukhov@oracle.com-20130507091058-x4li5jat6pvhop96
parent: annamalai.gurusami@oracle.com-20130506105856-d5il8vpqcovru47g
committer: Sergey Glukhov <sergey.glukhov@oracle.com>
branch nick: mysql-5.1
timestamp: Tue 2013-05-07 13:10:58 +0400
message:
  Bug#16095534 CRASH: PREPARED STATEMENT CRASHES IN ITEM_BOOL_FUNC2::FIX_LENGTH_AND_DEC
  The problem happened due to broken left expression in Item_in_optimizer object.
  In case of the bug left expression is runtime created Item_outer_ref item which
  is deleted at the end of the statement and one of Item_in_optimizer arguments
  becomes bad when re-executed. The fix is to use real_item() instead of original
  left expression. Note: It feels a bit weird that after preparing, the field is
  directly part of the generated Item_func_eq, whereas in execution it is replaced
  with an Item_outer_ref wrapper object.

          Later the bug was fixed in MySQL 5.6 and up by the following revision:

           revno: 5849
revision-id: chaithra.gopalareddy@oracle.com-20140228093141-v2sgort4nzof1ydg
parent: georgi.kodinov@oracle.com-20140228084025-kijma5qd98ttna38
committer: Chaithra Gopalareddy <chaithra.gopalareddy@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-02-28 15:01:41 +0530
message:
  Bug#18014565: WRONG RESULT COMPUTATION USING ALL() AND GROUP BY

          The first change was merged into MySQL 5.5 and made it to MariaDB 5.5; but then the bug was fixed in MariaDB 5.5 by this revision:

           revno: 3895
revision-id: sergii@pisem.net-20130913214200-0b5q0uanbj6mtti7
parent: bar@mnogosearch.org-20130913104740-3nvxlwt6h9pxatqz
fixes bug: https://mariadb.atlassian.net/browse/MDEV-5012
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: 5.5
timestamp: Fri 2013-09-13 23:42:00 +0200
message:
  MDEV-5012 Server crashes in Item_ref::real_item on EXPLAIN with select subqueries or views, constant table, derived_merge+derived_with_keys
  
  revert incorrect change, merged from mysql-5.5

          So, it's not reproducible on MariaDB 5.5 and 10.0.

          Recently, MySQL 5.1 was merged into MariaDB 5.1/5.2/5.3. The faulty change was merged too, so the wrong result started appearing on these trees. But the fix for it only exists in MySQL 5.6 or MariaDB 5.5, so apparently the bug will stay in MySQL 5.1/5.5 forever, and in MariaDB 5.1-5.3 too unless we decide it's worth fixing.

          Please decide.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5102 MySQL Bug 69581

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5112 InnoDB index intersection returns less results than expected

                • Major - Major loss of function.
                • Closed
                links to

                Web Link Bug #69581 - InnoDB index intersection returns less results than expected

                Web Link Bug #71244 - Wrong result computation using ALL() and GROUP BY

                Web Link Revno: 3895 Revision ID: sergii@pisem.net-20130913214200-0b5q0uanbj6mtti7

                Web Link Revno: 4005 Revision ID: sergey.glukhov@oracle.com-20130507091058-x4li5jat6pvhop96

                Web Link Revno: 5849 Revision ID: chaithra.gopalareddy@oracle.com-20140228093141-v2sgort4nzof1ydg

                  Activity

                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Linked upstream bugs were fixed in 5.6.14 and 5.6.17.
                  As described earlier, the problem is not reproducible on MariaDB 5.5 or 10.0, and I don't expect it will be fixed in 5.1-5.3, so closing it as "won't fix".

                  Show
                  elenst Elena Stepanova added a comment - Linked upstream bugs were fixed in 5.6.14 and 5.6.17. As described earlier, the problem is not reproducible on MariaDB 5.5 or 10.0, and I don't expect it will be fixed in 5.1-5.3, so closing it as "won't fix".

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: