On Apr 11, 2013, at 5:56 AM, Stephen Chrzanowski <pontia...@gmail.com> wrote:
> Looking at the query, in the CLI, what if you rename the three bolded > "title" fields to something else? I think the system is getting messed up > because a field in a table is called "title" and you're naming a query > called "title" but you're not distinctly stating which "title" you want to > query against, as in a table field or your query. Not to mention it looks > like you're doing the SORT BY twice against the same field in the same > manor. Mind you I have NOT tested this against anything. I already tried that, and it failed. I didn't try harder because the second occurrence of title is there because the generating code supplies it, it seems to be pulling fields out by name, the string "title" occurs about 150 times in the code, and I don't know which to change. It also seems to indicate that I need to rename all the names external to the query, not just "title". I will need to experiment harder. Thanks! > > sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE > bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS * title*, > (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT > 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE > moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url AS > url, moz_places.visit_count AS visit_count, moz_places.frecency AS > frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS > lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id > = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks > WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t, > moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id = > moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND > folders.parent != 4 GROUP BY bms.id ORDER BY *title *COLLATE NOCASE ASC, > *title > *COLLATE NOCASE ASC; > > > > > On Wed, Apr 10, 2013 at 3:32 PM, Perry Wagle <wa...@mac.com> wrote: > >> >> On Apr 10, 2013, at 5:33 AM, Richard Hipp <d...@sqlite.org> wrote: >> >>> On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle <wa...@mac.com> wrote: >>> >>>> I have a problem with a field name being ambiguous when it wasn't >> before. >>>> Is this a bug or a feature? >>>> >>>> Firefox 19 uses Sqlite 3.7.14.1 which does what it should. Firefox 20 >>>> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the >> ORDER >>>> BY in the below query: >>>> >>>> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE >>>> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title, >>>> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT >>>> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE >>>> moz_favicons.id = moz_places.favicon_id) END) AS favicon, >> moz_places.url >>>> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS >>>> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS >>>> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE >> place_id >>>> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks >>>> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t, >>>> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id = >>>> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND >>>> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC, >>>> title COLLATE NOCASE ASC >>>> >>>> I claim "title" shouldn't be ambiguous, since the first SELECT >> expression >>>> was named "title". What's the consensus? >>>> >>> >>> There were enhancements to name resolution in 3.7.16. Did you try it >>> there? >> >> See below: >> >> SQLite version 3.7.16.1 2013-03-29 13:44:34 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE >> (CASE bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS >> title, (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT >> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE >> moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url >> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS >> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS >> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id >> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks >> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t, >> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id = >> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND >> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC, >> title COLLATE NOCASE ASC; >> Error: ambiguous column name: title >> >>> >>> -- >>> D. Richard Hipp >>> d...@sqlite.org >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
