Details
Major Description
A critical query optimizer issue has been identified in MariaDB 11.8.3 where INNER JOIN and STRAIGHT_JOIN with identical join conditions produce different result sets.
Expected Behavior
INNER JOIN and STRAIGHT_JOIN should return the same result set (only the table join order differs; the results should be identical).
Actual Behavior
INNER JOIN query returns 0 rows
STRAIGHT_JOIN query returns 1 row: 314572759|0
Steps to Reproduce
-- Create test tables
|
CREATE OR REPLACE TABLE t0(c0 VARCHAR(100) UNIQUE PRIMARY KEY); |
CREATE TABLE IF NOT EXISTS t2(c0 REAL UNSIGNED UNIQUE NOT NULL, PRIMARY KEY(c0)); |
|
|
-- Insert test data
|
INSERT INTO t0 VALUES ('314572759'); |
INSERT INTO t2 VALUES (1804481142); |
|
|
-- Update operation (critical step)
|
UPDATE IGNORE t2 SET c0=-290584934; |
|
|
-- Query 1: INNER JOIN (returns 0 rows - incorrect)
|
SELECT DISTINCT t0.c0, t2.c0 FROM t0 INNER JOIN t2 ON ('-1946386008' IN (t2.c0)); |
|
|
-- Query 2: STRAIGHT_JOIN (returns 1 row - correct)
|
SELECT DISTINCT t0.c0, t2.c0 FROM t0 STRAIGHT_JOIN t2 ON ('-1946386008' IN (t2.c0)); |