Change UNION ALL to UNION. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Howard Kapustein > Sent: Monday, 24 April, 2017 15:46 > To: SQLite mailing list > Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ? > > >but for CTEs/views/subqueries, you might have to change the query itself, > or the database schema, or determine that the automatic index is the best > choice in your situation. > > Yes. The question is how? > > The CTE's doing a SELECT _PackageID FROM Package where _PackageID is a > primary key. If I change the CTE to SELECT rowid FROM Package would SQLite > still think an automatic index is needed? Or is the automatic index for > the CTE itself? I can only declare the PackageIdByAll CTE has results but > no type info e.g. > WITH PackageIdByAll(_PackageID PRIMARY KEY NOT NULL) > isn't legal. > > Right now my log's being spammed with oodles of automatic index messages > because of the CTE. I'd like to *fix* this, but right now the only > solution I'm seeing is suppressing all automatic index log messages. I'd > like to still see REAL ones to not mask new issues. > > Suggestions? > > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Clemens Ladisch > Sent: Friday, April 21, 2017 11:13 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ? > > Howard Kapustein wrote: > > I'm setting SQLite logging a warning > > Warning 0x11C: automatic index on PackageIdByAll(_PackageID) > > > > WITH ... > > PackageIdByAll(_PackageID) AS (...) > > SELECT * > > FROM ... > > INNER JOIN PackageIdByAll AS cte_p ON > > cte_p._PackageID=pkg._PackageID > > > > Why? > > > > PackageIdByAll is a CTE. I can't CREATE INDEX for it. > > The warning tells you that the database created an automatic index. > This does not imply that you _must_ create an index, it's just a hint that > you should think about whether it is possible to improve the query. > If you have a table, creating the index explicitly is the easiest way, but > for CTEs/views/subqueries, you might have to change the query itself, or > the database schema, or determine that the automatic index is the best > choice in your situation. > > > _PackageID is the primary key of the Package table So indexing should > > be covered being the table's ROWID. > > When you use the rowid to look up a table row, you still don't know which > CTE row that would correspond to. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglis > ts.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite- > users&data=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93 > ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C63628438442085 > 4952&sdata=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D&reserved=0 > _______________________________________________ > 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
