mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+ | case_marker | +-----------------+ | ===== ENV ===== | +-----------------+ +---------------+ | mysql_version | +---------------+ | 9.7.1 | +---------------+ +---------------------------------+-----------------------+ | information_schema_stats_expiry | optimizer_prune_level | +---------------------------------+-----------------------+ | 0 | 0 | +---------------------------------+-----------------------+ +---------------------+ | case_marker | +---------------------+ | ===== ANALYZE ===== | +---------------------+ +---------------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+----------+ | mysql_join_prefix_selective_filter_repro.t0 | analyze | status | OK | | mysql_join_prefix_selective_filter_repro.t1 | analyze | status | OK | | mysql_join_prefix_selective_filter_repro.t2 | analyze | status | OK | +---------------------------------------------+---------+----------+----------+ +------------------------+ | case_marker | +------------------------+ | ===== DATA SHAPE ===== | +------------------------+ +-----+-----------+--------+--------+--------+---------+--------+ | tbl | row_count | ndv_c0 | ndv_c1 | ndv_c4 | min_c4 | max_c4 | +-----+-----------+--------+--------+--------+---------+--------+ | t0 | 2416 | 2416 | 2416 | 32 | 1 | 32 | | t1 | 2640 | 2640 | 2640 | 2640 | -100000 | 99831 | | t2 | 2592 | 5 | 5 | NULL | NULL | NULL | +-----+-----------+--------+--------+--------+---------+--------+ +-----------------------------------+ | case_marker | +-----------------------------------+ | ===== ZERO-RESULT GUARANTEE ===== | +-----------------------------------+ +--------------------------------------+ | t1_rows_passing_impossible_predicate | +--------------------------------------+ | 0 | +--------------------------------------+ +---------------------------------------------------+ | case_marker | +---------------------------------------------------+ | ===== CASE A: default / EXPLAIN FORMAT=TREE ===== | +---------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=2.82e+6 rows=313114) -> Inner hash join (no condition) (cost=626476 rows=6.26e+6) -> Covering index scan on t2 using i5 (cost=0.109 rows=2592) -> Hash -> Filter: (t0.c4 is not null) (cost=245 rows=2416) -> Table scan on t0 (cost=245 rows=2416) -> Filter: ((t1.c1 = t2.c1) and (t1.c4 < -1796620509)) (cost=0.25 rows=0.05) -> Index lookup on t1 using i2 (c0 = t0.c4) (cost=0.25 rows=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------+ | case_marker | +------------------------------------------------+ | ===== CASE A1: default / EXPLAIN ANALYZE ===== | +------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=2.82e+6 rows=313114) (actual time=7726..7726 rows=0 loops=1) -> Inner hash join (no condition) (cost=626476 rows=6.26e+6) (actual time=1.27..266 rows=6.26e+6 loops=1) -> Covering index scan on t2 using i5 (cost=0.109 rows=2592) (actual time=0.00328..1.58 rows=2592 loops=1) -> Hash -> Filter: (t0.c4 is not null) (cost=245 rows=2416) (actual time=0.00569..1.11 rows=2416 loops=1) -> Table scan on t0 (cost=245 rows=2416) (actual time=0.0051..0.959 rows=2416 loops=1) -> Filter: ((t1.c1 = t2.c1) and (t1.c4 < -1796620509)) (cost=0.25 rows=0.05) (actual time=0.00108..0.00108 rows=0 loops=6.26e+6) -> Index lookup on t1 using i2 (c0 = t0.c4) (cost=0.25 rows=1) (actual time=950e-6..950e-6 rows=0 loops=6.26e+6) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------+ | case_marker | +------------------------------------------------+ | ===== CASE A2: default / EXPLAIN ANALYZE ===== | +------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=2.82e+6 rows=313114) (actual time=7689..7689 rows=0 loops=1) -> Inner hash join (no condition) (cost=626476 rows=6.26e+6) (actual time=1.28..266 rows=6.26e+6 loops=1) -> Covering index scan on t2 using i5 (cost=0.109 rows=2592) (actual time=0.00522..1.6 rows=2592 loops=1) -> Hash -> Filter: (t0.c4 is not null) (cost=245 rows=2416) (actual time=0.00686..1.11 rows=2416 loops=1) -> Table scan on t0 (cost=245 rows=2416) (actual time=0.00621..0.966 rows=2416 loops=1) -> Filter: ((t1.c1 = t2.c1) and (t1.c4 < -1796620509)) (cost=0.25 rows=0.05) (actual time=0.00107..0.00107 rows=0 loops=6.26e+6) -> Index lookup on t1 using i2 (c0 = t0.c4) (cost=0.25 rows=1) (actual time=945e-6..945e-6 rows=0 loops=6.26e+6) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------+ | case_marker | +------------------------------------------------+ | ===== CASE A3: default / EXPLAIN ANALYZE ===== | +------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=2.82e+6 rows=313114) (actual time=7670..7670 rows=0 loops=1) -> Inner hash join (no condition) (cost=626476 rows=6.26e+6) (actual time=1.27..264 rows=6.26e+6 loops=1) -> Covering index scan on t2 using i5 (cost=0.109 rows=2592) (actual time=0.00374..1.59 rows=2592 loops=1) -> Hash -> Filter: (t0.c4 is not null) (cost=245 rows=2416) (actual time=0.00699..1.11 rows=2416 loops=1) -> Table scan on t0 (cost=245 rows=2416) (actual time=0.00649..0.966 rows=2416 loops=1) -> Filter: ((t1.c1 = t2.c1) and (t1.c4 < -1796620509)) (cost=0.25 rows=0.05) (actual time=0.00107..0.00107 rows=0 loops=6.26e+6) -> Index lookup on t1 using i2 (c0 = t0.c4) (cost=0.25 rows=1) (actual time=943e-6..943e-6 rows=0 loops=6.26e+6) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------+ | case_marker | +-----------------------------------------------------------------+ | ===== CASE B: JOIN_PREFIX(t2,t1,t0) / EXPLAIN FORMAT=TREE ===== | +-----------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (t0.c4 = t1.c0) (cost=1.86e+6 rows=1.84e+6) -> Table scan on t0 (cost=575e-6 rows=2416) -> Hash -> Inner hash join (t1.c1 = t2.c1) (cost=23266 rows=7602) -> Filter: (t1.c4 < -1796620509) (cost=0.0789 rows=88) -> Table scan on t1 (cost=0.0789 rows=2640) -> Hash -> Covering index scan on t2 using i5 (cost=263 rows=2592) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------+ | case_marker | +--------------------------------------------------------------+ | ===== CASE B1: JOIN_PREFIX(t2,t1,t0) / EXPLAIN ANALYZE ===== | +--------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (t0.c4 = t1.c0) (cost=1.86e+6 rows=1.84e+6) (actual time=2.67..2.67 rows=0 loops=1) -> Table scan on t0 (cost=575e-6 rows=2416) (never executed) -> Hash -> Inner hash join (t1.c1 = t2.c1) (cost=23266 rows=7602) (actual time=2.67..2.67 rows=0 loops=1) -> Filter: (t1.c4 < -1796620509) (cost=0.0789 rows=88) (actual time=1.45..1.45 rows=0 loops=1) -> Table scan on t1 (cost=0.0789 rows=2640) (actual time=0.00396..1.32 rows=2640 loops=1) -> Hash -> Covering index scan on t2 using i5 (cost=263 rows=2592) (actual time=0.00712..0.989 rows=2592 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------+ | case_marker | +--------------------------------------------------------------+ | ===== CASE B2: JOIN_PREFIX(t2,t1,t0) / EXPLAIN ANALYZE ===== | +--------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (t0.c4 = t1.c0) (cost=1.86e+6 rows=1.84e+6) (actual time=2.69..2.69 rows=0 loops=1) -> Table scan on t0 (cost=575e-6 rows=2416) (never executed) -> Hash -> Inner hash join (t1.c1 = t2.c1) (cost=23266 rows=7602) (actual time=2.69..2.69 rows=0 loops=1) -> Filter: (t1.c4 < -1796620509) (cost=0.0789 rows=88) (actual time=1.36..1.36 rows=0 loops=1) -> Table scan on t1 (cost=0.0789 rows=2640) (actual time=0.00313..1.23 rows=2640 loops=1) -> Hash -> Covering index scan on t2 using i5 (cost=263 rows=2592) (actual time=0.00563..1.09 rows=2592 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------+ | case_marker | +--------------------------------------------------------------+ | ===== CASE B3: JOIN_PREFIX(t2,t1,t0) / EXPLAIN ANALYZE ===== | +--------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (t0.c4 = t1.c0) (cost=1.86e+6 rows=1.84e+6) (actual time=2.63..2.63 rows=0 loops=1) -> Table scan on t0 (cost=575e-6 rows=2416) (never executed) -> Hash -> Inner hash join (t1.c1 = t2.c1) (cost=23266 rows=7602) (actual time=2.63..2.63 rows=0 loops=1) -> Filter: (t1.c4 < -1796620509) (cost=0.0789 rows=88) (actual time=1.36..1.36 rows=0 loops=1) -> Table scan on t1 (cost=0.0789 rows=2640) (actual time=0.00273..1.24 rows=2640 loops=1) -> Hash -> Covering index scan on t2 using i5 (cost=263 rows=2592) (actual time=0.00558..1.03 rows=2592 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------+ | case_marker | +---------------------------------------------------------------+ | ===== CONTROL C: JOIN_ORDER(t2,t1,t0) / EXPLAIN ANALYZE ===== | +---------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (t0.c4 = t1.c0) (cost=1.86e+6 rows=1.84e+6) (actual time=2.62..2.62 rows=0 loops=1) -> Table scan on t0 (cost=575e-6 rows=2416) (never executed) -> Hash -> Inner hash join (t1.c1 = t2.c1) (cost=23266 rows=7602) (actual time=2.62..2.62 rows=0 loops=1) -> Filter: (t1.c4 < -1796620509) (cost=0.0789 rows=88) (actual time=1.34..1.34 rows=0 loops=1) -> Table scan on t1 (cost=0.0789 rows=2640) (actual time=0.00247..1.22 rows=2640 loops=1) -> Hash -> Covering index scan on t2 using i5 (cost=263 rows=2592) (actual time=0.00487..1.04 rows=2592 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------+ | case_marker | +-------------------------------------------------------------+ | ===== CONTROL D: JOIN_FIXED_ORDER() / EXPLAIN ANALYZE ===== | +-------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (t0.c4 = t1.c0) (cost=1.86e+6 rows=1.84e+6) (actual time=2.69..2.69 rows=0 loops=1) -> Table scan on t0 (cost=575e-6 rows=2416) (never executed) -> Hash -> Inner hash join (t1.c1 = t2.c1) (cost=23266 rows=7602) (actual time=2.69..2.69 rows=0 loops=1) -> Filter: (t1.c4 < -1796620509) (cost=0.0789 rows=88) (actual time=1.41..1.41 rows=0 loops=1) -> Table scan on t1 (cost=0.0789 rows=2640) (actual time=0.00247..1.27 rows=2640 loops=1) -> Hash -> Covering index scan on t2 using i5 (cost=263 rows=2592) (actual time=0.00498..1.03 rows=2592 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------+ | case_marker | +------------------+ | ===== DONE ===== | +------------------+