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