SQLite

View Ticket
Login
2019-01-25
13:16 Wiki page "checkin/718ead555b09892f3cbaddb1775ede4acfa4a2986d69f15da1270c433e5dd578" (artifact: b76247d6 user: drh)
2017-12-23
12:33
Move the generation of output column names earlier in the case of a CREATE TABLE AS. This is a fix for ticket [3b4450072511e62] and a continuation of check-in [ade7ddf1998190b2b63] that fixes cases of ticket [de3403bf5ae5f72ed6] that were missed previously. (check-in: 6b2ff26c user: drh tags: trunk)
11:51
Add a SELECTTRACE() macro to indicate when column names are assigned to a SELECT statement. This helps with debugging for tickets like [de3403bf5ae5f72e] and [3b4450072511e621]. (check-in: 8f194008 user: drh tags: trunk)
2017-12-22
19:28 New ticket [3b445007] Inconsistent column names. (artifact: 6ebc7575 user: drh)
2017-07-31
17:40
More consistent column names. Cherry-pick of [09834279] and [0c38dde45] as a fix for ticket [de3403bf5ae]. (check-in: be0e24a0 user: drh tags: branch-3.20)
16:42 Fixed ticket [de3403bf]: Column name shift due to LEFT JOIN query flattening plus 3 other changes (artifact: 726bbd38 user: drh)
16:42
Move the generation of output column names earlier, to right after name resolution and before query transformations such as flattening. This prevents the names from getting mangled by query transformations, and obviates hacks in the query flattener that attempt to work around the name mangling. The resulting code is smaller and faster and gives more consistent output. Fix to ticket [de3403bf5ae5f72ed]. (check-in: ade7ddf1 user: drh tags: trunk)
13:11
Move the generation of output column names earlier, to right after name resolution and before query transformations such as flattening. This prevents the names from getting mangled by query transformations, and obviates hacks in the query flattener that attempt to work around the name mangling. The resulting code is smaller and faster and gives more consistent output. This is a fix for ticket [de3403bf5ae5f72ed] and a cherry-pick of check-in [09834279aeca3bda] (check-in: 499942b3 user: drh tags: branch-3.19)
2017-07-29
17:47 Ticket [de3403bf] Column name shift due to LEFT JOIN query flattening status still Open with 6 other changes (artifact: 84cc8ca4 user: drh)
16:01
Move the generation of output column names earlier, to right after name resolution and before query transformations such as flattening. This prevents the names from getting mangled by query transformations, and obviates hacks in the query flattener that attempt to work around the name mangling. The resulting code is smaller and faster and gives more consistent output. This is an alternative fix to ticket [de3403bf5ae5f72ed]. (check-in: 09834279 user: drh tags: early-column-names)
03:33
In the query flattener, only add AS clauses to output columns of the outer query that are copied directly from the inner query. Formerly, all columns of the outer query received an AS clause if they did not have one already. This is a proposed fix for ticket [de3403bf5ae5f72]. (check-in: 439cc5c5 user: drh tags: flattener-column-names)
03:26 New ticket [de3403bf] Column name shift due to LEFT JOIN query flattening. (artifact: e6b7df69 user: drh)

Ticket Hash: de3403bf5ae5f72ed6638f6360339254c8c62f53
Title: Column name shift due to LEFT JOIN query flattening
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-07-31 16:42:56
Version Found In: 3.19.0
User Comments:
drh added on 2017-07-29 03:26:32:

The name of the output column on the SELECT statement below as reported by the sqlite3_column_name() interface changed in between versions 3.18.0 and 3.19.0.

CREATE TABLE x(a,b);
INSERT INTO x VALUES(1,2);
SELECT x.a FROM x LEFT JOIN (SELECT * FROM x) AS y;

SQLite version 3.18.0 and earlier reports the column name as "a". But in SQLite 3.19.0 and 3.20.0, the column name is reported as "x.a".

The SQLite documentation clearly states that, if there is no AS clause, the names of output columns are indeterminate, arbitrary, and subject to change. The column names are intended for display purposes only. And yet, many applications depend on particular column names. This name shift resulted at least one application error.

The problem was introduced by check-in [41c27bc0ff1d3135], which added a new optimization for doing query flattening on the right operand of a LEFT JOIN.

Analysis:

Without an AS clause, if the output column is a direct reference to one of the input table columns, then the output column name will be the same as the input column name. Except, if query flattening occurs, the output column name becomes an exact copy of the input SQL text.

The exception explains the name shift. Prior to version 3.19.0, the query flattener would not operate on a LEFT JOIN. Hence the output column name was derived from the input column name and was "a". But once the LEFT JOIN query flattener optimization was added by check-in [41c27bc0ff1d3135], the column naming exception mentioned in the previous paragraph kicked in and caused the column to be named as it was typed in the input SQL: "x.a".


drh added on 2017-07-29 17:47:12:

Column Naming Rules

The names of the columns in the result set of a query can take three forms:

  1. If there is an AS clause, the label to the right of the AS keyword becomes the column name. This has always worked and has never been an issue. When it doubt, the programmer can always put an AS clause onto the result expression to define the name of a column.
  1. If the output column is a direct reference to a column of an input table, the the output column name is the name of the column in the input table, as it appears in the CREATE TABLE (or CREATE VIEW) statement for the input table. (The previous sentence assumes that PRAGMA short_column_names if ON and PRAGMA full_column_names is OFF, which are the default settings. As both of those pragmas are deprecated, we will not consider the other cases.) For example, if the table is "CREATE TABLE t1(x)", and if the SELECT statement refers to the column of t1 as "x" or "X" or "(X)" or "t1.x" or "main.t1.x" or "[main].[t1].[x]" or anything else, the output column name will always be just "x", as it appears in the CREATE TABLE statement.
  1. If neither of the above apply, then the column name is a copy of the part of the original SQL statement that specified the column. Examples: In the query "SELECT 5+11;" the name of the column is "5+11". In the query "SELECT +x FROM t1" the "+x" is not a direct reference to the t1.x column because of the "+" operator, so the name of the output column is "+x".

Columns are named using form (1) if it applies, otherwise use form (2) if it applies, and finally fall back to form (3) if none of the other forms apply.

Except, since about 2009, if the query flattener (https://sqlite.org/optoverview.html#flattening) is involved, then columns of the query that would otherwise use form (2) shift to using form (3).

The exception is a hack that was inserted in order to give sensible column names when working with views. The problem that the hack aimed to address is demonstrated by this SQL:

CREATE TABLE x(a,b);
INSERT INTO x VALUES(1,2);
CREATE VIEW y(c,d) AS SELECT a,b FROM x;
SELECT c, d FROM y;

On expects the column names from the final query to be "c" and "d". But the query flattener runs in this case and transforms the input query into "SELECT a,b FROM x". With no adjustments, the names of the output columns for the revised query would be "a" and "b". The hack to fix this was to change the column naming rule from (2) to (3) so that the columns would continue to be called "c" and "d" as they were shown in the input text.

The Problem

That exception to the column naming rules that kicks in when the query flattener runs is the cause of the bug reported in this ticket. The original query used the form "t1.x" to specify the column in the result set. In SQLite 3.18.0 and earlier, rule (2) above applied and the output column name was just "x". Then in SQLite 3.19.0, the query flattener was enhanced so that it could be applied to the query in question, making the query run faster. But a side-effect was that the column naming rule shifted from (2) to (3) and the output column name became "t1.x".

Proposed Solution

The proposed solution (implemented by check-in [09834279aeca3bda]) is to eliminate the exception to the column naming rules associated with the query flattener. That means that the query that provoked this ticket would continue to use column naming rule (2) and would continue to see the column name as "x" as the application expects. This change is accomplished by moving the column naming step ahead of the query flattener step, so that column naming happens before query flattening, not afterwards.

Possible Problems

The proposed solution makes column names more consistent and easier to understand. It also makes the implementation smaller and simpler and helps it to run a little faster. However, there may be legacy programs that depended on the old behavior (shifting the column naming rule from (2) to (3) when using the query flattener) and those legacy programs could potentially break due to this change.