/ Ticket Change Details
Login
Overview

Artifact ID: e6b7df6949d71dff82c7b5d6cc086ee4a962daafa6794b90f9775978d4bbf89a
Ticket: de3403bf5ae5f72ed6638f6360339254c8c62f53
Column name shift due to LEFT JOIN query flattening
User & Date: drh 2017-07-29 03:26:32
Changes

  1. Change foundin to "3.19.0"
  2. Change icomment to:

    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".

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Important"
  6. Change status to "Open"
  7. Change title to:

    Column name shift due to LEFT JOIN query flattening

  8. Change type to "Code_Defect"