/ View Ticket
Login
Ticket UUID: 1c69be2dafc28b9f2d80ff022c89395afa106083
Title: Incorrect GROUP BY when input and output columns have the same name
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-02-17 02:06:36
Version Found In: 3.7.17
User Comments:
drh added on 2013-08-14 20:12:07:

Consider the following SQL:

CREATE TABLE t1(m CHAR(2));
INSERT INTO t1 VALUES('ax');
INSERT INTO t1 VALUES('bx');
INSERT INTO t1 VALUES('cy');
SELECT '1', count(*), substr(m,2,1) AS m FROM t1 GROUP BY m;
SELECT '2', count(*), substr(m,2,1) AS mx FROM t1 GROUP BY m;

Both queries should give the same three-row result. But in SQLite, the first query gives a two-row results where one row has a count of 2. The expression of a GROUP BY clause should bind preferentially to the input table, not the output table.


drh added on 2014-06-10 20:11:36:

It has come to light that some legacy Android applications are coded with queries that are logically equivalent to this:

CREATE TABLE t1(a,x);
CREATE TABLE t2(a,y);
SELECT t1.a AS a, x, y FROM t1, t2 GROUP BY a;

The original fix for this ticket (see check-in [f2d175f975cd]) caused such queries to give an error: "ambiguous column: a". The query is indeed incorrect. Nevertheless, we need to find a way to get SQLite to run and return the correct result even for these incorrect, legacy queries.