Bug #103085 result is diff in 5.6 5.7 and 8.0
Submitted: 24 Mar 8:05 Modified: 24 Mar 13:56
Reporter: peng gao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[24 Mar 8:05] peng gao
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;
[24 Mar 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 12:10] peng gao
sorry 
5.7:

Field_long address is 0x7fff909eadf0 and Field ptr address is 0x7fff909eacf4,
is the same.
[24 Mar 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 12:31] peng gao
in general  5.6 result is better.
thanks!!
[24 Mar 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.