MySQL Bugs: #101628: ORDER BY sub-select column sets all values to NULL

Bug #101628	ORDER BY sub-select column sets all values to NULL
Submitted:	16 Nov 2020 13:06	Modified:	1 Dec 2020 12:39
Reporter:	Sander Bouwhuis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Windows (Win10 x64)
Assigned to:		CPU Architecture:	x86

Description:
When doing an ORDER BY, the values of a sub-select are set to NULL. I.e., WRONG data is returned!

I tried with my the following applications (all the same result):
1. My own application using unicode ODBC v8.0.22
2. MySql Workbench
3. HeidiSQL

I really hope I'm making a foolish mistake, but the query works correctly on my MS-SQL Server. Unfortunately, my customer wants to use MySql, so I'm stuck with this problem until they can upgrade to MS-SQL Server.

How to repeat:
This query INCORRECTLY returns only NULL values for the C.`Selected` column:

SELECT
  P.`Description`, P.`Product_id`,
  C.`Selected`
FROM
  Products AS P
LEFT OUTER JOIN
(
  SELECT
    `Parent_id`, 1 AS `Selected`
  FROM
    Child_products
  WHERE
    `Child_id` = 15000018
) AS C
ON
  C.`Parent_id` = P.`Product_id`
ORDER BY
  C.`Selected`, P.`Description`

This query CORRECTLY returns NULL values and 1 values for the C.`Selected` column:

SELECT
  P.`Description`, P.`Product_id`,
  C.`Selected`
FROM
  Products AS P
LEFT OUTER JOIN
(
  SELECT
    `Parent_id`, 1 AS `Selected`
  FROM
    Child_products
  WHERE
    `Child_id` = 15000018
) AS C
ON
  C.`Parent_id` = P.`Product_id`
ORDER BY
  P.`Description`

I also tried '1' AS `Selected` to make it a CHAR type instead of an INTEGER type, but the same bug happens.
For now, I removed the ORDER BY clause when I detect a customer is using MySql instead of MS-SQL Server.

Suggested fix:
I seriously hope I'm making a stupid mistake and the 'fix' is that I need to adjust the query.

Hi Mr. Bouwhuis,

Thank you for your bug report.

However, this report is not a bug. According to SQL standard, attributes from the derived tables, which are also called tables from the FROM clause, are not visible to the outer SELECT node.

Our Reference Manual describes other ways to accomplish what you are looking for. Look especially in the section on what is new in 8.0.

Not a bug.

If this is not a bug, at least throw an ODBC error or warning! Now INCORRECT data is returned which should never happen when doing a simple ORDER BY clause.

Hi Mr. Bouwhuis,

If you wish to see changes in our Connector/ODBC, then create a bug with that category and not with a 'Server' category.

This bug is closed.

Hi Sander,

I agree with you that it looks strange that ORDER BY is changing the values of the result, not only the ordering. However, there's not enough information in the bug report to reproduce what you're seeing. Could you please provide a complete test case with CREATE TABLE and INSERT statements?

How many rows do you get in the result?
MySQL Workbench and probably HeidiSQL limits the result to the first 1000 rows. After ordering, maybe all the first 1000 rows have NULL values. This could be the reason you do not see 1. Left join returns NULL even if you hard code the selected column to 1 when there is no matching rows.

This is a genuine bug. It is simple to reproduce:

create table Products (
  `Description` varchar(100),
  `Product_id` integer
);
insert into Products values ('abc', 123);
create table Child_products (
  `Parent_id` integer,
  `Child_id` integer
);
insert into Child_products values (123, 15000018);

and then the query in question.

Visibility does not come into it; fields in derived tables are visible to the outer SELECT, just like regular tables. The bug is most likely related to that ORDER BY chooses to stream the rows via a temporary table, and I'd assume something goes wrong when materializing the combination of a constant field and NULL extension. As a workaround, you can replace the 1 with something constant that doesn't look constant to the optimizer, e.g.

`Child_id` > -1 AS `Selected`

Minimal test case:

CREATE TABLE t1 ( a INTEGER );
INSERT INTO t1 VALUES (123);
SELECT * FROM t1 LEFT JOIN ( SELECT 1 FROM t1 ) d1 ON TRUE ORDER BY a;
DROP TABLE t1;

Remove the ORDER BY a, and it works. Also broken on 5.7, but works on 5.6.

Documented fix as follows in the MySQL 8.0.23 changelog:

    When creating fields for materializing temporary tables (that
    is, when needing to sort a join), the optimizer checks whether
    the item needs to be copied or is only a constant. This was not
    done correctly in one specific case; when performing an outer
    join against a view or derived table containing a constant, the
    item was not properly materialized into the table, which could
    yield spurious occurrences of NULL in the result.

Closed.

Thank you, Jon .....