SQLite

Check-in [9322a7c2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow a HAVING clause on any aggregate query, even if there is no GROUP BY clause. This brings SQLite into closer agreement with PostgreSQL and fixes the concern raised by forum post 1a7fea4651.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9322a7c21f1c22ba00e9b889223e89bc1591db6e561ce05091e905e98c1bf2b3
User & Date: drh 2022-06-21 13:41:24
References
2023-03-31
23:48
Omit the the count-of-view optimization if there is a HAVING clause. This fixes a problem that originated with check-in [9322a7c21f1c22ba]. dbsqlfuzz 6a107e3055bd22afab31cfddabc2d9d54fcbaf69 (check-in: babe2b5e user: drh tags: trunk)
Context
2022-06-21
18:38
Disable the short-cut query planner if the NOT INDEXED modifier is used. Forum post 454d706296. (check-in: bd87d107 user: drh tags: trunk)
13:41
Allow a HAVING clause on any aggregate query, even if there is no GROUP BY clause. This brings SQLite into closer agreement with PostgreSQL and fixes the concern raised by forum post 1a7fea4651. (check-in: 9322a7c2 user: drh tags: trunk)
2022-06-20
19:12
Add an ALWAYS() macro to an always-true branch. (check-in: 364645d8 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

︙︙
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835

    ** Minor point: If this is the case, then the expression will be
    ** re-evaluated for each reference to it.
    */
    assert( (sNC.ncFlags & (NC_UAggInfo|NC_UUpsert|NC_UBaseReg))==0 );
    sNC.uNC.pEList = p->pEList;
    sNC.ncFlags |= NC_UEList;
    if( p->pHaving ){
      if( !pGroupBy ){
        sqlite3ErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
        return WRC_Abort;
      }
      if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort;
    }
    if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort;

    /* Resolve names in table-valued-function arguments */








|
|








1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835

    ** Minor point: If this is the case, then the expression will be
    ** re-evaluated for each reference to it.
    */
    assert( (sNC.ncFlags & (NC_UAggInfo|NC_UUpsert|NC_UBaseReg))==0 );
    sNC.uNC.pEList = p->pEList;
    sNC.ncFlags |= NC_UEList;
    if( p->pHaving ){
      if( (p->selFlags & SF_Aggregate)==0 ){
        sqlite3ErrorMsg(pParse, "HAVING clause on a non-aggregate query");
        return WRC_Abort;
      }
      if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort;
    }
    if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort;

    /* Resolve names in table-valued-function arguments */
︙︙

Changes to test/count.test.

︙︙
122
123
124
125
126
127
128
129
130


131

132
133
134
135
136
137
138

} {1 {DISTINCT aggregates must have exactly one argument}}
do_test count-2.7 {
  uses_op_count {SELECT count(*)+1 FROM t2}
} {0}
do_test count-2.8 {
  uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL}
} {0}
do_test count-2.9 {
  catchsql {SELECT count(*) FROM t2 HAVING count(*)>1}


} {1 {a GROUP BY clause is required before HAVING}}

do_test count-2.10 {
  uses_op_count {SELECT count(*) FROM (SELECT 1)}
} {0}
do_test count-2.11 {
  execsql { CREATE VIEW v1 AS SELECT 1 AS a }
  uses_op_count {SELECT count(*) FROM v1}
} {0}








|
|
>
>
|
>








122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

} {1 {DISTINCT aggregates must have exactly one argument}}
do_test count-2.7 {
  uses_op_count {SELECT count(*)+1 FROM t2}
} {0}
do_test count-2.8 {
  uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL}
} {0}
do_execsql_test count-2.9a {
  SELECT count(*) FROM t2 HAVING count(*)>1;
} {}
do_execsql_test count-2.9b {
  SELECT count(*) FROM t2 HAVING count(*)<10;
} {0}
do_test count-2.10 {
  uses_op_count {SELECT count(*) FROM (SELECT 1)}
} {0}
do_test count-2.11 {
  execsql { CREATE VIEW v1 AS SELECT 1 AS a }
  uses_op_count {SELECT count(*) FROM v1}
} {0}
︙︙

Changes to test/select3.test.

︙︙
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY;
  }
} {1 {near ";": syntax error}}

# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}

# Toss in some HAVING clauses
#
do_test select3-4.1 {
  execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}
} {4 8 5 15}
do_test select3-4.2 {








|
|
|
<








115
116
117
118
119
120
121
122
123
124

125
126
127
128
129
130
131

  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY;
  }
} {1 {near ";": syntax error}}

# Cannot have a HAVING without a GROUP BY
#
do_execsql_test select3-3.1 {
  SELECT log, count(*) FROM t1 HAVING log>=4
} {}


# Toss in some HAVING clauses
#
do_test select3-4.1 {
  execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}
} {4 8 5 15}
do_test select3-4.2 {
︙︙

Changes to test/window1.test.

︙︙
2199
2200
2201
2202
2203
2204
2205





2206
2207

  CREATE TABLE t0(a);
  SELECT a FROM t0, (SELECT a AS b FROM t0)
   WHERE (a,1)=(SELECT 2,2 UNION SELECT sum(b),max(b) OVER(ORDER BY b) ORDER BY 2)
     AND b=4
   ORDER BY b;
} {/1 {.*}/}







finish_test








>
>
>
>
>



2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212

  CREATE TABLE t0(a);
  SELECT a FROM t0, (SELECT a AS b FROM t0)
   WHERE (a,1)=(SELECT 2,2 UNION SELECT sum(b),max(b) OVER(ORDER BY b) ORDER BY 2)
     AND b=4
   ORDER BY b;
} {/1 {.*}/}

do_execsql_test 72.1 {
  CREATE TABLE dual(dummy);  INSERT INTO dual VALUES('X');
  CREATE VIEW v1(x,y) AS SELECT RANK() OVER (PARTITION BY 0), SUM(0) FROM dual;
  SELECT * FROM v1 WHERE true;
} {1 0}

finish_test