Re: [sqlite] GROUP BY: ambiguous column name?
On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > GROUP BY on a result column fails with "ambiguous column name": > > SELECT > COALESCE(x.id, y.id) AS id > FROM > y > LEFT JOIN > x > ON > x.id = y.fk > ORDER BY > id > ; Sorry, that should read GROUP BY of course. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
GROUP BY works on your input data, not output data. You want to GROUP BY COALESCE(x.id, y.id) On Thu, Sep 25, 2014 at 1:37 PM, Mark Lawrence wrote: > On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote: > > GROUP BY on a result column fails with "ambiguous column name": > > > > SELECT > > COALESCE(x.id, y.id) AS id > > FROM > > y > > LEFT JOIN > > x > > ON > > x.id = y.fk > > ORDER BY > > id > > ; > > Sorry, that should read GROUP BY of course. > > -- > Mark Lawrence > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Cory Nelson http://int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
On Thu Sep 25, 2014 at 01:43:20PM -0500, Cory Nelson wrote: > GROUP BY works on your input data, not output data. You want to GROUP > BY COALESCE(x.id, y.id) That describes the behaviour I demonstrated, but not the reasoning behind it nor the documentation pointing to that reasoning. Is SQLite clever enough to recognize that a GROUP BY expression and a SELECT column are the same? Because in my mind I think of the query as working in the following stages for the most efficient operation: - JOIN ROWS - SELECT COLUMNS -- COALESCE done here - GROUP OUTPUT - ORDER OUTPUT However, it appears to be the case that the order is more like this: - JOIN ROWS - GROUP ROWS -- COALESCE done here - SELECT COLUMNS -- COALESCE also done here? - ORDER OUTPUT Which looks to me like the expression would be calculated twice. Is SQLite smart enough to figure out that the columns are the same and only do it once? If SQLite is capable of determining that the same expression is used twice, why not just accept a SELECT expression? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
This is just the way the SQL standard mandates it to work, not an oddity specific to SQLite. I imagine the optimizer is probably smart enough to not do the work twice here, but someone else will need to chime in to confirm that. If you want to be sure, you can use a CTE. On Thu, Sep 25, 2014 at 2:02 PM, Mark Lawrence wrote: > On Thu Sep 25, 2014 at 01:43:20PM -0500, Cory Nelson wrote: > > > GROUP BY works on your input data, not output data. You want to GROUP > > BY COALESCE(x.id, y.id) > > That describes the behaviour I demonstrated, but not the reasoning > behind it nor the documentation pointing to that reasoning. > > Is SQLite clever enough to recognize that a GROUP BY expression and a > SELECT column are the same? Because in my mind I think of the query as > working in the following stages for the most efficient operation: > > - JOIN ROWS > - SELECT COLUMNS -- COALESCE done here > - GROUP OUTPUT > - ORDER OUTPUT > > However, it appears to be the case that the order is more like this: > > - JOIN ROWS > - GROUP ROWS -- COALESCE done here > - SELECT COLUMNS -- COALESCE also done here? > - ORDER OUTPUT > > Which looks to me like the expression would be calculated twice. Is > SQLite smart enough to figure out that the columns are the same and > only do it once? > > If SQLite is capable of determining that the same expression is used > twice, why not just accept a SELECT expression? > > -- > Mark Lawrence > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Cory Nelson http://int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
On Thu, 25 Sep 2014 20:32:29 +0200 Mark Lawrence wrote: > I would have expected the group to work the same as the order, given > that I think of the group as happening on the result set before any > joins. ORDER BY is different. It's not even *related* to GROUP BY. To elaborate on Cory Nelson's answer, GROUP BY is part of the SELECT processing, and ORDER BY is not. By analogy: $ cat input | SELECT | ORDER BY > output One way to understand it is that relational variables -- tables and similar in SQL -- don't have order. You can manipulate them independent of order, using as many operators (JOIN, WHERE, etc.) as you please. Only when you're done can you ask the system, as a convenience to yourself, to sort the results. SQLite takes some liberties with that model. Depending on one's point of view, supporting LIMIT and ORDER BY in a subquery is either a boon or a wart. Regardless, SQL as defined by the standard treats ORDER BY differently, for the above reasons. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY: ambiguous column name?
If you give your id columns unambiguous names to begin with, things will work out a lot better for you in the long run. May I suggest the following modification to your tables to remove ambiguity: CREATE TABLE x( x_id INTEGER ); CREATE TABLE y( y_id INTEGER, x_id INTEGER REFERENCES x(x_id) ); SELECT COALESCE(x_id, y_id) AS id FROM y LEFT JOIN x USING (x_id) GROUP BY id; -- View this message in context: http://sqlite.1065341.n5.nabble.com/GROUP-BY-ambiguous-column-name-tp78282p78302.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
