/ 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. foundin changed to: "3.19.0"
  2. icomment:
    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.
    
    <blockquote><verbatim>
    CREATE TABLE x(a,b);
    INSERT INTO x VALUES(1,2);
    SELECT x.a FROM x LEFT JOIN (SELECT * FROM x) AS y;
    </verbatim></blockquote>
    
    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.
    
    <b>Analysis:</b>
    
    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. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Important"
  6. status changed to: "Open"
  7. title changed to:
    Column name shift due to LEFT JOIN query flattening
    
  8. type changed to: "Code_Defect"