[MDEV-27473] Inconsistent results upon subquery with aggregate function and implicit cast Created: 2022-01-12  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description    

CREATE TABLE t1 (a int, b time, KEY(b), KEY(a)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (0,'00:00:00'), (4,'00:00:00'), (8,'00:00:08');
 
 
 
CREATE TABLE t2 (c int);
 
 
 
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 );
 
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ) ORDER BY b DESC;
 
ALTER TABLE t1 DROP INDEX a;
 
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 );
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

The first query, without ORDER BY, returns one row:

10.2 114476f2

 
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 );
 
b
 
00:00:08

The second query, with ORDER BY DESC, returns two rows:

SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ) ORDER BY b DESC;
 
b
 
00:00:00
 
00:00:00

And the third query, which is the same as the first one but after an index was dropped, returns 3 rows:

ALTER TABLE t1 DROP INDEX a;
 
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 );
 
b
 
00:00:00
 
00:00:00
 
00:00:08

Because of an aggregate function upon an empty result set in subquery and a datatype difference (time vs int), I wouldn't dare claim which of the results is correct. But they can't all be correct at once.
Generated at Thu Feb 08 09:53:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.