On Wednesday, 15 January, 2020 02:06, Jean-Baptiste Gardette <jb.garde...@orens.fr> wrote:
> Just to be sure, is it unsafe to write a non agregate SELECT with GROUP > BY and HAVING clauses (without sub-SELECT) for the sole prupose > explained before (even if the approache is discutable) ? Presently, yes it is. >I understand 2 different answers here : >- "No, this kind of query can't be rewritten by the optimizer for the >technical reasons (VDBE, index etc)" This is presently the case. Current versions of the query planner will not optimize these queries but will instead execute them as written. That is statements of the form "select ... from ... where ... group by ... having ... order by ... limit ... offset ..." will not convert the "group by" into an "order by" or the "having" into "where". The provers required to allow this sort of transformation do not exist at present. >- "Yes it is unsafe, a future version of SQLite may optimize differently >this kind of query" Yes. A future version of SQLite may indeed process the query by turning the "group by" into an "order by" (if there isn't one, or just ignoring it if there is an order by, or perhaps merging them) and moving the "having" to a "where" condition. Doing this would require that the optimizer recognize that the group by expression can only result in single row groups and that neither the select list nor the having expression contain aggregate functions. There is almost nothing to be gained from this optimization, however, so it is highly unlikely that such provers would be written in order to implement this particular optimization. Contrast this with recent optimizations that have been added, for example, the LEFT JOIN optimization which downgrades an outer join into an inner join if it can be proved that the overall result will be the same (the extra candidates generated by the outer join will be removed from the result set by a where clause, so going to all the bother of adding and processing them in the first place serves no purpose), or that leaf tables which are not referenced in the select list are removed from the query since generating those results merely incurs a cost to no effect. These optimizations can have a significant impact on performance. -- 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
