No. This is an aggregate query that relies on the fact that SQLite3 will
choose the values from (one of) the row(s) containing the aggregate to satisfy
select scalars that are not aggregates. Consider the query:
select a, max(idate), b from t where a == 'p006';
This will return the maximum value of idate for all the rows in t where a ==
'p006', and the values of a and b from (the same) one of the rows on which this
maximum is found. If no rows matching the condition a == 'p006' then the
maximum is NULL and the values of a and b are also NULL since there is no row
from which the values may be taken.
So, this query will return the values b, c, e from the (one of the rows) with
the max(idate) from all the rows in t having a == 'p006', or NULL for those
values if no such row exists. If no such row exists then the IfNull function
will convert those NULL values into the given values. The first ifnull will
never actually be executed (since the first value is not null, the second,
max(idate), will never be used). However, since IfNull is a function, in this
case taking two arguments, all the arguments must be evaluated BEFORE the
function can be evaluated.
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".
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Doug
>Sent: Monday, 18 November, 2019 12:49
>To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Question about: Adding a record to a table with
>select failure
>
>I'm really confused now. I don't understand the semantics of:
>SELECT IfNull('p006', Max(idate)),
> IfNull(b, 1),
> IfNull(c, 2),
> 'y',
> IfNull(e, 4),
> '2019-20-12'
> FROM t
> WHERE a = 'p006';
>
>versus this:
>SELECT (a,b,c,d,e,idate) from t where a = "p006"
>
>Doesn't the where clause that cannot be satisfied in both cases guarantee
>that no rows will be selected, when there are no records in the database?
>Doug
>
>> -----Original Message-----
>> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org>
>> On Behalf Of Jose Isaias Cabrera
>> Sent: Monday, November 18, 2019 12:11 PM
>> To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
>> Subject: Re: [sqlite] Question about: Adding a record to a table
>> with select failure
>>
>>
>> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
>> Jose Isaias Cabrera
>> [clip]
>> > > >
>> > > > INSERT INTO t (a, b, c, d, e, idate)
>> > > > SELECT IfNull('p006', Max(idate)),
>> > > > IfNull(b, 1),
>> > > > IfNull(c, 2),
>> > > > 'y',
>> > > > IfNull(e, 4),
>> > > > '2019-20-12'
>> > > > FROM t
>> > > > WHERE a = 'p006';
>> >
>> > I think that you will never insert the first record with a query
>> like this, since
>> > the select returns 0 records of there are none in the database
>> yet.
>>
>> Well, it does...
>> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
>> idate));
>> sqlite> INSERT INTO t
>> ...> SELECT IfNull('p001', Max(idate)),
>> ...> IfNull(b, 1),
>> ...> IfNull(c, 2),
>> ...> IfNull(d,'n'),
>> ...> IfNull(e, 4),
>> ...> '2019-20-11'
>> ...> FROM t
>> ...> WHERE a = 'p001';
>> sqlite> select * from t;
>> p001|1|2|n|4|2019-20-11
>> sqlite>
>>
>> And, since I put an uniqueness on a and idate, now these can not
>> be repeated, so if I run the same command again,
>> sqlite> INSERT INTO t
>> ...> SELECT IfNull('p001', Max(idate)),
>> ...> IfNull(b, 1),
>> ...> IfNull(c, 2),
>> ...> IfNull(d,'n'),
>> ...> IfNull(e, 4),
>> ...> '2019-02-11'
>> ...> FROM t
>> ...> WHERE a = 'p001';
>> Error: UNIQUE constraint failed: t.a, t.idate
>> sqlite>
>>
>> I do not get a repeated record for 'p001' and 2019-02-11; But if
>> they are different,
>> sqlite> INSERT INTO t
>> ...> SELECT IfNull('p002', Max(idate)),
>> ...> IfNull(b, 1),
>> ...> IfNull(c, 2),
>> ...> IfNull(d,'n'),
>> ...> IfNull(e, 4),
>> ...> '2019-02-11'
>> ...> FROM t
>> ...> WHERE a = 'p002';
>> sqlite> select * from t;
>> p001|1|2|n|4|2019-02-11
>> p002|1|2|n|4|2019-02-11
>>
>> It'll work. Thanks.
>>
>> josé
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> users
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
