Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.3
-
None
-
windows 11
Description
Expected Behavior
According to relational algebra, the following two queries should return identical results:
Query 1: INNER JOIN on condition C
Query 2: (LEFT JOIN on C) INTERSECT (RIGHT JOIN on C)
Actual Behavior
The queries produce inconsistent results:
INNER JOIN query returns 2 rows (correct):
-825194864|-530407270
-825194864|2107463184
LEFT JOIN INTERSECT RIGHT JOIN query returns 0 rows (incorrect)
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.
Steps to Reproduce
-- Create test tables
|
CREATE TABLE IF NOT EXISTS t0(c0 REAL SIGNED NOT NULL) engine=MyISAM; |
CREATE OR REPLACE TABLE t1 LIKE t0; |
-- Table operations
|
REPAIR TABLE t1; |
UPDATE LOW_PRIORITY IGNORE t1 SET c0='cNC34KJs'; |
-- Insert test data
|
INSERT INTO t1 VALUES (2107463184); |
INSERT INTO t1 VALUES (-530407270); |
INSERT INTO t0 VALUES (-825194864); |
-- Verify table integrity
|
CHECK TABLE t1 EXTENDED MEDIUM FOR UPGRADE; |
-- Create index
|
CREATE INDEX ic0 ON t1(c0 ASC); |
-- Query 1: INNER JOIN (returns 2 rows - correct)
|
SELECT DISTINCT t0.c0, t1.c0 |
FROM t0 INNER JOIN t1 |
ON (t0.c0 & t0.c0) IN (t1.c0, TRIM(t1.c0), t0.c0); |
-- Query 2: LEFT JOIN INTERSECT RIGHT JOIN (returns 0 rows - incorrect)
|
(SELECT DISTINCT t0.c0, t1.c0 |
FROM t0 LEFT JOIN t1 |
ON (t0.c0 & t0.c0) IN (t1.c0, TRIM(t1.c0), t0.c0)) |
INTERSECT
|
(SELECT DISTINCT t0.c0, t1.c0 |
FROM t0 RIGHT JOIN t1 |
ON (t0.c0 & t0.c0) IN (t1.c0, TRIM(t1.c0), t0.c0)); |