Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19676

HAVING with constant expression on empty table and without GROUP BY loses constant result.

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.3.15, 10.4.5, 5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.5, 10.6
    • Optimizer
    • 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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Alicen - created issue -
          Alicen - made changes -
          Field Original Value New Value
          Description Given the following, minimal sample:
          {code:sql}
          CREATE SCHEMA A ;

          CREATE TABLE A.A ( A BOOLEAN DEFAULT TRUE);

          SELECT 1 AS A FROM A.A AS AA HAVING TRUE;
          {code}

          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.
          		Given the following, minimal sample:
          {code:sql}
          CREATE SCHEMA A ;

          CREATE TABLE A.A ( A BOOLEAN DEFAULT TRUE);

          SELECT 1 AS A FROM A.A AS AA HAVING TRUE;
          {code}

          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.
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.4 [ 22408 ]
          Affects Version/s 5.5 [ 15800 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.2 [ 14601 ]
          Affects Version/s 10.3 [ 22126 ]
          Affects Version/s 10.4 [ 22408 ]
          Assignee Igor Babaev [ igor ]
          Priority Major [ 3 ] Minor [ 4 ]
          igor Igor Babaev added a comment -

          What about

          SELECT  A FROM A.A AS AA HAVING TRUE

          ?

          igor Igor Babaev added a comment - What about SELECT A FROM A.A AS AA HAVING TRUE ?
          Alicen - added a comment - 

          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.

          Alicen - added a comment - 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.
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 97285 ] MariaDB v4 [ 141322 ]
          alice Alice Sherepa made changes -
          Affects Version/s 10.5 [ 23123 ]
          Affects Version/s 10.6 [ 24028 ]
          Affects Version/s 10.7 [ 24805 ]
          Affects Version/s 10.8 [ 26121 ]
          Affects Version/s 10.9 [ 26905 ]
          Affects Version/s 10.10 [ 27530 ]
          alice Alice Sherepa made changes -
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.7 [ 24805 ]
          Fix Version/s 10.8 [ 26121 ]
          Fix Version/s 10.9 [ 26905 ]
          alice Alice Sherepa made changes -
          Labels optimizer 11.0-sel optimizer
          monty Michael Widenius made changes -
          Assignee Igor Babaev [ igor ] Michael Widenius [ monty ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.7 [ 24805 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.3 [ 22126 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.8 [ 26121 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.9 [ 26905 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.4 [ 22408 ]

          People

            monty Michael Widenius
            Alicen -
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.