[MDEV-8776] Update with subquery from the same table while derived_merge=on produces wrong result Created: 2015-09-09  Updated: 2017-04-24  Resolved: 2017-04-24

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update, Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 5.5.47, 10.0.23, 10.1.9

Type: Bug Priority: Major
Reporter: Ender Wiggin Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

One master / one slave configuration with parallel replication.

Attachments: File derived_tables_bug_test_case.sql    
Sprint: 10.1.23

 Description   

After switching to MariaDB from MySQL, we noticed an update having different result.
Possibly related to MDEV-6163, in which I got the idea to turn off derived_merge as a workaround.

Test case:

CREATE TABLE `tab` (
 
  `start` date DEFAULT NULL,
 
  `end` date DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
 
 
 
INSERT INTO `tab` (`start`, `end`) VALUES
 
('2014-08-13', NULL),
 
('2014-09-09', NULL);

Now execute the update:

set optimizer_switch='derived_merge=on';
 
UPDATE tab SET end=(SELECT x.start FROM (SELECT * FROM tab) AS x WHERE x.start > tab.start ORDER BY x.start ASC LIMIT 1);

Resulting table:

+------------+------+
 
| start      | end  |
 
+------------+------+
 
| 2014-08-13 | NULL |
 
| 2014-09-09 | NULL |
 
+------------+------+

However, with derived_merge=off:

set optimizer_switch='derived_merge=off';
 
UPDATE tab SET end=(SELECT x.start FROM (SELECT * FROM tab) AS x WHERE x.start > tab.start ORDER BY x.start ASC LIMIT 1);

Resulting table:

+------------+------------+
 
| start      | end        |
 
+------------+------------+
 
| 2014-08-13 | 2014-09-09 |
 
| 2014-09-09 | NULL       |
 
+------------+------------+

This is also how it was before migrating from MySQL.

 Comments   
Comment by Elena Stepanova [ 2015-09-10 ]

Thanks for the report and the test case.

Comment by Marc [ 2016-09-15 ]

Hi,
Has this bug been fixed? We have a similiar issue using MariaDB 5.5.44.

Regards,

Marc

Comment by Oleksandr Byelkin [ 2016-09-15 ]

When the but be fixed status will be changed on the "closed".

Comment by Marc [ 2016-09-15 ]

I have included the file derived_tables_bug_test_case.sql which shows the issue we have. The update statement does not use order by clause in our case.

Marc

Comment by Oleksandr Byelkin [ 2016-09-15 ]

Thank you a lot!

Comment by Marc [ 2016-09-15 ]

HI Oleksandr, thank you for your reply.

Our test case seems to work fine in 10.1.13. Can you confirm it has been fixed in 10.1.13?
regards,

Marc

Comment by Oleksandr Byelkin [ 2016-09-15 ]

I would be happy to say something, but the truth is that I have not looked on the problem close yet, so can say nothing.

It is quite possible that problem also was shown in other bug and have foxed already. Wait a bit I'll check last tree of 5.5 if it is repeatable there (also possible that 10.1 is the version where it is fixed, then when time comes we will find and backport the fix).

Comment by Oleksandr Byelkin [ 2016-09-15 ]

Original test suite is not repeatable any more.

What you expected from SQL you uploaded, for me following looks correctt:

select * from test_table;
id test_field
1 value
2 NULL
3 NULL

Comment by Marc [ 2016-09-15 ]

And you tested in which version(s)?

Comment by Oleksandr Byelkin [ 2016-09-15 ]

Current tree branch (no version yet) of 5.5. Will be in the next version.

Comment by Marc [ 2016-09-15 ]

Ok, so we can assume it has been fixed in 10.1.13 ?

Comment by Oleksandr Byelkin [ 2016-09-15 ]

I do not know. It will take too much time for me to check certain version now.

Comment by Oleksandr Byelkin [ 2017-04-19 ]

Now it do not depend on temporary table or not, but result is differ from SELECT:

create table t1
 
(
 
    id              bigint not null auto_increment,
 
    test_field      varchar(20),
 
    primary key(id)
 
);
 
 
 
insert into t1
 
(
 
    id,
 
    test_field
 
)
 
values
 
(1, null),
 
(2, null),
 
(3, null);
 
 
 
select *, (select 'value' from (select * from t1) as tt_derived
 
where tt_derived.id = tt.id + 1) as exp from t1 tt;
 
 
 
 
 
update t1 tt
 
set 
    tt.test_field = 
        (
 
            select 'value'
 
            from (select * from t1) as tt_derived
 
            where tt_derived.id = tt.id + 1
 
        )
 
where tt.id = 1;
 
select * from t1;
 
drop table t1;


worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
create table t1
 
(
 
id              bigint not null auto_increment,
 
test_field      varchar(20),
 
primary key(id)
 
);
 
insert into t1
 
(
 
id,
 
test_field
 
)
 
values
 
(1, null),
 
(2, null),
 
(3, null);
 
select *, (select 'value' from (select * from t1) as tt_derived
 
where tt_derived.id = tt.id + 1) as exp from t1 tt;
 
id	test_field	exp
 
1	NULL	value
 
2	NULL	value
 
3	NULL	NULL
 
update t1 tt
 
set 
tt.test_field = 
(
 
select 'value'
 
            from (select * from t1) as tt_derived
 
where tt_derived.id = tt.id + 1
 
)
 
where tt.id = 1;
 
select * from t1;
 
id	test_field
 
1	value
 
2	NULL
 
3	NULL
 
drop table t1;
 
main.test                                [ pass ]      4

Comment by Oleksandr Byelkin [ 2017-04-24 ]

Above is wrong (sorry).

And there is no difference in merge/materialize derived tables as deep as allow me my compmiler: mariadb-5.5.49 (older version can not be compiled with modern gcc).

Comment by Elena Stepanova [ 2017-04-24 ]

The problem disappeared from 5.5 tree after this commit:

commit c88ca2c22739dc2327c7b1082df79a93370662ba
 
Author: Oleksandr Byelkin <sanja@mariadb.com>
 
Date:   Fri Nov 6 17:56:56 2015 +0100
 
 
 
    MDEV-8701 Crash on derived query
 
    MDEV-8938 Server Crash on Update with joins
 
    
    Make unique table check after setup_fields of update because unique table can materialize table and we do not need field resolving after materialization.

It was released with 5.5.47, 10.0.23 and 10.1.9.

Generated at Thu Feb 08 07:29:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.