stonedb icon indicating copy to clipboard operation
stonedb copied to clipboard
verified publisher icon stoneatom

bug: incorrect result set.Check that ON DUPLICATE KEY UPDATE，A query result error occurs

Open shangyanwen opened this issue 3 years ago • 2 comments

Describe the problem

CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INTEGER)engine=tianmu;
CREATE TABLE t2(a2 INTEGER PRIMARY KEY, b2 INTEGER)engine=tianmu;
CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON TRUE;
INSERT INTO v(a1,b1) VALUES (11, 0) ON DUPLICATE KEY UPDATE b1=b1+1;
INSERT INTO v(a1,b1) VALUES (11, 0) ON DUPLICATE KEY UPDATE b1=b1+1;
INSERT INTO v(a2,b2) VALUES (21, 0) ON DUPLICATE KEY UPDATE b2=b2+1;
INSERT INTO v(a2,b2) VALUES (21, 0) ON DUPLICATE KEY UPDATE b2=b2+1;
SELECT * FROM v;

#Incorrect test results
mysql> SELECT * FROM v;
+----+------+----+------+
| a1 | b1   | a2 | b2   |
+----+------+----+------+
| 11 |    0 | 21 |    0 |
+----+------+----+------+

Expected behavior

Correct test results（note:Innodb test verification, test results are as follows）

mysql> SELECT * FROM v;
+----+------+----+------+
| a1 | b1   | a2 | b2   |
+----+------+----+------+
| 11 |    1 | 21 |    1 |
+----+------+----+------+

How To Reproduce

CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INTEGER)engine=tianmu;
CREATE TABLE t2(a2 INTEGER PRIMARY KEY, b2 INTEGER)engine=tianmu;
CREATE VIEW v AS SELECT * FROM t1 JOIN t2 ON TRUE;
INSERT INTO v(a1,b1) VALUES (11, 0) ON DUPLICATE KEY UPDATE b1=b1+1;
INSERT INTO v(a1,b1) VALUES (11, 0) ON DUPLICATE KEY UPDATE b1=b1+1;
INSERT INTO v(a2,b2) VALUES (21, 0) ON DUPLICATE KEY UPDATE b2=b2+1;
INSERT INTO v(a2,b2) VALUES (21, 0) ON DUPLICATE KEY UPDATE b2=b2+1;
SELECT * FROM v;

Environment

[root@dev bin]# ./mysqld --version
./mysqld  Ver 5.7.36_v1.0.0_beta-StoneDB for Linux on x86_64 (build-)
build information as follow: 
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7
        Branch name: stonedb-5.7
        Last commit ID: eed32f6
        Last commit time: Date:   Wed Aug 3 11:19:48 2022 +0800
        Build time: Date: Wed Aug  3 13:01:31 CST 2022

Are you interested in submitting a PR to solve the problem?

  • [ ] Yes, I will!

shangyanwen avatar Sep 17 '22 08:09 shangyanwen

The physical layer data file has no data and is not successfully inserted


[root@localhost 0]# hexdump  -C DN
00000000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000afa0  00 00 00 00 00 00 00 00  05 00 00 00 00 00 00 00  |................|
0000afb0  ff ff ff ff 01 00 00 00  00 00 00 00 00 00 00 00  |................|
0000afc0  ff ff ff ff ff ff ff ff  00 00 00 00 00 00 00 00  |................|
0000afd0  23 27 00 00 66 88 35 63  ff ff ff ff ff ff ff ff  |#'..f.5c........|
0000afe0  0b 00 00 00 00 00 00 00  0b 00 00 00 00 00 00 00  |................|
0000aff0  0b 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
0000b000
[root@localhost 0]# tree
.
├── DN
├── filters
│   ├── bloom
│   ├── cmap
│   └── hist
├── META
└── v
    └── 6335886600002723

5 directories, 3 files
[root@localhost 0]# 

[root@localhost 0]# pwd
/stonedb57/install/data/t4/t1.tianmu/columns/0

adofsauron avatar Sep 30 '22 02:09 adofsauron

View data on the physical layer


./stonedb-tools ver ./data/v/6335886600002723


src/main.cpp:16:main	[INFO] file_type = ver
src/main.cpp:17:main	[INFO] file_path = ./data/v/6335886600002723
src/parser.cpp:14:parser_file	[INFO] file_type = ver
src/parser.cpp:15:parser_file	[INFO] file_path = ./data/v/6335886600002723
src/parser_ver.cpp:13:parser_ver	[INFO] file_path = ./data/v/6335886600002723
src/parser_ver.cpp:32:parser_ver_print	[INFO] hdr_total->hdr.numOfRecords = 1
src/parser_ver.cpp:33:parser_ver_print	[INFO] hdr_total->hdr.numOfNulls = 0
src/parser_ver.cpp:34:parser_ver_print	[INFO] hdr_total->hdr.numOfPacks = 1
src/parser_ver.cpp:35:parser_ver_print	[INFO] hdr_total->hdr.numOfDeleted = 0
src/parser_ver.cpp:37:parser_ver_print	[INFO] hdr_total->hdr.auto_inc_next = 0
src/parser_ver.cpp:38:parser_ver_print	[INFO] hdr_total->hdr.min = 11
src/parser_ver.cpp:39:parser_ver_print	[INFO] hdr_total->hdr.max = 11
src/parser_ver.cpp:40:parser_ver_print	[INFO] hdr_total->hdr.dict_ver = 0
src/parser_ver.cpp:41:parser_ver_print	[INFO] hdr_total->hdr.unique = 0
src/parser_ver.cpp:42:parser_ver_print	[INFO] hdr_total->hdr.unique_updated = 0
src/parser_ver.cpp:43:parser_ver_print	[INFO] hdr_total->hdr.natural_size = 8
src/parser_ver.cpp:44:parser_ver_print	[INFO] hdr_total->hdr.compressed_size = 0
src/parser_ver.cpp:51:parser_ver_print	[INFO] arr i: 0, PACK_INDEX: 511
src/main.cpp:22:main	[INFO] parser_file ret = true




[root@localhost stonedb-tools-dev]# ./stonedb-tools dpn ./data/DN,0
src/main.cpp:16:main	[INFO] file_type = dpn
src/main.cpp:17:main	[INFO] file_path = ./data/DN,0
src/parser.cpp:14:parser_file	[INFO] file_type = dpn
src/parser.cpp:15:parser_file	[INFO] file_path = ./data/DN,0
src/parser_dpn.cpp:43:parser_dpn_one	[INFO] file_path_args = ./data/DN,0
src/parser_dpn.cpp:51:parser_dpn_one	[INFO] file_num: 2
src/parser_dpn.cpp:61:parser_dpn_one	[INFO] ./data/DN
src/parser_dpn.cpp:61:parser_dpn_one	[INFO] 0
src/parser_dpn.cpp:69:parser_dpn_one	[INFO] dpn_file = ./data/DN
src/parser_dpn.cpp:70:parser_dpn_one	[INFO] dpn_index = 0
src/parser_dpn.cpp:27:parser_dpn_one	[INFO] file_path = ./data/DN
src/parser_dpn.cpp:28:parser_dpn_one	[INFO] index = 0
src/parser_dpn.cpp:77:parser_dpn_one_print	[INFO] dpn->used = 0
src/parser_dpn.cpp:78:parser_dpn_one_print	[INFO] dpn->local = 0
src/parser_dpn.cpp:79:parser_dpn_one_print	[INFO] dpn->synced = 0
src/parser_dpn.cpp:80:parser_dpn_one_print	[INFO] dpn->null_compressed = 0
src/parser_dpn.cpp:81:parser_dpn_one_print	[INFO] dpn->delete_compressed = 0
src/parser_dpn.cpp:82:parser_dpn_one_print	[INFO] dpn->data_compressed = 0
src/parser_dpn.cpp:83:parser_dpn_one_print	[INFO] dpn->no_compress = 0
src/parser_dpn.cpp:84:parser_dpn_one_print	[INFO] dpn->padding = 0
src/parser_dpn.cpp:85:parser_dpn_one_print	[INFO] dpn->base = 0
src/parser_dpn.cpp:86:parser_dpn_one_print	[INFO] dpn->numOfRecords = 0
src/parser_dpn.cpp:87:parser_dpn_one_print	[INFO] dpn->numOfNulls = 0
src/parser_dpn.cpp:88:parser_dpn_one_print	[INFO] dpn->numOfDeleted = 0
src/parser_dpn.cpp:89:parser_dpn_one_print	[INFO] dpn->dataAddress = 0
src/parser_dpn.cpp:90:parser_dpn_one_print	[INFO] dpn->dataLength = 0
src/parser_dpn.cpp:92:parser_dpn_one_print	[INFO] dpn->xmin = 0
src/parser_dpn.cpp:93:parser_dpn_one_print	[INFO] dpn->xmax = 0
src/parser_dpn.cpp:95:parser_dpn_one_print	[INFO] dpn->min_i = 0
src/parser_dpn.cpp:96:parser_dpn_one_print	[INFO] dpn->min_d = 0
src/parser_dpn.cpp:97:parser_dpn_one_print	[INFO] dpn->min_s = 
src/parser_dpn.cpp:99:parser_dpn_one_print	[INFO] dpn->max_i = 0
src/parser_dpn.cpp:100:parser_dpn_one_print	[INFO] dpn->max_d = 0
src/parser_dpn.cpp:101:parser_dpn_one_print	[INFO] dpn->max_s = 
src/parser_dpn.cpp:104:parser_dpn_one_print	[INFO] dpn->sum_i = 0
src/parser_dpn.cpp:105:parser_dpn_one_print	[INFO] dpn->sum_d = 0
src/parser_dpn.cpp:106:parser_dpn_one_print	[INFO] dpn->maxlen = 0
src/main.cpp:22:main	[INFO] parser_file ret = true





[root@localhost stonedb-tools-dev]# ./stonedb-tools dpn ./data/DN,1
src/main.cpp:16:main	[INFO] file_type = dpn
src/main.cpp:17:main	[INFO] file_path = ./data/DN,1
src/parser.cpp:14:parser_file	[INFO] file_type = dpn
src/parser.cpp:15:parser_file	[INFO] file_path = ./data/DN,1
src/parser_dpn.cpp:43:parser_dpn_one	[INFO] file_path_args = ./data/DN,1
src/parser_dpn.cpp:51:parser_dpn_one	[INFO] file_num: 2
src/parser_dpn.cpp:61:parser_dpn_one	[INFO] ./data/DN
src/parser_dpn.cpp:61:parser_dpn_one	[INFO] 1
src/parser_dpn.cpp:69:parser_dpn_one	[INFO] dpn_file = ./data/DN
src/parser_dpn.cpp:70:parser_dpn_one	[INFO] dpn_index = 1
src/parser_dpn.cpp:27:parser_dpn_one	[INFO] file_path = ./data/DN
src/parser_dpn.cpp:28:parser_dpn_one	[INFO] index = 1
src/parser_dpn.cpp:77:parser_dpn_one_print	[INFO] dpn->used = 0
src/parser_dpn.cpp:78:parser_dpn_one_print	[INFO] dpn->local = 0
src/parser_dpn.cpp:79:parser_dpn_one_print	[INFO] dpn->synced = 0
src/parser_dpn.cpp:80:parser_dpn_one_print	[INFO] dpn->null_compressed = 0
src/parser_dpn.cpp:81:parser_dpn_one_print	[INFO] dpn->delete_compressed = 0
src/parser_dpn.cpp:82:parser_dpn_one_print	[INFO] dpn->data_compressed = 0
src/parser_dpn.cpp:83:parser_dpn_one_print	[INFO] dpn->no_compress = 0
src/parser_dpn.cpp:84:parser_dpn_one_print	[INFO] dpn->padding = 0
src/parser_dpn.cpp:85:parser_dpn_one_print	[INFO] dpn->base = 0
src/parser_dpn.cpp:86:parser_dpn_one_print	[INFO] dpn->numOfRecords = 0
src/parser_dpn.cpp:87:parser_dpn_one_print	[INFO] dpn->numOfNulls = 0
src/parser_dpn.cpp:88:parser_dpn_one_print	[INFO] dpn->numOfDeleted = 0
src/parser_dpn.cpp:89:parser_dpn_one_print	[INFO] dpn->dataAddress = 0
src/parser_dpn.cpp:90:parser_dpn_one_print	[INFO] dpn->dataLength = 0
src/parser_dpn.cpp:92:parser_dpn_one_print	[INFO] dpn->xmin = 0
src/parser_dpn.cpp:93:parser_dpn_one_print	[INFO] dpn->xmax = 0
src/parser_dpn.cpp:95:parser_dpn_one_print	[INFO] dpn->min_i = 0
src/parser_dpn.cpp:96:parser_dpn_one_print	[INFO] dpn->min_d = 0
src/parser_dpn.cpp:97:parser_dpn_one_print	[INFO] dpn->min_s = 
src/parser_dpn.cpp:99:parser_dpn_one_print	[INFO] dpn->max_i = 0
src/parser_dpn.cpp:100:parser_dpn_one_print	[INFO] dpn->max_d = 0
src/parser_dpn.cpp:101:parser_dpn_one_print	[INFO] dpn->max_s = 
src/parser_dpn.cpp:104:parser_dpn_one_print	[INFO] dpn->sum_i = 0
src/parser_dpn.cpp:105:parser_dpn_one_print	[INFO] dpn->sum_d = 0
src/parser_dpn.cpp:106:parser_dpn_one_print	[INFO] dpn->maxlen = 0
src/main.cpp:22:main	[INFO] parser_file ret = true

adofsauron avatar Sep 30 '22 02:09 adofsauron

ACK

lujiashun avatar Oct 20 '22 05:10 lujiashun 

(gdb) bt
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007f4c0b096859 in __GI_abort () at abort.c:79
#2  0x00007f4c0b096729 in __assert_fail_base (fmt=0x7f4c0b22c588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n",
    assertion=0x56370c43dfa0 "send_metadata || field_types == 0 || field_types[field_pos] == MYSQL_TYPE_INT24 || field_types[field_pos] == MYSQL_TYPE_LONG", file=0x56370c43d808 "/data/codebase/stonedb/sql/protocol_classic.cc", line=1351,
    function=<optimized out>) at assert.c:92
#3  0x00007f4c0b0a7fd6 in __GI___assert_fail (
    assertion=0x56370c43dfa0 "send_metadata || field_types == 0 || field_types[field_pos] == MYSQL_TYPE_INT24 || field_types[field_pos] == MYSQL_TYPE_LONG", file=0x56370c43d808 "/data/codebase/stonedb/sql/protocol_classic.cc", line=1351,
    function=0x56370c43df68 "virtual bool Protocol_text::store_long(longlong)") at assert.c:101
#4  0x000056370b24db06 in Protocol_text::store_long (this=0x7f46ec001fb8, from=11)
    at /data/codebase/stonedb/sql/protocol_classic.cc:1351
#5  0x000056370ac60f12 in Item::send (this=0x7f46ec906720, protocol=0x7f46ec001fb8, buffer=0x7f4c047ec260)
    at /data/codebase/stonedb/sql/item.cc:7572
#6  0x000056370b2e2028 in THD::send_result_set_row (this=0x7f46ec000e10, row_items=0x7f46ec005c98)
    at /data/codebase/stonedb/sql/sql_class.cc:4757
#7  0x000056370b2dbeff in Query_result_send::send_data (this=0x7f46ec010700, items=...)
    at /data/codebase/stonedb/sql/sql_class.cc:2742
#8  0x000056370bcaee1a in Tianmu::core::ResultSender::SendRecord (this=0x7f46ec8fd590,
    r=std::vector of length 4, capacity 4 = {...}) at /data/codebase/stonedb/storage/tianmu/core/engine_results.cpp:356
#9  0x000056370bcae815 in Tianmu::core::ResultSender::SendRow (this=0x7f46ec8fd590,
    record=std::vector of length 4, capacity 4 = {...}, owner=0x7f46ec8ff330)
    at /data/codebase/stonedb/storage/tianmu/core/engine_results.cpp:262
#10 0x000056370bd86e73 in Tianmu::core::TempTable::SendResult (this=0x7f46ec8ff330, limit=1, offset=0, sender=..., pagewise=false)
    at /data/codebase/stonedb/storage/tianmu/core/temp_table_low.cpp:482
#11 0x000056370bd85b84 in Tianmu::core::TempTable::FillMaterializedBuffers (this=0x7f46ec8ff330, local_limit=1, local_offset=0,
    sender=0x7f46ec8fd590, pagewise=false) at /data/codebase/stonedb/storage/tianmu/core/temp_table_low.cpp:310
#12 0x000056370bd6e2d3 in Tianmu::core::TempTable::Materialize (this=0x7f46ec8ff330, in_subq=false, sender=0x7f46ec8fd590,
    lazy=false) at /data/codebase/stonedb/storage/tianmu/core/temp_table.cpp:1958
#13 0x000056370bca82e8 in Tianmu::core::Engine::Execute (this=0x56370ee88c20, thd=0x7f46ec000e10, lex=0x7f46ec003138,
    result_output=0x7f46ec010700, unit_for_union=0x0) at /data/codebase/stonedb/storage/tianmu/core/engine_execute.cpp:423
#14 0x000056370bca7368 in Tianmu::core::Engine::HandleSelect (this=0x56370ee88c20, thd=0x7f46ec000e10, lex=0x7f46ec003138,
    result=@0x7f4c047ed458: 0x7f46ec010700, setup_tables_done_option=0, res=@0x7f4c047ed44c: 0,
    optimize_after_tianmu=@0x7f4c047ed454: 1, tianmu_free_join=@0x7f4c047ed450: 1, with_insert=0)
    at /data/codebase/stonedb/storage/tianmu/core/engine_execute.cpp:238
#15 0x000056370bda2158 in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7f46ec000e10, lex=0x7f46ec003138,
    result=@0x7f4c047ed458: 0x7f46ec010700, setup_tables_done_option=0, res=@0x7f4c047ed44c: 0,
    optimize_after_tianmu=@0x7f4c047ed454: 1, tianmu_free_join=@0x7f4c047ed450: 1, with_insert=0)
    at /data/codebase/stonedb/storage/tianmu/handler/ha_rcengine.cpp:81
--Type <RET> for more, q to quit, c to continue without paging--
#16 0x000056370b346ef2 in execute_sqlcom_select (thd=0x7f46ec000e10, all_tables=0x7f46ec0067a0)
    at /data/codebase/stonedb/sql/sql_parse.cc:5182
#17 0x000056370b33ff81 in mysql_execute_command (thd=0x7f46ec000e10, first_level=true)
    at /data/codebase/stonedb/sql/sql_parse.cc:2831
#18 0x000056370b347f80 in mysql_parse (thd=0x7f46ec000e10, parser_state=0x7f4c047ee5f0)
    at /data/codebase/stonedb/sql/sql_parse.cc:5621
#19 0x000056370b33cc3d in dispatch_command (thd=0x7f46ec000e10, com_data=0x7f4c047eeea0, command=COM_QUERY)
    at /data/codebase/stonedb/sql/sql_parse.cc:1495
#20 0x000056370b33bab4 in do_command (thd=0x7f46ec000e10) at /data/codebase/stonedb/sql/sql_parse.cc:1034
#21 0x000056370b484252 in handle_connection (arg=0x563713a6dbc0)
    at /data/codebase/stonedb/sql/conn_handler/connection_handler_per_thread.cc:313
#22 0x000056370bbce251 in pfs_spawn_thread (arg=0x5637137e8ba0) at /data/codebase/stonedb/storage/perfschema/pfs.cc:2197
#23 0x00007f4c0b3bf609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#24 0x00007f4c0b193133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
(gdb)

lujiashun avatar Oct 20 '22 06:10 lujiashun

1 fisrtly, in debug mode，there is a assertion failure， just fix it； 2 secondly, the release version will not cause this assertion failure，and will give the result，but the result is incorrect； 3 if execute the sql one by one for at least 1 seconds interval，we can see the result is correct； 4 the root reason is because of the variable ‘tianmu_insert_delayed’，its default value is ON， if inset one row，the data is not stored in tiamu at that time（stored later），stored in the rocksdb or memory； if setted off，the row will be dirrectly inserted into tianmu，so query result will be correct；

lujiashun avatar Oct 20 '22 11:10 lujiashun