-- 8.0.25 build cat docs/INFO_SRC commit: 5213d6a67df504e4b36f2219c8e103487752713d date: 2021-04-23 17:00:38 +0200 build-date: 2021-04-23 15:07:39 +0000 short: 5213d6a67df branch: mysql-8.0.25-release MySQL source 8.0.25 -- rm -rf 104083/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/104083 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/104083 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/104083/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test Database changed mysql> CREATE TABLE test_table(a int not null auto_increment PRIMARY KEY, b binary(32), c datetime, d int, KEY i_bc(b,c), KEY i_bd(b,d)); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO test_table(b,c) VALUES(HEX(UUID_TO_BIN(UUID())),FROM_UNIXTIME(2000000000*RAND())); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO test_table(b,c) SELECT HEX(UUID_TO_BIN(UUID())), FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT HEX(UUID_TO_BIN(UUID())), FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT HEX(UUID_TO_BIN(UUID())), FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT HEX(UUID_TO_BIN(UUID())), FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 16 rows affected (0.01 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 32 rows affected (0.01 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 64 rows affected (0.02 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 128 rows affected (0.02 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 256 rows affected (0.06 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 512 rows affected (0.06 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 1024 rows affected (0.09 sec) Records: 1024 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table;Query OK, 2048 rows affected (0.14 sec) Records: 2048 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 4096 rows affected (0.35 sec) Records: 4096 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 8192 rows affected (0.12 sec) Records: 8192 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 16384 rows affected (0.33 sec) Records: 16384 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 32768 rows affected (0.55 sec) Records: 32768 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_table(b,c) SELECT b, FROM_UNIXTIME(2000000000*RAND()) FROM test_table; Query OK, 65536 rows affected (1.12 sec) Records: 65536 Duplicates: 0 Warnings: 0 mysql> SELECT b FROM test_table LIMIT 1,1; +--------------------------------------------------------------------+ | b | +--------------------------------------------------------------------+ | 0x3131433646453032443335383131454242443239303230303137303146424432 | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT COUNT(*) FROM test_table WHERE b=x'3131433646453032443335383131454242443239303230303137303146424432' AND c < '2019-01-01 00:00:00'; +----+-------------+------------+------------+------+---------------+------+---------+-------+-------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+-------+-------+----------+------------------------------------+ | 1 | SIMPLE | test_table | NULL | ref | i_bc,i_bd | i_bd | 33 | const | 16000 | 33.33 | Using index condition; Using where | +----+-------------+------------+------------+------+---------------+------+---------+-------+-------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) -- SET optimizer_trace='enabled=on'; SET optimizer_trace_max_mem_size=1000000; SELECT trace FROM information_schema.optimizer_trace INTO OUTFILE '/tmp/bug.json' LINES TERMINATED BY ''; - cat /tmp/bug.json { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `COUNT(*)` from `test_table` where ((`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432) and (`test_table`.`c` < '2019-01-01 00:00:00'))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432) and (`test_table`.`c` < '2019-01-01 00:00:00'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`test_table`.`c` < '2019-01-01 00:00:00') and multiple equal(0x3131433646453032443335383131454242443239303230303137303146424432, `test_table`.`b`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`test_table`.`c` < '2019-01-01 00:00:00') and multiple equal(0x3131433646453032443335383131454242443239303230303137303146424432, `test_table`.`b`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`test_table`.`c` < TIMESTAMP'2019-01-01 00:00:00') and multiple equal(0x3131433646453032443335383131454242443239303230303137303146424432, `test_table`.`b`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`test_table`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`test_table`", "field": "b", "equals": "0x3131433646453032443335383131454242443239303230303137303146424432", "null_rejecting": true }, { "table": "`test_table`", "field": "b", "equals": "0x3131433646453032443335383131454242443239303230303137303146424432", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`test_table`", "range_analysis": { "table_scan": { "rows": 130750, "cost": 13213.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "i_bc", "usable": true, "key_parts": [ "b", "c", "a" ] }, { "index": "i_bd", "usable": true, "key_parts": [ "b", "d", "a" ] } ], "best_covering_index_scan": { "index": "i_bc", "cost": 13246.4, "chosen": false, "cause": "cost" }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "i_bc", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "i_bd", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "i_bc", "ranges": [ "0x3131433646453032443335383131454242443239303230303137303146424432 <= b <= 0x3131433646453032443335383131454242443239303230303137303146424432 AND NULL < c < 0x99a2020000" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 13596, "cost": 1377.65, "chosen": true }, { "index": "i_bd", "ranges": [ "0x3131433646453032443335383131454242443239303230303137303146424432 <= b <= 0x3131433646453032443335383131454242443239303230303137303146424432" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 16000, "cost": 5600.26, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "i_bc", "rows": 13596, "ranges": [ "0x3131433646453032443335383131454242443239303230303137303146424432 <= b <= 0x3131433646453032443335383131454242443239303230303137303146424432 AND NULL < c < 0x99a2020000" ] }, "rows_for_plan": 13596, "cost_for_plan": 1377.65, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`test_table`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "i_bc", "chosen": false, "cause": "range_uses_more_keyparts" }, { "access_type": "ref", "index": "i_bd", "rows": 16000, "cost": 2008.75, "chosen": true }, { "rows_to_scan": 13596, "access_type": "range", "range_details": { "used_index": "i_bc" }, "resulting_rows": 13596, "cost": 2737.25, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 16000, "cost_for_plan": 2008.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432) and (`test_table`.`c` < TIMESTAMP'2019-01-01 00:00:00'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`test_table`", "attached": "((`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432) and (`test_table`.`c` < TIMESTAMP'2019-01-01 00:00:00'))" } ] } }, { "optimizing_distinct_group_by_order_by": { } }, { "finalizing_table_conditions": [ { "table": "`test_table`", "original_table_condition": "((`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432) and (`test_table`.`c` < TIMESTAMP'2019-01-01 00:00:00'))", "final_table_condition ": "((`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432) and (`test_table`.`c` < TIMESTAMP'2019-01-01 00:00:00'))" } ] }, { "refine_plan": [ { "table": "`test_table`", "pushed_index_condition": "(`test_table`.`b` = 0x3131433646453032443335383131454242443239303230303137303146424432)", "table_condition_attached": "(`test_table`.`c` < TIMESTAMP'2019-01-01 00:00:00')" } ] }, { "considering_tmp_tables": [ ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] }