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.

[clip]

> >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, on

Jose Isaias Cabrera
> >> Sent: Monday, November 18, 2019 12:11 PM
> >
> >> 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

Reply via email to