Thanks, Keith.
Keith Medcalf, on Monday, November 18, 2019 07:25 PM, wrote...
>
>
> On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera, on
>
> >Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
> >>
> >> This relies on two implementation details particular to SQLite3 which
> >> hold at present, but may of course change at any time:
> >> (1) that selecting a non-aggregate scalar column will return a value
> >> from (one of) the row(s) matching the value of the aggregate (most
> >> RDBMS used to do this, most now throw an error at this construct); and,
> >> (2) that the optimizer will not optimize "IfNull('p006', max(idate))"
> >> into 'p006' since the result must always be 'p006' which would of
> >> course render the select to be a simple select and not an aggregate
> >> causing "all hell to break loose".
>
> >Thanks Keith. So, you are saying that this is a bad INSERT, and I don't
> >know much to argue, but is working. If I take out the first IfNull, and
> >there is not, at least one instance of 'p006' in the table, the INSERT
> >never works. I was thinking of using COALESCE, but that would also mean
> >that one or the other would have to be not null. Any suggestion would be
> >appreciated.
>
> No, what I am saying is that this particular insert is working with the
> current version of SQLite3 and is unlikely to work with any other RDBMS
> that uses SQL as this is using an "implementation detail" specific to
> SQLite3 that does not exist elsewhere; and, secondly that the particular
> construction used putting the aggregate function in the second argument
> of a coalesce where the first argument is a constant is dependent on the
> query optimizer not "optimizing way" the entire expression based on the
> fact that the first argument is a not-null constant (though if this
> optimized away the aggregate-ness of the query this would be a bug in the
> optimizer, since the optimization process should not cause different
> results to occur).
>
> What I am saying is that you need to be aware of this and if you change
> versions of SQLite3 then you need to make sure that this query still
> operates as you intend it to operate.
>
> So far as I know the first constraint (the scalar use of columns that are
> not part of the group by clause in a query) is unlikely to change because
> this is a significant change to backwards compatibility and Richard is
> unlikely to make such a change without warnings in second coming type.
>
> The second is unlikely with SQLite3 because the optimizer is not based on
> a query re-write which would be more prone to this sort of issue.
>
> The second issue can be bypassed entirely by using bound parameters (since
> there is no way except by examining the bound parameter at execution time
> to know that a bound parameter is not NULL) thus precluding the
> possibility of optimizing away the aggregate function. As in:
>
> select ifnull(:a, max(idate)),
> ifnull(b, 1),
> ifnull(c, 2),
> :y,
> ifnull(e, 4),
> :idate
> from t
> where a == :a
>
> and binding values for :a, :y and :idate. There is no way to know at prepare
> time that parameter :a cannot be null so therefore the ifnull(:a... and the
> evaluation of the aggregate cannot be optimized away.
>
> >[clip]
> >
> >> >SELECT IfNull('p006', Max(idate)),
> >> > IfNull(b, 1),
> >> > IfNull(c, 2),
> >> > 'y',
> >> > IfNull(e, 4),
> >> > '2019-20-12'
> >> > FROM t
> >> > WHERE a = 'p006';
> >> >
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
