MySQL Bugs: #103150: scalar expression returns empty result inside a CTE

Bug #103150	scalar expression returns empty result inside a CTE
Submitted:	30 Mar 2021 9:11	Modified:	31 Mar 2021 8:42
Reporter:	Yushan ZHANG	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cte, scalar

Description:
Finding the second highest salary of the employee, if not exists then return null:

Table:

id | salary |
0  | 1000   |
0  | 2000   |

-- correct
mysql> with mid as (
    ->     select salary
    ->     from employee
    ->     order by salary desc
    ->     limit 1,1
    -> ) 
    -> 
    -> select
    ->     case 
    ->     when (select count(*)=0 from mid)=1 then (select null)
    ->      else (select mid.salary)
    ->     end as SecondHighestSalary
    -> from mid;
+---------------------+
| SecondHighestSalary |
+---------------------+
|                1000 |
+---------------------+
1 row in set (0.00 sec)

-- correct
mysql> select count(*)=0
    -> from (
    ->   select salary
    ->     from employee
    ->     order by salary desc
    ->     limit 1,1) as t;
+------------+
| count(*)=0 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

Table:

id | salary |
0  | 1000   |

-- wrong?? it cannot be empty set because there are only two cases
mysql> with mid as (
    ->     select salary
    ->     from employee
    ->     order by salary desc
    ->     limit 1,1
    -> ) 
    -> 
    -> select
    ->     case 
    ->     when (select count(*)=0 from mid)=1 then (select null)
    ->      else (select mid.salary)
    ->     end as SecondHighestSalary
    -> from mid;
Empty set (0.00 sec)

-- correct
mysql> select count(*)=0
    -> from (
    ->   select salary
    ->     from employee
    ->     order by salary desc
    ->     limit 1,1) as t;
+------------+
| count(*)=0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

-- wrong?? empty set???
mysql> with mid as (
    ->     select salary
    ->     from employee
    ->     order by salary desc
    ->     limit 1,1
    -> ) 
    -> 
    -> select
    -> (select count(*)=0 from mid)=1
    -> from mid;
Empty set (0.00 sec)

-- correct
mysql> with mid as (
    ->     select salary
    ->     from employee
    ->     order by salary desc
    ->     limit 1,1
    -> ) 
    -> 
    -> select count(*)=0
    -> from mid;
+------------+
| count(*)=0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

How to repeat:
-- all correct
drop table employee;
create table employee (id int primary key, salary int);
insert into employee values (0, 1000);
insert into employee values (1, 2000);

-- wrong
drop table employee;
create table employee (id int primary key, salary int);
insert into employee values (0, 1000);

-- wrong
with mid as (
    select salary
    from employee
  	  order by salary desc
    limit 1,1
) 

select
    case 
    when (select count(*)=0 from mid)=1 then (select null)
     else (select mid.salary)
    end as SecondHighestSalary
from mid;

-- wrong
with mid as (
    select salary
    from employee
    order by salary desc
    limit 1,1
) 

select
	(select count(*)=0 from mid)=1
from mid;

-- correct
with mid as (
    select salary
    from employee
  	  order by salary desc
    limit 1,1
) 

select count(*)=0
from mid;

Hello Yushan ZHANG,

Thank you for the report and test case.

regards,
Umesh

Hi,

A CTE is a _table_ expression. It returns zero or more rows; in your case, no rows exists, so it returns nothing. Thus, there is no bug here:

mysql> select salary     from employee     order by salary desc     limit 1,1;
Empty set (0,00 sec)

What you want is something like:

mysql> select (select salary from employee order by salary desc limit 1 offset 1) as second_highest_salary;
+-----------------------+
| second_highest_salary |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set (0,00 sec)

mysql> insert into employee values (1, 2000);
Query OK, 1 row affected (0,00 sec)

mysql> select (select salary from employee order by salary desc limit 1 offset 1) as second_highest_salary;
+-----------------------+
| second_highest_salary |
+-----------------------+
|                  1000 |
+-----------------------+
1 row in set (0,00 sec)

You can also solve this in many other ways, such as a left join against a derived table with window functions.

Thanks for the explanation.
But I don't understand why CTE's behavior is different in the following queries:
If they should return an empty result for my test table, then both of the following should be empty. However, the first one returns 0, and the second returns 1.

-- wrong
with mid as (
    select salary
    from employee
    order by salary desc
    limit 1,1
) 
select
 (select count(*)=0 from mid)=1
from mid;

-- correct
with mid as (
    select salary
    from employee
  	  order by salary desc
    limit 1,1
) 
select count(*)=0
from mid;

Sorry for atypo, the first has an empty result, and the second returns 1.

The second query has an implicit aggregation due to the COUNT(*) in the outer query. SELECT COUNT(*) FROM t1 will yield exactly one row, even if t1 has no rows.