On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera <jic...@outlook.com>
wrote:
>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';
>> >
--
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
