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

          No workflow transitions have been executed yet.

          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.