Description:
A have a few queries which broke with the recent upgrade to MySQL. The problem occurs specifically with not null columns which are left joined and selected along with a window function AND an order by clause below. Please see below on how to replicate:
How to repeat:
-- create table with not null columns, and nullable columns just for testing
create table test (
id1 int unsigned not null primary key,
id2 int unsigned not null,
id3 int unsigned,
name varchar(30),
name2 varchar(30) not null
);
-- insert data
insert into test(id1, id2, id3, name, name2) values(1, 2, 3, 'test1', 'test1');
insert into test(id1, id2, id3, name, name2) values(4, 5, 6, 'test2', 'test2');
-- query with window function and order by
select count(1) over(),
q.*
from (select 1) d
left join (
select * from test
) q on 1=1
order by name;
-- above query returns
|2|null|null|3|test1|null|
|2|null|null|6|test2|null|
-- returned when removing the order by clause
|2|1|2|3|test1|test1|
|2|4|5|6|test2|test2|
Suggested fix:
Return MySQL to previous functionality, where window functions in conjunction with order by clauses do not cause columns to become null.
Thank you for your assistance,
Joe
Description: A have a few queries which broke with the recent upgrade to MySQL. The problem occurs specifically with not null columns which are left joined and selected along with a window function AND an order by clause below. Please see below on how to replicate: How to repeat: -- create table with not null columns, and nullable columns just for testing create table test ( id1 int unsigned not null primary key, id2 int unsigned not null, id3 int unsigned, name varchar(30), name2 varchar(30) not null ); -- insert data insert into test(id1, id2, id3, name, name2) values(1, 2, 3, 'test1', 'test1'); insert into test(id1, id2, id3, name, name2) values(4, 5, 6, 'test2', 'test2'); -- query with window function and order by select count(1) over(), q.* from (select 1) d left join ( select * from test ) q on 1=1 order by name; -- above query returns |2|null|null|3|test1|null| |2|null|null|6|test2|null| -- returned when removing the order by clause |2|1|2|3|test1|test1| |2|4|5|6|test2|test2| Suggested fix: Return MySQL to previous functionality, where window functions in conjunction with order by clauses do not cause columns to become null. Thank you for your assistance, Joe