Bug #103085 | result is diff in 5.6 5.7 and 8.0 | ||
---|---|---|---|
Submitted: | 24 Mar 2021 8:05 | Modified: | 24 Mar 2021 13:56 |
Reporter: | peng gao | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | OS: | Any | |
Assigned to: | CPU Architecture: | Any |
[24 Mar 2021 8:05]
peng gao
[24 Mar 2021 12:01]
peng gao
Hi: 5.6 here is false but 5.7/8.0 first loop is ture 2029 if (table == table_to_update) (gdb) p table == table_to_update $9 = false (gdb) bt #0 multi_update::send_data (this=0x7fff18050a10, not_used_values=...) at /opt/mysql/mysql-5.6.25/sql/sql_update.cc:2029 #1 0x00000000007ac1af in end_send (join=0x7fff18050ae0, join_tab=0x7fff18059438, end_of_records=false) at /opt/mysql/mysql-5.6.25/sql/sql_executor.cc:2776 #2 0x00000000007a971c in evaluate_join_record (join=0x7fff18050ae0, join_tab=0x7fff18059138) at /opt/mysql/mysql-5.6.25/sql/sql_executor.cc:1601 5.6 use alway use do { tbl->file->position(tbl->record[0]); memcpy((char*) tmp_table->field[field_num]->ptr, (char*) tbl->file->ref, tbl->file->ref_length); /* For outer joins a rowid field may have no NOT_NULL_FLAG, so we have to reset NULL bit for this field. (set_notnull() resets NULL bit only if available). */ tmp_table->field[field_num]->set_notnull(); field_num++; } while ((tbl= tbl_it++)); /* Store regular updated fields in the row. */ fill_record(thd, tmp_table->field + 1 + unupdated_check_opt_tables.elements, *values_for_table[offset], 1, NULL); memcpy like a memory deep copy,so Field_long::store->longstore(ptr,res) not change source memory values, But 5.7 /8.0 here Field_long::store first (table == table_to_update) is true,so will change source memory values. debug like 5.7: Field_long address is 0X7fff90006bf8 and Field ptr address is 0x7fff909eacf4, is the same. Breakpoint 18, Item_func_plus::int_op (this=0x7fff90006bf8) at /opt/percona-server-locks-detail-5.7.22/sql/item_func.cc:1790 1790 longlong val0= args[0]->val_int(); (gdb) n 1791 longlong val1= args[1]->val_int(); (gdb) p ((Item_field*)args[0])->field->ptr $86 = (uchar *) 0x7fff909eacf4 "G" (gdb) p ((Item_field*)args[0])->field $87 = (Field *) 0x7fff909eadf0 (gdb) n 2021-03-24T11:21:15.651272Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 35995ms. The settings might not be optimal. (flushed=0, during the time.) 1792 longlong res= val0 + val1; (gdb) n 1793 bool res_unsigned= FALSE; (gdb) n 1795 if ((null_value= args[0]->null_value || args[1]->null_value)) (gdb) n 1803 if (args[0]->unsigned_flag) (gdb) n 1820 if (args[1]->unsigned_flag) (gdb) n 1836 if (val0 >=0 && val1 >= 0) (gdb) n 1837 res_unsigned= TRUE; (gdb) n 1842 return check_integer_overflow(res, res_unsigned); (gdb) n 1846 } (gdb) n Item_func_numhybrid::val_int (this=0x7fff90006bf8) at /opt/percona-server-locks-detail-5.7.22/sql/item_func.cc:1480 1480 } (gdb) n Item::save_in_field_inner (this=0x7fff90006bf8, field=0x7fff909eadf0, no_conversions=false) at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:6883 6883 if (null_value) (gdb) n 6885 field->set_notnull(); (gdb) n 6886 return field->store(nr, unsigned_flag); (gdb) n Breakpoint 30, Field_long::store (this=0x7fff909eadf0, nr=72, unsigned_val=false) at /opt/percona-server-locks-detail-5.7.22/sql/field.cc:4121 4121 ASSERT_COLUMN_MARKED_FOR_WRITE; (gdb) p ptr $88 = (uchar *) 0x7fff909eacf4 "G" (gdb) 5.6: them are not the same. (gdb) n 1316 longlong val1= args[1]->val_int(); (gdb) p ((Item_field*)args[0])->field->ptr $6 = (uchar *) 0x7fff18045afc "\026" (gdb) p ((Item_field*)args[0])->field $7 = (Field *) 0x7fff1803f8a0 (gdb) n 1317 longlong res= val0 + val1; (gdb) n 1318 bool res_unsigned= FALSE; (gdb) n 1320 if ((null_value= args[0]->null_value || args[1]->null_value)) (gdb) n 1328 if (args[0]->unsigned_flag) (gdb) n 1345 if (args[1]->unsigned_flag) (gdb) n 1361 if (val0 >=0 && val1 >= 0) (gdb) n 1362 res_unsigned= TRUE; (gdb) n 1367 return check_integer_overflow(res, res_unsigned); (gdb) n 1371 } (gdb) n Item_func_numhybrid::val_int (this=0x7fff18006a58) at /opt/mysql/mysql-5.6.25/sql/item_func.cc:1013 1013 } (gdb) n Item::save_in_field (this=0x7fff18006a58, field=0x7fff18057068, no_conversions=false) at /opt/mysql/mysql-5.6.25/sql/item.cc:6339 6339 if (null_value) (gdb) n 6341 field->set_notnull(); (gdb) n 6342 error=field->store(nr, unsigned_flag); (gdb) S Field_long::store (this=0x7fff18057068, nr=23, unsigned_val=false) at /opt/mysql/mysql-5.6.25/sql/field.cc:3750 3750 ASSERT_COLUMN_MARKED_FOR_WRITE; (gdb) p ptr $8 = (uchar *) 0x7fff1805730f "" new test in 5.7 : drop table test1; drop table test3; create table test1(id varchar(2),s1 int,s2 int); create table test3(id varchar(2),s1 int,s2 int); insert into test1 values('1',6,6); insert into test3 values('1',6,6); update test1 a join test3 b on a.id=b.id set a.s1=a.s1+1,b.s1=a.s1+1,b.s2=a.s1+1,a.s2=a.s1+1 where a.id=1; mysql> select * from test1; +------+------+------+ | id | s1 | s2 | +------+------+------+ | 1 | 7 | 8 | +------+------+------+ 1 row in set (3.68 sec) mysql> select * from test3; +------+------+------+ | id | s1 | s2 | +------+------+------+ | 1 | 8 | 8 | +------+------+------+ test3 table test1.s2 and test3.s1 test3.s2 is the same . thanks!!
[24 Mar 2021 12:10]
peng gao
sorry 5.7: Field_long address is 0x7fff909eadf0 and Field ptr address is 0x7fff909eacf4, is the same.
[24 Mar 2021 12:21]
peng gao
new test in 5.6 mysql> create table test1(id varchar(2),s1 int,s2 int); Query OK, 0 rows affected (0.03 sec) mysql> create table test3(id varchar(2),s1 int,s2 int); Query OK, 0 rows affected (0.03 sec) mysql> insert into test1 values('1',6,6); Query OK, 1 row affected (0.01 sec) mysql> insert into test3 values('1',6,6); Query OK, 1 row affected (0.02 sec) mysql> update test1 a join test3 b on a.id=b.id set a.s1=a.s1+1,b.s1=a.s1+1,b.s2=a.s1+1,a.s2=a.s1+1 where a.id=1; Query OK, 2 rows affected (0.71 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from test1; +------+------+------+ | id | s1 | s2 | +------+------+------+ | 1 | 7 | 7 | +------+------+------+ 1 row in set (2.53 sec) mysql> select * from test3; +------+------+------+ | id | s1 | s2 | +------+------+------+ | 1 | 7 | 7 | +------+------+------+ 1 row in set (0.00 sec) thanks!!
[24 Mar 2021 12:31]
peng gao
in general 5.6 result is better. thanks!!
[24 Mar 2021 13:56]
MySQL Verification Team
Hi Mr. gao, Thank you for your bug report. However, this is not a bug. Unlike version 5.6, versions 5.7 and 8.0 follow SQL standard on this point, particularly certain lemmas in the paragraph 14 and 15. Not a bug.