[MDEV-31962] Latest 10.11.5 major bug with INNER JOIN Created: 2023-08-20  Updated: 2023-09-01  Resolved: 2023-09-01

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

Type: Bug Priority: Major
Reporter: Eric X Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 1
Labels: regression
Environment:

Distributor ID: Debian
Description: Debian GNU/Linux 11 (bullseye)
Release: 11
Codename: bullseye

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

 Description   

I just tried to update from 10.11.4 to 10.11.5 today. Only hours later did we notice this major, peculiar bug:

CREATE TABLE at (  
  account_id bigint(20) unsigned NOT NULL auto_increment,  
  max_legs smallint NOT NULL default -1,  
  PRIMARY KEY account_id (account_id)
 
)  ENGINE=innodb DEFAULT CHARSET=utf8mb4;
 
 
 
CREATE TABLE conf (  
  conf_id bigint(20) unsigned NOT NULL auto_increment,
 
  master_id bigint(20) unsigned NOT NULL DEFAULT 0,
 
  legs_current smallint unsigned NOT NULL DEFAULT 0,
 
  PRIMARY KEY  (conf_id),
 
  KEY master_id  (master_id)
 
) ENGINE=Innodb DEFAULT CHARSET=utf8mb4;
 
 
 
INSERT INTO at VALUES(NULL, -1);
 
 
 
10.11.4:
 
SELECT at.max_legs, SUM(conf.legs_current) AS legs 
FROM conf
 
INNER JOIN at ON conf.master_id = at.account_id 
 
WHERE conf.master_id=1;
 
+----------+------+
 
| max_legs | legs |
 
+----------+------+
 
|       -1 | NULL |
 
+----------+------+
 
1 row in set (0.000 sec)
 
        
10.11.5
 
SELECT at.max_legs, SUM(conf.legs_current) AS legs 
FROM conf
 
INNER JOIN at ON conf.master_id = at.account_id  
WHERE conf.master_id=1;
 
+----------+------+
 
| max_legs | legs |
 
+----------+------+
 
|     NULL | NULL |
 
+----------+------+
 
1 row in set (0.001 sec)

The important thing to understand is that the conf table has no entries in it (thus the NULL legs count). It still used to always return the max_legs value from the at table. Now it seems like the INNER JOIN completely fails and returns nothing. This is a breaking change one would never expect in a minor version update. Therefore it is a bug.

 Comments   
Comment by Alice Sherepa [ 2023-08-28 ]

Could you please add SHOW CREATE TABLE for both tables?

Comment by Eric X [ 2023-08-28 ]

I added a simplified version of the two tables, included an INSERT for the at table and changed the master_id to 1 to match the INSERTed row.

Comment by Eric X [ 2023-08-28 ]

Incidentally, I think both results I get from these mariadb version are wrong. The INNER JOIN should fail with no matches in the conf table, so there should be NO returned rows whatsoever. I am fairly sure when I first wrote this logic many years ago, that was likely the outcome, because I have app logic to handle the zero rows casae. At some point it started to return a single row, but since the max_legs values was present, my code didn't break, so I didn't notice the issue. But now it returns a single row with both values NULL which caused a serious, service impacting regression.

Comment by Sergei Golubchik [ 2023-09-01 ]

The new result is correct. There are no rows in the second table, so the result of the INNER JOIN 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 Eric X [ 2023-09-01 ]

Surely an INNER JOIN that fails to make the connection should result in NO ROWS. Not a single row of nulls. That implies there was a found connection. Whether or not this is some SQL standard, it's logically inconsistent. This USED to return no result at all which at least makes sense.

So I just looked at this cited bug:
Created:    2014-09-23 10:00
Resolved:    2023-05-03 00:50

So a 9 year old bug was just fixed, which caused a regression in logic that worked for 9 years. At a certain point, a "bug" is so old, "fixing" it is breaking EXPECTED behavior. This should have been held back for a major version release.

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