/ Check-in [0c38dde4]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New test cases for column name generation interacting with the query flattener.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | early-column-names
Files: files | file ages | folders
SHA3-256: 0c38dde4543d6183a6ab0b7b3b75819f56c47704756a2426d54d3f20468d78d8
User & Date: drh 2017-07-29 17:02:22
References
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
Context
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
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
2017-07-29
17:02
New test cases for column name generation interacting with the query flattener. Closed-Leaf check-in: 0c38dde4 user: drh tags: early-column-names
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/colname.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library. 
    12     12   #
    13     13   # The focus of this file is testing how SQLite generates the names
    14     14   # of columns in a result set.
    15     15   #
    16         -# $Id: colname.test,v 1.7 2009/06/02 15:47:38 drh Exp $
    17     16   
    18     17   set testdir [file dirname $argv0]
    19     18   source $testdir/tester.tcl
    20     19   
    21     20   # Rules (applied in order):
    22     21   #
    23     22   # (1) If there is an AS clause, use it.
................................................................................
   321    320   do_test colname-8.1 {
   322    321     db eval {
   323    322       CREATE TABLE "t3893"("x");
   324    323       INSERT INTO t3893 VALUES(123);
   325    324       SELECT "y"."x" FROM (SELECT "x" FROM "t3893") AS "y";
   326    325     }
   327    326   } {123}
          327  +
          328  +# 2017-07-29: Interaction between column naming and query flattening.
          329  +# For years now, the query flattener has inserted AS clauses on the
          330  +# outer query that were the original SQL text of the column.  This caused
          331  +# column-name shifts when the query flattener was enhanced, breaking
          332  +# legacy applications.  See https://sqlite.org/src/info/41c27bc0ff1d3135
          333  +# for details.
          334  +#
          335  +# To fix this, the column naming logic was moved ahead of the query
          336  +# flattener so that column names are assigned before the query flattener
          337  +# runs.
          338  +#
          339  +db close
          340  +sqlite3 db :memory:
          341  +do_test colname-9.100 {
          342  +  db eval {
          343  +    CREATE TABLE t1(a,b);
          344  +    INSERT INTO t1 VALUES(1,2);
          345  +    CREATE VIEW v1(x,y) AS SELECT a,b FROM t1;
          346  +  }
          347  +  execsql2 {SELECT v1.x, (Y) FROM v1}
          348  +  # Prior to the fix, this would return:  "v1.x 1 (Y) 2"
          349  +} {x 1 y 2}
          350  +do_test colname-9.110 {
          351  +  execsql2 {SELECT * FROM v1}
          352  +} {x 1 y 2}
          353  +do_test colname-9.120 {
          354  +  db eval {
          355  +    CREATE VIEW v2(x,y) AS SELECT a,b FROM t1 LIMIT 10;
          356  +  }
          357  +  execsql2 {SELECT * FROM v2 WHERE 1}
          358  +} {x 1 y 2}
          359  +do_test colname-9.130 {
          360  +  execsql2 {SELECT v2.x, [v2].[y] FROM v2 WHERE 1}
          361  +} {x 1 y 2}
          362  +do_test colname-9.140 {
          363  +  execsql2 {SELECT +x, +y FROM v2 WHERE 1}
          364  +} {+x 1 +y 2}
          365  +
          366  +do_test colname-9.200 {
          367  +  db eval {
          368  +    CREATE TABLE t2(c,d);
          369  +    INSERT INTO t2 VALUES(3,4);
          370  +    CREATE VIEW v3 AS SELECT c AS a, d AS b FROM t2;
          371  +  }
          372  +  execsql2 {SELECT t1.a, v3.a AS n FROM t1 LEFT JOIN v3}
          373  +} {a 1 n 3}
          374  +do_test colname-9.211 {
          375  +  execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3}
          376  +} {n 1 a 3}
          377  +do_test colname-9.210 {
          378  +  execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3}
          379  +} {a 1 n 3}
          380  +
   328    381   
   329    382   finish_test