mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------------+
| case_marker                               |
+-------------------------------------------+
| ===== BUG #120793 SCALED SUPPLEMENT ===== |
+-------------------------------------------+
+----------------------------------------------------------------------+
| note                                                                 |
+----------------------------------------------------------------------+
| This testcase is a stronger/scaled supplement for MySQL Bug #120793. |
+----------------------------------------------------------------------+
+-----------------+
| case_marker     |
+-----------------+
| ===== ENV ===== |
+-----------------+
+---------------+
| mysql_version |
+---------------+
| 9.7.1         |
+---------------+
+---------------------------------+-----------------------+--------------+----------------------+
| information_schema_stats_expiry | optimizer_prune_level | scale_factor | impossible_threshold |
+---------------------------------+-----------------------+--------------+----------------------+
|                               0 |                     0 |           16 |          -1796620509 |
+---------------------------------+-----------------------+--------------+----------------------+
+---------------------+
| case_marker         |
+---------------------+
| ===== ANALYZE ===== |
+---------------------+
+--------------------------------------+---------+----------+----------+
| Table                                | Op      | Msg_type | Msg_text |
+--------------------------------------+---------+----------+----------+
| mysql_bug120793_scaled_supplement.t0 | analyze | status   | OK       |
| mysql_bug120793_scaled_supplement.t1 | analyze | status   | OK       |
| mysql_bug120793_scaled_supplement.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=7729..7729 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.00537..1.11 rows=2416 loops=1)
                -> Table scan on t0  (cost=245 rows=2416) (actual time=0.00461..0.961 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=946e-6..946e-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=7643..7643 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.00545..1.56 rows=2592 loops=1)
        -> Hash
            -> Filter: (t0.c4 is not null)  (cost=245 rows=2416) (actual time=0.00703..1.12 rows=2416 loops=1)
                -> Table scan on t0  (cost=245 rows=2416) (actual time=0.00646..0.971 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=936e-6..936e-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=7646..7646 rows=0 loops=1)
    -> Inner hash join (no condition)  (cost=626476 rows=6.26e+6) (actual time=1.27..265 rows=6.26e+6 loops=1)
        -> Covering index scan on t2 using i5  (cost=0.109 rows=2592) (actual time=0.00397..1.56 rows=2592 loops=1)
        -> Hash
            -> Filter: (t0.c4 is not null)  (cost=245 rows=2416) (actual time=0.00644..1.11 rows=2416 loops=1)
                -> Table scan on t0  (cost=245 rows=2416) (actual time=0.00587..0.959 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=936e-6..936e-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.72..2.72 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.72..2.72 rows=0 loops=1)
            -> Filter: (t1.c4 < -1796620509)  (cost=0.0789 rows=88) (actual time=1.4..1.4 rows=0 loops=1)
                -> Table scan on t1  (cost=0.0789 rows=2640) (actual time=0.00436..1.27 rows=2640 loops=1)
            -> Hash
                -> Covering index scan on t2 using i5  (cost=263 rows=2592) (actual time=0.00653..1.07 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.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.35..1.35 rows=0 loops=1)
                -> Table scan on t1  (cost=0.0789 rows=2640) (actual time=0.00318..1.22 rows=2640 loops=1)
            -> Hash
                -> Covering index scan on t2 using i5  (cost=263 rows=2592) (actual time=0.00511..1.02 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.59..2.59 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.58..2.58 rows=0 loops=1)
            -> Filter: (t1.c4 < -1796620509)  (cost=0.0789 rows=88) (actual time=1.32..1.32 rows=0 loops=1)
                -> Table scan on t1  (cost=0.0789 rows=2640) (actual time=0.00271..1.2 rows=2640 loops=1)
            -> Hash
                -> Covering index scan on t2 using i5  (cost=263 rows=2592) (actual time=0.00481..1.01 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.61..2.61 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.61..2.61 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.00257..1.21 rows=2640 loops=1)
            -> Hash
                -> Covering index scan on t2 using i5  (cost=263 rows=2592) (actual time=0.00509..1.02 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.59..2.59 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.59..2.59 rows=0 loops=1)
            -> Filter: (t1.c4 < -1796620509)  (cost=0.0789 rows=88) (actual time=1.33..1.33 rows=0 loops=1)
                -> Table scan on t1  (cost=0.0789 rows=2640) (actual time=0.00242..1.2 rows=2640 loops=1)
            -> Hash
                -> Covering index scan on t2 using i5  (cost=263 rows=2592) (actual time=0.00457..1.01 rows=2592 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+------------------+
| case_marker      |
+------------------+
| ===== DONE ===== |
+------------------+
