Integer column compared with string constant is not propagated through join equality

-- Integer column compared with string constant is not propagated through

-- join equality t2.a = t1.a, leading to a bad plan on skewed data.

CREATE DATABASE IF NOT EXISTS mdev_test;

USE mdev_test;

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (

  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  a INT UNSIGNED NOT NULL,

  k INT UNSIGNED NOT NULL,

  b INT UNSIGNED NOT NULL,

  UNIQUE KEY a_k (a, k)

) ENGINE=InnoDB;

CREATE TABLE t2 (

  a INT UNSIGNED NOT NULL,

  id INT UNSIGNED NOT NULL,

  b INT UNSIGNED NOT NULL,

  c SMALLINT UNSIGNED NOT NULL,

  pad1 DOUBLE NOT NULL DEFAULT 0,  -- non-indexed columns: makes a_b_c non-covering

  pad2 DOUBLE NOT NULL DEFAULT 0,

  PRIMARY KEY (a, id),

  KEY a_b_c (a, b, c)

) ENGINE=InnoDB;

-- 1000 small groups (a = 1000..1999): 1 b value, 100 rows each

INSERT INTO t2 (a, id, b, c)

SELECT 1000 + s.seq DIV 100, s.seq, 1, 300 + s.seq % 200

FROM seq_0_to_99999 s;

-- one large group (a = 46), skewed: 1000 b values x 1000 rows

INSERT INTO t2 (a, id, b, c)

SELECT 46, s.seq, s.seq DIV 1000, 300 + s.seq % 200

FROM seq_0_to_999999 s;

INSERT INTO t1 (a, k, b)

SELECT 46, s.seq, s.seq % 1000 FROM seq_1_to_10000 s;

ANALYZE TABLE t1, t2;

-- Query A: integer constants

-- t2 uses a_b_c with ref const,t1.b; r_rows ~505; fast (~7 ms)

ANALYZE SELECT t1.k, t2.id, t2.pad1, t2.pad2

FROM t1 JOIN t2 ON t2.a = t1.a AND t2.b = t1.b

WHERE t1.a = 46

  AND t1.k IN (101,102,103,104,105,106,107)

  AND t2.c BETWEEN 300 AND 400;

-- Query B: identical, but string constant

-- '46' is not propagated to t2.a; t2 uses PRIMARY prefix (a) with ref t1.a,

-- estimated ~1100 rows, actual r_rows = 1,000,000 per loop; slow (~1.2 s)

ANALYZE SELECT t1.k, t2.id, t2.pad1, t2.pad2

FROM t1 JOIN t2 ON t2.a = t1.a AND t2.b = t1.b

WHERE t1.a = '46'

  AND t1.k IN (101,102,103,104,105,106,107)

  AND t2.c BETWEEN 300 AND 400;