Description:
Column a is primary key of t2 and has the type of INT.
mysql> CREATE TABLE t2 (a int(11) NOT NULL, b bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`a`) );
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> insert into t2 values (0,1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t2 where (a not in (0.2,-0.2));
Empty set (0.00 sec)
mysql> select * from t2 where (a not in (0.5,-0.5));
+---+---+
| a | b |
+---+---+
| 0 | 1 |
+---+---+
1 row in set (0.00 sec)
"select * from t2 where (a not in (0.2,-0.2));" should have the same result like "select * from t2 where (a not in (0.5,-0.5));" because 0 is not in {0.2,-0.2} or {0.5,-0.5}. But "select * from t2 where (a not in (0.2,-0.2));" gets empty result.
Use optimize trace:
mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where (a not in (0.2,-0.2));
Empty set (0.00 sec)
mysql> select trace from `information_schema`.`optimizer_trace`\G;
*************************** 1. row ***************************
trace: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` where (`t2`.`a` not in (0.2,-(0.2)))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`t2`.`a` not in (0.2,-(0.2)))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`t2`.`a` not in (0.2,-(0.2)))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`t2`.`a` not in (0.2,-(0.2)))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`t2`.`a` not in (0.2,-(0.2)))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`t2`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`t2`",
"range_analysis": {
"table_scan": {
"rows": 1,
"cost": 2.45
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"a"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"a < 0",
"0 < a"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 0.71,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 2,
"ranges": [
"a < 0",
"0 < a"
]
},
"rows_for_plan": 2,
"cost_for_plan": 0.71,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`t2`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 2,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
},
"resulting_rows": 2,
"cost": 0.91,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 2,
"cost_for_plan": 0.91,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`t2`.`a` not in (0.2,-(0.2)))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`t2`",
"attached": "(`t2`.`a` not in (0.2,-(0.2)))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`t2`",
"original_table_condition": "(`t2`.`a` not in (0.2,-(0.2)))",
"final_table_condition ": "(`t2`.`a` not in (0.2,<cache>(-(0.2))))"
}
]
},
{
"refine_plan": [
{
"table": "`t2`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
1 row in set (0.01 sec)
Please take attention to below:
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"a < 0",
"0 < a"
],
The ranges to scan is changed from (-inf,-0.2) && (0.2,+inf) to (-inf,0) && (0,+inf).
But in fact, a of the only record matched equals 0 which is not in the ranges so not result is fetched.
Simply converting 0.2 to 0 caused this error.
Perhaps, the ranges should be (-inf,-1)&&{0}&&(1,+inf) or range scan should not be used in such case.
How to repeat:
As above.
Description: Column a is primary key of t2 and has the type of INT. mysql> CREATE TABLE t2 (a int(11) NOT NULL, b bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`a`) ); Query OK, 0 rows affected, 2 warnings (0.10 sec) mysql> insert into t2 values (0,1); Query OK, 1 row affected (0.02 sec) mysql> select * from t2 where (a not in (0.2,-0.2)); Empty set (0.00 sec) mysql> select * from t2 where (a not in (0.5,-0.5)); +---+---+ | a | b | +---+---+ | 0 | 1 | +---+---+ 1 row in set (0.00 sec) "select * from t2 where (a not in (0.2,-0.2));" should have the same result like "select * from t2 where (a not in (0.5,-0.5));" because 0 is not in {0.2,-0.2} or {0.5,-0.5}. But "select * from t2 where (a not in (0.2,-0.2));" gets empty result. Use optimize trace: mysql> SET optimizer_trace='enabled=on'; Query OK, 0 rows affected (0.00 sec) mysql> select * from t2 where (a not in (0.2,-0.2)); Empty set (0.00 sec) mysql> select trace from `information_schema`.`optimizer_trace`\G; *************************** 1. row *************************** trace: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` where (`t2`.`a` not in (0.2,-(0.2)))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t2`.`a` not in (0.2,-(0.2)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t2`.`a` not in (0.2,-(0.2)))" }, { "transformation": "constant_propagation", "resulting_condition": "(`t2`.`a` not in (0.2,-(0.2)))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t2`.`a` not in (0.2,-(0.2)))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t2`", "range_analysis": { "table_scan": { "rows": 1, "cost": 2.45 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "a" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "a < 0", "0 < a" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2, "cost": 0.71, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "a < 0", "0 < a" ] }, "rows_for_plan": 2, "cost_for_plan": 0.71, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "resulting_rows": 2, "cost": 0.91, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 0.91, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t2`.`a` not in (0.2,-(0.2)))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t2`", "attached": "(`t2`.`a` not in (0.2,-(0.2)))" } ] } }, { "finalizing_table_conditions": [ { "table": "`t2`", "original_table_condition": "(`t2`.`a` not in (0.2,-(0.2)))", "final_table_condition ": "(`t2`.`a` not in (0.2,<cache>(-(0.2))))" } ] }, { "refine_plan": [ { "table": "`t2`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 1 row in set (0.01 sec) Please take attention to below: "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "a < 0", "0 < a" ], The ranges to scan is changed from (-inf,-0.2) && (0.2,+inf) to (-inf,0) && (0,+inf). But in fact, a of the only record matched equals 0 which is not in the ranges so not result is fetched. Simply converting 0.2 to 0 caused this error. Perhaps, the ranges should be (-inf,-1)&&{0}&&(1,+inf) or range scan should not be used in such case. How to repeat: As above.