| 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 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.
Description: hi: when use follow statment result is diff in mysql5.6 mysql 5.7.33 and mysql 8.0.23 create table test1(id varchar(2),score int(11)); create table test2(id varchar(2),score int(11)); insert into test1 values('1',6); insert into test2 values('1',8); update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1; select * from test1; select * from test2; mysql 5.6 is: root@zjgldb.sock>select * from test1; +------+-------+ | id | score | +------+-------+ | 1 | 7 | +------+-------+ 1 row in set (0.00 sec) root@zjgldb.sock>select * from test2; +------+-------+ | id | score | +------+-------+ | 1 | 7 | +------+-------+ 1 row in set (0.00 sec) mysql5.7 is: mysql> select * from test1; +------+-------+ | id | score | +------+-------+ | 1 | 7 | +------+-------+ 1 row in set (0.00 sec) mysql> select * from test2; +------+-------+ | id | score | +------+-------+ | 1 | 8 | +------+-------+ 1 row in set (0.00 sec) mysql8.0.23 is: mysql> select * from test1; +------+-------+ | id | score | +------+-------+ | 1 | 7 | +------+-------+ 1 row in set (0.00 sec) mysql> select * from test2; +------+-------+ | id | score | +------+-------+ | 1 | 8 | +------+-------+ 1 row in set (0.00 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 8.0.23-debug | +--------------+ 1 row in set (0.00 sec) which one is correct ? thanks!! How to repeat: create table test1(id varchar(2),score int(11)); create table test2(id varchar(2),score int(11)); insert into test1 values('1',6); insert into test2 values('1',8); update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1; select * from test1; select * from test2;