Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 5.5.25
-
Component/s: None
-
Labels:None
Description
The following type of scenarios produces lots of mismatches between the main 5.5 tree and MDEV-193 tree:
CREATE TABLE t1 (a INT) ENGINE=MyISAM; CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (1); # Either t1 is empty, or SELECT has an impossible condition # Either t2 has 1 row, or an aggregate function, e.g. SUM, is used in the subquery SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1 HAVING bb IS NOT NULL;
On 5.5 main tree, the query returns an empty result set. On MDEV-193 tree it returns ( NULL, NULL ).
The same query, but without a HAVING clause, returns (NULL, NULL) on both branches, which is also incorrect, see https://bugs.launchpad.net/maria/+bug/1008773. However, 5.5 really considers bb to be NULL when it applies the HAVING condition, while MDEV-193 seems to "think" that bb actually contains 1, but does not show it.
Possibly internally the behavior on MDEV-193 is even more correct, but it causes visibly inconsistent result; besides, my concern is that it might affect cases when the resultset without HAVING is actually correct, so it would be good to check the internal logic, just in case.
Reproducible with MyISAM and Aria, but not InnoDB.
Reproducible with the default optimizer_switch as well as with all OFF values except for in_to_exists which is required to run the query.
Test case:
SET optimizer_switch='in_to_exists=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (1); # Either t1 is empty, or SELECT has an impossible condition SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1 HAVING bb IS NOT NULL;
Result on 5.5 main:
MAX(a) bb
Result on MDEV-193 tree:
MAX(a) bb NULL NULL
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This bug is a duplicate of https://bugs.launchpad.net/maria/+bug/1008773.
The patch for lp:1008773 fixes this problem and the wrong result of 5.5.