2023-09-11 mysql-代号m-内连接即使完全匹配但返回都是null值-问题分析
摘要:
2023-09-11 mysql-代号m-内连接即使完全匹配但返回都是null值-问题分析
问题记录: https://devops.aliyun.com/projex/project/36ed2d8a9a29e7f8407c6f5498/bug/59a8e4abf0946200930e1f48c7
DDL:
表结构:
CREATE TABLE t1(id INT NOT NULL,first_name VARCHAR(10) NOT NULL,last_name VARCHAR(10) NOT NULL,sex VARCHAR(5) NOT NULL,score INT NOT NULL,copy_id INT NOT NULL,PRIMARY KEY (`id`)
);CREATE TABLE t2(id INT NOT NULL,first_name VARCHAR(10) NOT NULL,last_name VARCHAR(10) NOT NULL,sex VARCHAR(5) NOT NULL,score INT NOT NULL,copy_id INT NOT NULL,PRIMARY KEY (`id`)
);
插入数据:
t1.csv
1|彭|莉|1|87|1
2|陈|黎益|1|70|2
3|江|黎益|0|72|3
4|侯|黎益|0|77|4
5|孙|景名|1|83|5
6|高|民|1|70|6
7|林|敏丙|1|88|7
8|侯|十骏|1|87|8
9|林|闵静|0|67|9
10|高|三民|1|57|10
11|阮|京|0|55|11
12|李|京|1|93|12
13|吴|丙天|0|44|13
14|侯|莉八|0|57|14
15|彭|依七|0|46|15
16|郑|五|0|60|16
17|王|三|1|88|17
18|钱|黎俊|0|94|18
19|周|静|1|95|19
20|刘|景六|1|51|20
21|彭|骏名|0|91|21
22|赵|景兲|0|56|22
23|刘|六|0|59|23
24|周|诚恬|0|63|24
25|李|诚恬|0|53|25
26|王|骏八|1|97|26
27|高|军五|1|85|27
28|孙|军五|1|48|28
29|赵|黎恬|1|50|29
30|徐|名五|0|98|30t2.csv
1|郑|骏十|0|45|1
2|吴|骏十|0|88|2
3|李|九|1|96|3
4|江|静骏|0|99|4
5|阮|静骏|1|46|5
6|李|景|0|76|6
7|刘|力三|0|55|7
8|徐|乙铭|0|41|8
9|林|闵丁|0|47|9
10|邹|闵丁|0|76|10
11|钱|立七|0|42|11
12|彭|四|0|71|12
13|王|城八|0|87|13
14|林|丁十|0|58|14
15|赵|丁十|0|71|15
16|杨|五|1|81|16
17|林|五|1|90|17
18|赵|城|0|82|18
19|郑|天|1|69|19
20|彭|名丁|0|53|20
21|高|名丁|0|67|21
22|徐|恬成|1|81|22
23|江|恬成|0|85|23
24|刘|益五|0|92|24
25|李|十君|0|77|25
26|彭|四敏|0|84|26
27|柳|民四|1|82|27
28|李|军丁|1|53|28
29|杨|甲|0|84|29
30|刘|甲|1|57|30导入数据命令:
load data local infile '/tmp/t1.csv' into table t1 fields terminated by '|';
load data local infile '/tmp/t2.csv' into table t2 fields terminated by '|';查询SQL:
select t1.id,t1.first_name,t2.last_name from t1 join t2 on t1.id = t2.id where t1.id=10;核心处理:
THD::send_result_set_row
调用堆栈:
#0 THD::send_result_set_row (this=0x7f77b8001040, row_items=...) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_class.cc:2872
#1 0x0000000003dd56c0 in Query_result_send::send_data (this=0x7f77b811ca50, thd=0x7f77b8001040, items=...) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/query_result.cc:100
#2 0x00000000038943e3 in Mondetdb_Adaptor::execute_query (this=0xd37b080, dbname=0x7f77b8011200 "test_join", sql=0x7f77b810d980 "select t1.id,t1.first_name,t2.last_name from t1 join t2 on t1.id = t2.id where t1.id=10", thd=0x7f77b8001040, values=..., query_result=..., send_records_ptr=@0x7f77b811cd10: 1)at /root/work/trunk/mysql-server-mysql-8.0.33/sql/monetdb_adaptor/monetdb_adaptor.cc:436
#3 0x00000000037f8373 in Query_expression::ExecuteIteratorQuery (this=0x7f77b810da30, thd=0x7f77b8001040) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_union.cc:1781
#4 0x00000000037f8706 in Query_expression::execute (this=0x7f77b810da30, thd=0x7f77b8001040) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_union.cc:1852
#5 0x0000000003744eec in Sql_cmd_dml::execute_inner (this=0x7f77b811ca18, thd=0x7f77b8001040) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_select.cc:799
#6 0x0000000003744392 in Sql_cmd_dml::execute (this=0x7f77b811ca18, thd=0x7f77b8001040) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_select.cc:578
#7 0x00000000036c06cb in mysql_execute_command (thd=0x7f77b8001040, first_level=true) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:4714
#8 0x00000000036c2987 in dispatch_sql_command (thd=0x7f77b8001040, parser_state=0x7f782c7f78b0) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:5363
#9 0x00000000036b86d0 in dispatch_command (thd=0x7f77b8001040, com_data=0x7f782c7f89a0, command=COM_QUERY) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:2050
#10 0x00000000036b6618 in do_command (thd=0x7f77b8001040) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/sql_parse.cc:1439
#11 0x000000000393a8be in handle_connection (arg=0xd3953b0) at /root/work/trunk/mysql-server-mysql-8.0.33/sql/conn_handler/connection_handler_per_thread.cc:302
#12 0x00000000059485b9 in pfs_spawn_thread (arg=0xd40c7d0) at /root/work/trunk/mysql-server-mysql-8.0.33/storage/perfschema/pfs.cc:3042
#13 0x00007f7880fef1ca in start_thread () from /lib64/libpthread.so.0
#14 0x00007f787f59fe73 in clone () from /lib64/libc.so.6
数据分析:
Field:
(gdb) p f[0]
$12 = (Field_varstring) { = { = { = {_vptr.Field = 0x8754340 , ptr = 0x7f77b8107a7d "\006闵丁", m_hidden = dd::Column::enum_hidden_type::HT_VISIBLE, m_null_ptr = 0x0, m_is_tmp_nullable = false, m_is_tmp_null = false, m_check_for_truncated_fields_saved = CHECK_FIELD_IGNORE, static dummy_null_buffer = 32 ' ', table = 0x7f77b81070b0, orig_db_name = 0x0, orig_table_name = 0x0, table_name = 0x7f77b8107208, field_name = 0x7f77b8135a58 "last_name", comment = {str = 0x671d320 "", length = 0}, key_start = {map = 0}, part_of_key = {map = 0}, part_of_prefixkey = {map = 0}, part_of_sortkey = {map = 0}, part_of_key_not_extended = {map = 0}, static MAX_VARCHAR_WIDTH = 65535, static MAX_TINY_BLOB_WIDTH = 255, static MAX_SHORT_BLOB_WIDTH = 65535, static MAX_MEDIUM_BLOB_WIDTH = 16777215, static MAX_LONG_BLOB_WIDTH = 4294967295, field_length = 40, flags = 4097, m_field_index = 2, null_bit = 0 '\000', auto_flags = 0 '\000', is_created_from_null_item = false, m_indexed = false, m_engine_attribute = {
--Type for more, q to quit, c to continue without paging--str = 0x6718a70 "", length = 0}, m_secondary_engine_attribute = {str = 0x6718a70 "", length = 0}, m_warnings_pushed = 0, gcol_info = 0x0, stored_in_db = true, m_default_val_expr = 0x0}, members of Field_str: field_charset = 0x8a56ce0 , field_derivation = DERIVATION_IMPLICIT, char_length_cache = 10}, }, members of Field_varstring: length_bytes = 1
}
( (Field_str*) f ).field_charset[0]
(gdb) p ( (Field_str*) f ).field_charset[0]
$14 = {number = 255, primary_number = 0, binary_number = 0, state = 17377, csname = 0x78ac6ee "utf8mb4", m_coll_name = 0x78acd80 "utf8mb4_0900_ai_ci", comment = 0x78ac531 "UTF-8 Unicode", tailoring = 0x0, coll_param = 0x0, ctype = 0x78ac400 "", to_lower = 0x0, to_upper = 0x0, sort_order = 0x0, uca = 0x8a46ee0 , tab_to_uni = 0x0, tab_from_uni = 0x0, caseinfo = 0x8a46ec0 , state_maps = 0xd1d2fa0, ident_map = 0xd1d31a0 "", strxfrm_multiply = 0, caseup_multiply = 1 '\001', casedn_multiply = 1 '\001', mbminlen = 1, mbmaxlen = 4, mbmaxlenlen = 1, min_sort_char = 9, max_sort_char = 1114111, pad_char = 32 ' ', escape_with_backslash_is_dangerous = false, levels_for_compare = 1 '\001', cset = 0x8a74340 , coll = 0x8a51680 , pad_attribute = NO_PAD
}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！