[MDEV-19676] HAVING with constant expression on empty table and without GROUP BY loses constant result. Created: 2019-06-03  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.1, 10.3.15, 10.4.5, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: - Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: 11.0-sel, optimizer
Environment:

MariaDB Docker (Bionic) on Ubuntu Linux 19.4


 Description   

Given the following, minimal sample:

CREATE SCHEMA A ;
 
 
 
CREATE  TABLE A.A  ( A BOOLEAN DEFAULT TRUE);
 
 
 
SELECT   1 AS A FROM A.A AS AA HAVING TRUE;

Summary: No result is returned if a constant without aggregate function is selected and a HAVING clause (with a constant expression like 'TRUE'), without an explicit GROUP BY clause is used on an empty table.

I expect that a single 1 is returned. This is also the behaviour of Postgres and described here (https://stackoverflow.com/a/53238082). The HAVING groups the empty result into a single, but existing group. HAVING doesn't filter this single row, so a single row and result should be returned.

When a explicit GROUP BY is used, or COUNT ( * ) is used additionally, or there exists an entry in the table, a 1 is returned, which is correct.

 Comments   
Comment by Elena Stepanova [ 2019-06-03 ]

Indeed, PostgreSQL returns a result:

postgres=# CREATE SCHEMA A ;
 
CREATE SCHEMA
 
postgres=#  
postgres=# CREATE  TABLE A.A  ( A BOOLEAN DEFAULT TRUE);
 
CREATE TABLE
 
postgres=#  
postgres=# SELECT   1 AS A FROM A.A AS AA HAVING TRUE;
 
 a 
---
 
 1
 
(1 row)

All versions of MariaDB and MySQL return an empty set.
I don't know which is correct.

Comment by Igor Babaev [ 2019-06-04 ]

What about

SELECT  A FROM A.A AS AA HAVING TRUE

?

Comment by - [ 2019-06-04 ] 

SELECT  A FROM A.A AS AA HAVING TRUE

@Igor In Postgres, this is an error:

postgres=# CREATE SCHEMA A ;
 
CREATE SCHEMA
 
 
 
postgres=# CREATE  TABLE A.A  ( A BOOLEAN DEFAULT TRUE);
 
CREATE TABLE
 
 
 
postgres=#  SELECT A FROM A.A AS AA HAVING TRUE;
 
ERROR: column "aa.a" must appear in the GROUP BY clause or be used in an aggregate function
 
LINE 1: SELECT A FROM A.A AS AA HAVING TRUE;

In MariaDB 10.4.5, this also returns an empty result set, like the single 1.

Interesting: If I insert some values before, I get these values back:

CREATE SCHEMA A ;
 
 
 
CREATE  TABLE A.A  ( A BOOLEAN DEFAULT TRUE);
 
 
 
INSERT INTO A.A VALUES (TRUE, FALSE, TRUE);
 
 
 
SELECT A.A AS A FROM A.A AS AA HAVING TRUE;
 
-- Returns [1, 0, 1]
 
 
 
SELECT 1 AS A FROM A.A AS AA HAVING TRUE;
 
-- Returns [1, 1, 1]

It seems like HAVING without a GROUP BY is completely 'ignored'. As far as I understand SQL, there should be a implicit grouping when a having occurs.

Generated at Thu Feb 08 08:53:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.