On Tuesday, 14 January, 2020 06:58, Jean-Baptiste Gardette <jb.garde...@orens.fr> wrote:
>Consider the following exemple : >CREATE TABLE t1 ( >a TEXT PRIMARY KEY, >b INTEGER); >SELECT * >FROM t1 >GROUP BY a >HAVING b > 1; >Will the GROUP BY clause be supressed and HAVING clause be rewritten in >WHERE clause by the optimizer ? No. The VDBE code will be generated as you have specified. The "group by a" will be used to select the index to be used to access the table data, and at the end of the processing of each group of records, the "having b > 1" will be applied to the group to determine whether the group is output. The PRIMARY KEY constraint on a does not make "a" unique since a is not constrained not null, so there may be multiple records in the same group. Even if "a" is constrained not null the query is still processed as a group by/having even though each group can only consist of one record even though (and only in that case) the query equivalent to "select * from t1 where b > 1 order by a". -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
