[MDEV-28175] wrong select result because of derived_merge=on (default setting) Created: 2022-03-25  Updated: 2022-08-24  Resolved: 2022-08-24

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.5.15, 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: peterdd Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: subquery
Environment:

Linux servers, Debian Testing VM, Opensuse 15.3 VM, probably irrelevant


 Description   

Using a select from (select) gives wrong results when there is a calculated variable involved.

create table user(id int auto_increment, lname varchar(10), fname varchar(10), sex varchar(1) default NULL, death int unsigned default NULL, key id(id));
 
 
 
create table period_member(period_id int, user_id int references user(id));
 
 
 
-- for example write result to a table
 
create table team_member(team_id int, version int, user_id int references user(id) ON DELETE NO ACTION, pos int not NULL, unique key (team_id,version,user_id), unique key(team_id, version, pos));
 
 
 
-- some demo data
 
insert into user(lname, fname, sex) values('BBBBB','Daniel','m'),('CCC','Annika','w'),('CC','Tom','m'),('AAAA','Edward','m'),('AAA','Bernd','m');
 
insert into user(lname,fname,sex) values('LLL','Franziska','w'),('C','Emilia','w'),('A','Arne','m'),('BBBB','Chris','m'),('AAA','Arne','m');
 
insert into user(lname,fname,sex) values('LLL','Daniel','m'),('CC','Emilia','w'),('AA','Arne','m'),('B','Chris','m'),('AAA','Bruno','m');
 
insert into user(lname,fname,sex) values('LL','Danilo','m'),('C','Emil','m'),('AAA','Beate','m'),('BBB','Petra','w'),('A','Claire','w');
 
 
 
insert into period_member values(1,2),(1,4),(1,5),(1,6),(1,7),(1,8);
 
insert into period_member values(2,4),(2,5),(2,7),(2,8),(2,1),(2,3),(2,9),(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16),(2,17),(2,18);
 
 
 
 
 
-- mariadb wrong, mysql ok
 
set @ps:=0;
 
select 2,id,lname,fname,(@pos:=@pos+1)
 
from (
 
        select id,lname,fname from user
 
        join period_member on user.id=period_member.user_id
 
        where period_member.period_id=2
 
        and sex='m'
 
        order by lname,fname
 
) sub
 
order by fname,lname;
 
 
 
-- mariadb wrong, mysql ok
 
select 2,id,lname,fname,(@pos:=@pos+1)
 
from (
 
        select id,lname,fname from user
 
        join period_member on user.id=period_member.user_id
 
        where period_member.period_id=2
 
        and sex='m'
 
        order by lname,fname
 
) sub,
 
(select @pos:=0) r
 
order by fname,lname;

Adding a GROUP BY workarounds the problem, but that appears like an ugly hack to me, dunno.

-- using 'group by' workaround
 
-- mariadb ok, mysql ok
 
select 2,id,lname,fname,(@pos:=@pos+1)
 
from (
 
        select id,lname,fname from user
 
        join period_member on user.id=period_member.user_id
 
        where period_member.period_id=2
 
        and sex='m'
 
        group by id
 
        order by lname,fname
 
) sub,
 
(select @pos:=0) r
 
order by fname, lname;

Explicitly turning off derived_merge optimizer_switch hack:

-- using 'derived_merge=off' hack
 
-- mariadb ok, mysql ok
 
set optimizer_switch='derived_merge=off';
 
select 2,id,lname,fname,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub, (select @pos:=0) r order by lname, fname;

I stumbled over it when trying to generate prefilled list versions using insert-select constructs and it worked in mysql (5.1 no problem and mysql8.0 too no problem) but not mariadb (10.5.15, 10.6.8 and older versions) 

set @pos:=0; set optimizer_switch='derived_merge=on';  insert into team_member select 1,1,id,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub order by lname, fname;
 
set @pos:=0; set optimizer_switch='derived_merge=off'; insert into team_member select 1,2,id,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub order by lname, fname;
 
 
 
select * from team_member left join user on user.id=user_id where version=1 order by pos;
 
select * from team_member left join user on user.id=user_id where version=2 order by pos;

Using 

SHOW WARNINGS;

and 

EXPLAIN EXTENDED

shows the problem:

set @pos:=0; set optimizer_switch='derived_merge=on'; show warnings; explain extended select 2,id,lname,fname,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub order by lname, fname;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                    |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select 2 AS `2`,`test`.`user`.`id` AS `id`,`test`.`user`.`lname` AS `lname`,`test`.`user`.`fname` AS `fname`,@pos:=@`pos` + 1 AS `(@pos:=@pos+1)` from `test`.`user` join `test`.`period_member` where `test`.`period_member`.`period_id` = 2 and `test`.`period_member`.`user_id` = `test`.`user`.`id` and `test`.`user`.`sex` = 'm' order by `test`.`user`.`lname`,`test`.`user`.`fname` |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
+------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+----------------------------------------------+
 
| id   | select_type | table         | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra                                        |
 
+------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+----------------------------------------------+
 
|    1 | SIMPLE      | user          | ALL  | id            | NULL    | NULL    | NULL         | 20   |   100.00 | Using where; Using temporary; Using filesort |
 
|    1 | SIMPLE      | period_member | ref  | user_id       | user_id | 5       | test.user.id | 1    |   100.00 | Using where                                  |
 
+------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+----------------------------------------------+
 
2 rows in set, 1 warning (0.001 sec)
 
}


set @pos:=0; set optimizer_switch='derived_merge=off'; show warnings; explain extended select 2,id,lname,fname,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and
 
sex='m' order by lname,fname) sub order by lname, fname;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                    |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select 2 AS `2`,`test`.`user`.`id` AS `id`,`test`.`user`.`lname` AS `lname`,`test`.`user`.`fname` AS `fname`,@pos:=@`pos` + 1 AS `(@pos:=@pos+1)` from `test`.`user` join `test`.`period_member` where `test`.`period_member`.`period_id` = 2 and `test`.`period_member`.`user_id` = `test`.`user`.`id` and `test`.`user`.`sex` = 'm' order by `test`.`user`.`lname`,`test`.`user`.`fname` |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
+------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+---------------------------------+
 
| id   | select_type | table         | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra                           |
 
+------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+---------------------------------+
 
|    1 | PRIMARY     | <derived2>    | ALL  | NULL          | NULL    | NULL    | NULL         | 20   |   100.00 | Using temporary; Using filesort |
 
|    2 | DERIVED     | user          | ALL  | id            | NULL    | NULL    | NULL         | 20   |   100.00 | Using where; Using filesort     |
 
|    2 | DERIVED     | period_member | ref  | user_id       | user_id | 5       | test.user.id | 1    |   100.00 | Using where                     |
 
+------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+---------------------------------+
 
3 rows in set, 1 warning (0.002 sec)



 Comments   
Comment by Elena Stepanova [ 2022-07-26 ]

I don't see any difference at all between 10.5 and MySQL 8.0.28, except that MySQL also prints warnings which MariaDB should probably start doing too:

Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release

Otherwise, it is the same

select 2,id,lname,fname,(@pos:=@pos+1)
 
from (
 
select id,lname,fname from user
 
join period_member on user.id=period_member.user_id
 
where period_member.period_id=2
 
and sex='m'
 
        order by lname,fname
 
) sub
 
order by fname,lname;
 
2	id	lname	fname	(@pos:=@pos+1)
 
2	8	A	Arne	NULL
 
2	13	AA	Arne	NULL
 
2	10	AAA	Arne	NULL
 
2	18	AAA	Beate	NULL
 
2	5	AAA	Bernd	NULL
 
2	15	AAA	Bruno	NULL
 
2	14	B	Chris	NULL
 
2	9	BBBB	Chris	NULL
 
2	1	BBBBB	Daniel	NULL
 
2	11	LLL	Daniel	NULL
 
2	16	LL	Danilo	NULL
 
2	4	AAAA	Edward	NULL
 
2	17	C	Emil	NULL
 
2	3	CC	Tom	NULL
 
Warnings:
 
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
 
select 2,id,lname,fname,(@pos:=@pos+1)
 
from (
 
select id,lname,fname from user
 
join period_member on user.id=period_member.user_id
 
where period_member.period_id=2
 
and sex='m'
 
        order by lname,fname
 
) sub,
 
(select @pos:=0) r
 
order by fname,lname;
 
2	id	lname	fname	(@pos:=@pos+1)
 
2	8	A	Arne	1
 
2	13	AA	Arne	2
 
2	10	AAA	Arne	3
 
2	18	AAA	Beate	4
 
2	5	AAA	Bernd	5
 
2	15	AAA	Bruno	6
 
2	14	B	Chris	7
 
2	9	BBBB	Chris	8
 
2	1	BBBBB	Daniel	9
 
2	11	LLL	Daniel	10
 
2	16	LL	Danilo	11
 
2	4	AAAA	Edward	12
 
2	17	C	Emil	13
 
2	3	CC	Tom	14
 
Warnings:
 
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
 
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

So, please specify what you are getting as an actual result and what, in your opinion, is the expected result.
While doing so please note that the warning above is not accidental. The result of assigning and reading a variable within the same statement is non-deterministic, both in MariaDB and in MySQL. See MySQL manual for more details (more suitable than MariaDB documentation, as you refer to MySQL as correctly behaving baseline)

Generated at Thu Feb 08 09:58:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.