[MDEV-32073] SELECT behavior has changed between 10.11.4 and 10.11.5 Created: 2023-09-01  Updated: 2023-09-02  Resolved: 2023-09-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.22
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Renaud Grand Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-6768 Wrong result with aggregate with join... Closed

 Description   

The statement

SELECT p0_.id AS sclr_0, SUM(p1_.quantity) AS sclr_1, p0_.minimum_guaranteed_quantity AS sclr_2 FROM order_product o2_
 
LEFT JOIN placed_order p3_ ON o2_.placed_order_id = p3_.id
 
LEFT JOIN product_edit p1_ ON o2_.placed_product_edit_id = p1_.id
 
LEFT JOIN product p0_ ON o2_.product_id = p0_.id WHERE p3_.preparation_date = '2023-09-02 00:00:00' AND p0_.id = 158

Results in one row `NULL, NULL, NULL`, in 10.5.22 or 10.11.5
Results in one row `158, NULL, 1` in 10.5.12, 10.5.21, 10.11.4

After trying various Docker images for MariaDB I can confirm with certainty that this behavior has been introduced between 10.5.21 and 10.5.22. Also tested last version 10.11.5 and 10.11.4 and same behavior has been introduced between those two versions.

Interestingly, same `NULL, NULL, NULL` behavior happens in 10.5.0. So this might be a regression.

Feels like `158, NULL, 1` is the non buggy behavior: it's strange that `SELECT p0_.id AS sclr_0` returns `NULL` given the fact the data is given in the WHERE clause.

Engine is InnoDB. Version on server indicates 10.5.22-MariaDB-1:10.5.22+maria~deb10-log so Debian, but I could reproduce the issue easily on both MacOS (last public OS on M1 Pro) and Ubuntu 22.04 (On an AMD 5950x)

Note: if the `SUM()` is removed such as:

SELECT p0_.id AS sclr_0, p0_.minimum_guaranteed_quantity AS sclr_2 FROM order_product o2_
 
LEFT JOIN placed_order p3_ ON o2_.placed_order_id = p3_.id
 
LEFT JOIN product p0_ ON o2_.product_id = p0_.id WHERE p3_.preparation_date = '2023-09-02 00:00:00' AND p0_.id = 158

No data is returned on 10.5.22, 10.5.21, 10.5.12, 10.11.4, 10.11.5

So the problem arises when results are aggregated I guess?

 Comments   
Comment by Sergei Golubchik [ 2023-09-01 ]

The new result is correct. There are no rows after the WHERE clause, so the result of the non-aggregated query is an empty set, no rows.
Now the SQL standard (2016, Part 2, Foundation, section 4.16.4 Aggregate functions) says

If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the null value.

so the new result is correct.

The old incorrect result was due to a bug MDEV-6768.

Comment by Renaud Grand [ 2023-09-01 ]

Hi! The result of the non-aggregated query has seemingly always been an empty set.

The problem arises when the query is aggregated, where we have either:

  • one row `NULL, NULL, NULL`, in 10.5.22 or 10.11.5
  • one row `158, NULL, 1` in 10.5.12, 10.5.21, 10.11.4

Which I feel you responded that the new behavior is correct by describing the standard but I want to make sure I've understood well?

Comment by Sergei Golubchik [ 2023-09-01 ]

Yes. Empty set was also before the fix. The bug was that in the presence of aggregate functions some columns were showing values that were not part of the result set (which was empty). This was fixed as MDEV-6768.

Comment by Renaud Grand [ 2023-09-02 ]

Instead of returning a row with NULL, NULL, NULL, wouldn't have it made more sense to return no data?

Comment by Sergei Golubchik [ 2023-09-02 ]

SQL Standard (quoted above) says that SUM() of the empty set is NULL.

Generated at Thu Feb 08 10:28:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.