Summary

Equivalent STRAIGHT_JOIN with swapped join order returns different row counts (FLOAT ZEROFILL + index on t0)

Severity

• Wrong result: two queries that are semantically equivalent INNER joins (only STRAIGHT_JOIN fixes the join order) return different result sets.

Environment

• Product: MariaDB Server
• Reproduced on: 12.3.1

Actual behaviour

With index i1 on t0(c0):

• SELECT COUNT( * ) FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; → 2
• SELECT COUNT( * ) FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0; → 0

After dropping the index on t0, both COUNTs are 2 (consistent with each other), which points to an index/ref-related code path.

Expected behaviour

For the same ON t0.c0 = t1.c0 inner-join predicate, swapping the left/right operands of STRAIGHT_JOIN must yield the same result cardinality (independent of whether STRAIGHT_JOIN is used).

Minimal reproducible SQL

SET sql_mode = '';

DROP TABLE IF EXISTS t0, t1;

CREATE TABLE t0 (c0 FLOAT ZEROFILL) ENGINE = MEMORY;

CREATE TABLE t1 (c0 FLOAT ZEROFILL) ENGINE = MEMORY;

INSERT INTO t0 VALUES (0), (-0);

CREATE INDEX i1 ON t0 (c0);

INSERT INTO t1 VALUES ('-0');

SELECT COUNT(*) AS n FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0;

SELECT COUNT(*) AS n FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;

DROP TABLE t0, t1;