MySQL Bugs: #78598: Update results in wrong nr of columns updated (null = null)

Bug #78598	Update results in wrong nr of columns updated (null = null)
Submitted:	28 Sep 2015 8:28	Modified:	15 Nov 2015 13:45
Reporter:	Andre Timmer	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	multiple index incorrect null

Description:
Nr of rows updated in MySQL 5.6 is no longer correct (was OK in MySQL 5.1).

Script in "How to repeat" shows an update statement with updates rows where comparison of columns with null values result in a match. Under certain conditions of course, see below.

Summary:
- first column with null values on normal or unique muliple column index results in incorrect behaviour 
  for update statements, select statements still work as intended

Note:
- for inserts this problem is not there
- for deletes this problem also exists

How to repeat:
drop table if exists test;
drop table if exists test_tmp;

create table test (id integer, col1 varchar(12), comment varchar(200)
, primary key (id)
, index i1    (col1)
) engine innodb default charset=utf8;

insert into test (id,col1) values (1, 'row 1');
insert into test (id,col1) values (2, 'row 2');
insert into test (id,col1) values (3, 'row 3');
insert into test (id,col1) values (4, null);
insert into test (id,col1) values (5, 'row 5');

-- rows with col1 value already present
insert into test (id,col1) values (6, null);
insert into test (id,col1) values (7, 'row 5');

create table test_tmp as select col1, id id_max from test limit 0;
alter table test_tmp add index i1 (col1, id_max);
insert into test_tmp select col1, max(id) from test group by 1;

show create table test;
show create table test_tmp;

-- should be 1
select * from  test aa
where  exists (
              select ''
              from   test_tmp bb
              where  aa.col1  = bb.col1
              and    aa.id   != bb.id_max
              );

-- should set 1 row to found (not the row with col1 is null)
update test aa
set    comment = 'Found'
where  exists (
              select ''
              from   test_tmp bb
              where  aa.col1  = bb.col1
              and    aa.id   != bb.id_max
              );

select * from test;
select * from test_tmp;

drop table test;
drop table test_tmp;

-- script works as intended
1) without: alter table test_tmp add index i1 (col1, id_max);
2) with:    alter table test_tmp add index i1 (col1);
3) with:    alter table test_tmp add primary key (col1, id_max);

-- what doesn't work is the following:
a) alter table test_tmp add index i1 (col1, id_max);
b) alter table test_tmp add unique key (col1, id_max);

The update results in 2 rows updated:

id	col1	comment
--	-------	---------------
 1	row 1	null
 2	row 2	null
 3	row 3	null
 4	null	Found	--> unexpected
 5	row 5	Found
 6	null	null
 7	row 5	null

This is 1 row more than the select!

update rewritten to update with join instead of exist clause works correct:
  update test aa, test_tmp bb
  set    aa.comment = 'Found'
  where  aa.col1  = bb.col1
  and    aa.id   != bb.id_max;

So bug seems only to be present when using an exist clause.

This seems to be a problem with NULLs. As you are using InnoDB engine for both tables, see what value do you have for the global variable:

innodb_stats_method

There are three methods, so check the startup value of this variable. It is probably nulls_equal.

Then re-start server first with second and then with third possible value, which are :

nulls_unequal
nulls_ignored

and then let us know if you get different results.

With the suggested settings the result is the same:
  set global innodb_stats_method = nulls_unequal;
  set global innodb_stats_method = nulls_ignored;

  set global innodb_stats_method = nulls_equal;

The documentation on http://dev.mysql.com/doc/refman/5.6/en/index-statistics.html
says this setting influences statistics. Influencing how a sql statement works is not happing / not part of the deal.

So i still have this problem.

This looks like a fixed bug to me. You should try the latest release of 5.6. I changed the script only marginally, with couple of selects extra and this is what I get:

Table   Create Table
test    CREATE TABLE `test` (\n  `id` int(11) NOT NULL,\n  `col1` varchar(12) DEFAULT NULL,\n  `comment` varchar(200) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `i1` (`col1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8
Table   Create Table
test_tmp        CREATE TABLE `test_tmp` (\n  `col1` varchar(12) CHARACTER SET utf8 DEFAULT NULL,\n  `id_max` int(11) NOT NULL,\n  KEY `i1` (`col1`,`id_max`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

                
id      col1    comment
5       row 5   NULL

                 
id      col1    comment
1       row 1   NULL
2       row 2   NULL
3       row 3   NULL
4       NULL    NULL
5       row 5   Found
6       NULL    NULL
7       row 5   NULL

Hence, it looks to me like an already fixed bug !!!!

Your test is slightly different, charset utf8 and latin1 are used.

Any way:
- in MySQL 5.1 there is no problem
- in MySQL 5.5.4 there is no problem
- in MariaDB 10.0.19-MariaDB-log which used MySQL 5.6 it goes wrong
  but only when charset utf8 is used every where (3x) or latin1 (3x)

It's getting complicated.

Question:
- does the ORIGINAL script works correctly on you side (which version are you using exactly)?

Hi,

I have changed my test script and I still get the same result.

We do not have any script named ORIGINAL.

MariaDB is not MySQL, so please try our latest 5.6. This bug seems to be fixed there.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".