summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/cset_narrowing.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/cset_narrowing.result')
-rw-r--r--mysql-test/main/cset_narrowing.result528
1 files changed, 528 insertions, 0 deletions
diff --git a/mysql-test/main/cset_narrowing.result b/mysql-test/main/cset_narrowing.result
new file mode 100644
index 00000000..e048e85b
--- /dev/null
+++ b/mysql-test/main/cset_narrowing.result
@@ -0,0 +1,528 @@
+set
+@tmp_csetn_os= @@optimizer_switch,
+optimizer_switch='cset_narrowing=on';
+set names utf8mb4;
+create table t1 (
+mb3name varchar(32),
+mb3 varchar(32) collate utf8mb3_general_ci,
+key(mb3)
+);
+insert into t1 select seq, seq from seq_1_to_10000;
+insert into t1 values ('mb3-question-mark', '?');
+insert into t1 values ('mb3-replacement-char', _utf8mb3 0xEFBFBD);
+create table t10 (
+pk int auto_increment primary key,
+mb4name varchar(32),
+mb4 varchar(32) character set utf8mb4 collate utf8mb4_general_ci
+);
+insert into t10 (mb4name, mb4) values
+('mb4-question-mark','?'),
+('mb4-replacement-char', _utf8mb4 0xEFBFBD),
+('mb4-smiley', _utf8mb4 0xF09F988A),
+('1', '1');
+analyze table t1,t10 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t10 analyze status Engine-independent statistics collected
+test.t10 analyze status OK
+#
+# Check that constants are already handled: the following should use
+# ref/range, because constants are converted into utf8mb3.
+#
+select collation('abc');
+collation('abc')
+utf8mb4_general_ci
+explain select * from t1 force index (mb3) where t1.mb3='abc';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref mb3 mb3 99 const 1 Using index condition
+explain select * from t1 force index (mb3) where t1.mb3 in ('abc','cde','xyz');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range mb3 mb3 99 NULL 3 Using index condition
+explain select * from t1 force index (mb3) where t1.mb3 between 'abc' and 'acc';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range mb3 mb3 99 NULL 1 Using index condition
+explain select * from t1 force index (mb3) where t1.mb3 <'000';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range mb3 mb3 99 NULL 1 Using index condition
+# If a constant can't be represented in utf8mb3, an error is produced:
+explain select * from t1 force index (mb3) where t1.mb3='😊';
+ERROR HY000: Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
+#
+# Check ref access on mb3_field=mb4_field
+#
+explain format=json
+select * from t10,t1 where t10.mb4=t1.mb3;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 100,
+ "attached_condition": "t10.mb4 is not null"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ref",
+ "possible_keys": ["mb3"],
+ "key": "mb3",
+ "key_length": "99",
+ "used_key_parts": ["mb3"],
+ "ref": ["test.t10.mb4"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t1.mb3 = t10.mb4"
+ }
+ }
+ ]
+ }
+}
+select * from t10,t1 where t10.mb4=t1.mb3;
+pk mb4name mb4 mb3name mb3
+1 mb4-question-mark ? mb3-question-mark ?
+2 mb4-replacement-char � mb3-replacement-char �
+3 mb4-smiley 😊 mb3-replacement-char �
+4 1 1 1 1
+select * from t10,t1 use index() where t10.mb4=t1.mb3;
+pk mb4name mb4 mb3name mb3
+4 1 1 1 1
+1 mb4-question-mark ? mb3-question-mark ?
+2 mb4-replacement-char � mb3-replacement-char �
+3 mb4-smiley 😊 mb3-replacement-char �
+explain format=json
+select * from t10,t1 where t10.mb4<=>t1.mb3;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ref",
+ "possible_keys": ["mb3"],
+ "key": "mb3",
+ "key_length": "99",
+ "used_key_parts": ["mb3"],
+ "ref": ["test.t10.mb4"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t10.mb4 <=> t1.mb3"
+ }
+ }
+ ]
+ }
+}
+select * from t10,t1 where t10.mb4<=>t1.mb3;
+pk mb4name mb4 mb3name mb3
+1 mb4-question-mark ? mb3-question-mark ?
+2 mb4-replacement-char � mb3-replacement-char �
+3 mb4-smiley 😊 mb3-replacement-char �
+4 1 1 1 1
+set statement optimizer_switch='cset_narrowing=off', join_cache_level=0 for
+explain format=json
+select * from t10,t1 where t10.mb4=t1.mb3;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10002,
+ "filtered": 100,
+ "attached_condition": "t10.mb4 = convert(t1.mb3 using utf8mb4)"
+ }
+ }
+ ]
+ }
+}
+#
+# Check ref access on mb3_field=mb4_expr
+#
+explain format=json
+select * from t10,t1 where t1.mb3=concat('',t10.mb4);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ref",
+ "possible_keys": ["mb3"],
+ "key": "mb3",
+ "key_length": "99",
+ "used_key_parts": ["mb3"],
+ "ref": ["func"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t1.mb3 = concat('',t10.mb4)"
+ }
+ }
+ ]
+ }
+}
+select * from t10,t1 where t1.mb3=concat('',t10.mb4);
+pk mb4name mb4 mb3name mb3
+1 mb4-question-mark ? mb3-question-mark ?
+2 mb4-replacement-char � mb3-replacement-char �
+3 mb4-smiley 😊 mb3-replacement-char �
+4 1 1 1 1
+select * from t10,t1 use index() where t1.mb3=concat('',t10.mb4);
+pk mb4name mb4 mb3name mb3
+4 1 1 1 1
+1 mb4-question-mark ? mb3-question-mark ?
+2 mb4-replacement-char � mb3-replacement-char �
+3 mb4-smiley 😊 mb3-replacement-char �
+# Check that ref optimizer gets the right constant.
+# We need a const table for that, because key=const is handled by
+# coercing the constant.
+#
+# So, we take the smiley:
+select * from t10 where t10.pk=3;
+pk mb4name mb4
+3 mb4-smiley 😊
+set optimizer_trace=1;
+# And see that we've got the Replacement Character in the ranges:
+explain
+select * from t10, t1 where t10.mb4=t1.mb3 and t10.pk=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t1 ref mb3 mb3 99 const 3 Using index condition
+select
+json_detailed(json_extract(trace, '$**.range_scan_alternatives')) as JS
+from
+information_schema.optimizer_trace;
+JS
+[
+ [
+ {
+ "index": "mb3",
+ "ranges":
+ ["(�) <= (mb3) <= (�)"],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 3,
+ "cost": 3.760377105,
+ "chosen": true
+ }
+ ]
+]
+select * from t10, t1 where t10.mb4=t1.mb3 and t10.pk=3;
+pk mb4name mb4 mb3name mb3
+3 mb4-smiley 😊 mb3-replacement-char �
+#
+# Will range optimizer handle t1.mb3>t10.mb4? No...
+#
+explain format=json
+select * from t10, t1 where (t1.mb3=t10.mb4 or t1.mb3='hello') and t10.pk=3;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "const",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["pk"],
+ "ref": ["const"],
+ "rows": 1,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "range",
+ "possible_keys": ["mb3"],
+ "key": "mb3",
+ "key_length": "99",
+ "used_key_parts": ["mb3"],
+ "rows": 4,
+ "filtered": 100,
+ "index_condition": "t1.mb3 = '????' or t1.mb3 = 'hello'"
+ }
+ }
+ ]
+ }
+}
+explain format=json
+select * from t10, t1 where t1.mb3>t10.mb4 and t10.pk=3;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "const",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["pk"],
+ "ref": ["const"],
+ "rows": 1,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10002,
+ "filtered": 100,
+ "attached_condition": "convert(t1.mb3 using utf8mb4) > '????'"
+ }
+ }
+ ]
+ }
+}
+# For comparison, it will handle it when collations match:
+create table t2 (
+mb4name varchar(32),
+mb4 varchar(32) collate utf8mb4_general_ci,
+key(mb4)
+);
+insert into t2 select * from t1;
+explain format=json
+select * from t10, t2 where t2.mb4>t10.mb4 and t10.pk=3;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "const",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["pk"],
+ "ref": ["const"],
+ "rows": 1,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["mb4"],
+ "key": "mb4",
+ "key_length": "131",
+ "used_key_parts": ["mb4"],
+ "rows": 3,
+ "filtered": 100,
+ "index_condition": "t2.mb4 > '????'"
+ }
+ }
+ ]
+ }
+}
+#
+# Check multiple equalities
+#
+# - ref acccess lookup keys do use equality substitution,
+# - concat() arguments don't
+explain format=json
+select straight_join * from t10,t1 force index(mb3),t2
+where
+t1.mb3=t2.mb4 and t2.mb4=t10.mb4 and concat(t1.mb3, t2.mb4, t10.mb4)<>'Bebebe';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 100,
+ "attached_condition": "t10.mb4 is not null and t10.mb4 is not null"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ref",
+ "possible_keys": ["mb3"],
+ "key": "mb3",
+ "key_length": "99",
+ "used_key_parts": ["mb3"],
+ "ref": ["test.t10.mb4"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t1.mb3 = t10.mb4"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": ["mb4"],
+ "key": "mb4",
+ "key_length": "131",
+ "used_key_parts": ["mb4"],
+ "ref": ["test.t10.mb4"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe'"
+ }
+ }
+ ]
+ }
+}
+select json_detailed(json_extract(trace, '$**.condition_processing')) as JS
+from information_schema.optimizer_trace;
+JS
+[
+ {
+ "condition": "WHERE",
+ "original_condition": "t1.mb3 = t2.mb4 and t2.mb4 = t10.mb4 and concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe'",
+ "steps":
+ [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe' and multiple equal(t1.mb3, t2.mb4, t10.mb4)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe' and multiple equal(t1.mb3, t2.mb4, t10.mb4)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe' and multiple equal(t1.mb3, t2.mb4, t10.mb4)"
+ }
+ ]
+ }
+]
+select straight_join * from t10,t1 force index(mb3),t2
+where
+t1.mb3=t2.mb4 and t2.mb4=t10.mb4 and concat(t1.mb3, t2.mb4, t10.mb4)<>'Bebebe';
+pk mb4name mb4 mb3name mb3 mb4name mb4
+1 mb4-question-mark ? mb3-question-mark ? mb3-question-mark ?
+2 mb4-replacement-char � mb3-replacement-char � mb3-replacement-char �
+3 mb4-smiley 😊 mb3-replacement-char � mb3-replacement-char �
+4 1 1 1 1 1 1
+# Equality substitution doesn't happen for constants, for both narrowing
+# and non-narrowing comparisons:
+explain format=json
+select * from t10,t1,t2
+where
+t1.mb3=t2.mb4 and t2.mb4=t10.mb4 and t10.mb4='hello' and
+concat(t1.mb3, t2.mb4, t10.mb4)<>'Bebebe';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t10",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 25,
+ "attached_condition": "t10.mb4 = 'hello'"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ref",
+ "possible_keys": ["mb3"],
+ "key": "mb3",
+ "key_length": "99",
+ "used_key_parts": ["mb3"],
+ "ref": ["const"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t1.mb3 = t10.mb4"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": ["mb4"],
+ "key": "mb4",
+ "key_length": "131",
+ "used_key_parts": ["mb4"],
+ "ref": ["const"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t2.mb4 = t10.mb4 and concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe'"
+ }
+ }
+ ]
+ }
+}
+select json_detailed(json_extract(trace, '$**.condition_processing')) as JS
+from information_schema.optimizer_trace;
+JS
+[
+ {
+ "condition": "WHERE",
+ "original_condition": "t1.mb3 = t2.mb4 and t2.mb4 = t10.mb4 and t10.mb4 = 'hello' and concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe'",
+ "steps":
+ [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "t10.mb4 = 'hello' and concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe' and multiple equal(t1.mb3, t2.mb4, t10.mb4)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "t10.mb4 = 'hello' and concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe' and multiple equal(t1.mb3, t2.mb4, t10.mb4)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "t10.mb4 = 'hello' and concat(convert(t1.mb3 using utf8mb4),t2.mb4,t10.mb4) <> 'Bebebe' and multiple equal(t1.mb3, t2.mb4, t10.mb4)"
+ }
+ ]
+ }
+]
+drop table t2;
+drop table t1, t10;
+set optimizer_switch=@tmp_csetn_os;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 11:11:28 +0000