Details
Major Description
Expected Behavior
According to relational algebra, the following two queries should return identical results:
Query 1: INNER JOIN with condition C and WHERE clause W
Query 2: (LEFT JOIN with C WHERE W) INTERSECT (RIGHT JOIN with C WHERE W)
Both queries should return the same result.
Actual Behavior
The queries produce inconsistent results:
INNER JOIN query returns 1 row:
-122573583|103403640
LEFT JOIN INTERSECT RIGHT JOIN query returns 0 rows
This indicates a query optimizer or execution bug where the INTERSECT operation with LEFT JOIN and RIGHT JOIN fails to produce the logically equivalent result of an INNER JOIN when combined with a complex WHERE clause involving IF() function and unary plus operator.
Steps to Reproduce
-- Create test tables
|
CREATE TABLE IF NOT EXISTS t0(c0 REAL NOT NULL) engine=MyISAM; |
CREATE TABLE t1 LIKE t0; |
-- Insert test data
|
INSERT INTO t1 VALUES (-122573583); |
INSERT INTO t0 VALUES (-1507933483); |
-- Analyze tables
|
ANALYZE TABLE t1, t0; |
-- Insert additional data
|
INSERT INTO t0 VALUES (103403640); |
-- Query 1: INNER JOIN (returns 1 row)
|
SELECT DISTINCT t1.c0, t0.c0 |
FROM t1 INNER JOIN t0 |
ON (t0.c0 >= (-1381544742 << (t1.c0 - t1.c0))) |
WHERE (+ (IF(t0.c0, t1.c0, -1594132977) - true)); |
-- Query 2: LEFT JOIN INTERSECT RIGHT JOIN (returns 0 rows)
|
(SELECT DISTINCT t1.c0, t0.c0 |
FROM t1 LEFT JOIN t0 |
ON (t0.c0 >= (-1381544742 << (t1.c0 - t1.c0))) |
WHERE (+ (IF(t0.c0, t1.c0, -1594132977) - true))) |
INTERSECT
|
(SELECT DISTINCT t1.c0, t0.c0 |
FROM t1 RIGHT JOIN t0 |
ON (t0.c0 >= (-1381544742 << (t1.c0 - t1.c0))) |
WHERE (+ (IF(t0.c0, t1.c0, -1594132977) - true))); |