Description:
INNER JOIN and STRAIGHT_JOIN queries with identical join conditions return completely different results. This violates the fundamental principle that STRAIGHT_JOIN should be semantically equivalent to INNER_JOIN, only enforcing table join order.
Specific behavior:
INNER JOIN with (ATAN(0.8699084789709488)) IN (t1.c0) returns empty result set (correct behavior)
STRAIGHT_JOIN with (ATAN(0.08699084789709488)) IN (t1.c0) returns 1 row (1, 0) (incorrect behavior)
Both queries use IN operator in join conditions with ATAN function
The issue suggests inconsistent evaluation of IN conditions between join types
How to repeat:
-- Create test tables
CREATE TABLE t0(c0 SMALLINT(199) UNIQUE);
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
-- Insert test data
INSERT LOW_PRIORITY IGNORE INTO t1(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(0.4672070388859795);
INSERT INTO t0(c0) VALUES(0.9559196584655957);
-- First query (returns empty - correct)
SELECT DISTINCT t0.c0, t1.c0 FROM t0 INNER JOIN t1 ON (ATAN(0.8699084789709488)) IN (t1.c0);
-- Second query (returns 1 row - incorrect)
SELECT DISTINCT t0.c0, t1.c0 FROM t0 STRAIGHT_JOIN t1 ON (ATAN(0.08699084789709488)) IN (t1.c0);
