Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.5.15, 10.6.7
-
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) |