1 # 2008 July 15

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library. 

    12 #

    13 # The focus of this file is testing how SQLite generates the names

    14 # of columns in a result set.

    15 #

    16 # $Id: colname.test,v 1.5 2008/09/13 01:20:15 drh Exp $

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 # Rules (applied in order):

    22 #

    23 # (1) If there is an AS clause, use it.

    24 #

    25 # (2) A non-trival expression (not a table column name) then the name is

    26 #     a copy of the expression text.

    27 #

    28 # (3) If short_column_names=ON, then just the abbreviated column name without

    29 #     the table name.

    30 #

    31 # (4) When short_column_names=OFF and full_column_names=OFF then

    32 #     use case (2) for simple queries and case (5) for joins.

    33 #

    34 # (5) When short_column_names=OFF and full_column_names=ON then

    35 #     use the form: TABLE.COLUMN

    36 #

    37 

    38 

    39 # Verify the default settings for short_column_name and full_column_name

    40 #

    41 do_test colname-1.1 {

    42   db eval {PRAGMA short_column_names}

    43 } {1}

    44 do_test colname-1.2 {

    45   db eval {PRAGMA full_column_names}

    46 } {0}

    47 

    48 # Tests for then short=ON and full=any

    49 #

    50 do_test colname-2.1 {

    51   db eval {

    52     CREATE TABLE tabc(a,b,c);

    53     INSERT INTO tabc VALUES(1,2,3);

    54     CREATE TABLE txyz(x,y,z);

    55     INSERT INTO txyz VALUES(4,5,6);

    56     CREATE TABLE tboth(a,b,c,x,y,z);

    57     INSERT INTO tboth VALUES(11,12,13,14,15,16);

    58     CREATE VIEW v1 AS SELECT tabC.a, txyZ.x, * 

    59       FROM tabc, txyz ORDER BY 1 LIMIT 1;

    60     CREATE VIEW v2 AS SELECT tabC.a, txyZ.x, tboTh.a, tbotH.x, *

    61       FROM tabc, txyz, tboth ORDER BY 1 LIMIT 1;

    62   }

    63   execsql2 {

    64     SELECT * FROM tabc;

    65   }

    66 } {a 1 b 2 c 3}

    67 do_test colname-2.2 {

    68   execsql2 {

    69     SELECT Tabc.a, tAbc.b, taBc.c, * FROM tabc

    70   }

    71 } {a 1 b 2 c 3 a 1 b 2 c 3}

    72 do_test colname-2.3 {

    73   execsql2 {

    74     SELECT +tabc.a, -tabc.b, tabc.c, * FROM tabc

    75   }

    76 } {+tabc.a 1 -tabc.b -2 c 3 a 1 b 2 c 3}

    77 do_test colname-2.4 {

    78   execsql2 {

    79     SELECT +tabc.a AS AAA, -tabc.b AS BBB, tabc.c CCC, * FROM tabc

    80   }

    81 } {AAA 1 BBB -2 CCC 3 a 1 b 2 c 3}

    82 do_test colname-2.5 {

    83   execsql2 {

    84     SELECT tabc.a, txyz.x, * FROM tabc, txyz;

    85   }

    86 } {a 1 x 4 a 1 b 2 c 3 x 4 y 5 z 6}

    87 do_test colname-2.6 {

    88   execsql2 {

    89     SELECT tabc.a, txyz.x, tabc.*, txyz.* FROM tabc, txyz;

    90   }

    91 } {a 1 x 4 a 1 b 2 c 3 x 4 y 5 z 6}

    92 do_test colname-2.7 {

    93   execsql2 {

    94     SELECT tabc.a, txyz.x, tboth.a, tboth.x, * FROM tabc, txyz, tboth;

    95   }

    96 } {a 11 x 14 a 11 x 14 a 11 b 12 c 13 x 14 y 15 z 16 a 11 b 12 c 13 x 14 y 15 z 16}

    97 do_test colname-2.8 {

    98   execsql2 {

    99     SELECT * FROM v1 ORDER BY 2;

   100   }

   101 } {a 1 x 4 a:1 1 b 2 c 3 x:1 4 y 5 z 6}

   102 do_test colname-2.9 {

   103   execsql2 {

   104     SELECT * FROM v2 ORDER BY 2;

   105   }

   106 } {a 1 x 4 a:1 11 x:1 14 a:2 1 b 2 c 3 x:2 4 y 5 z 6 a:3 11 b:1 12 c:1 13 x:3 14 y:1 15 z:1 16}

   107 

   108 

   109 # Tests for short=OFF and full=OFF

   110 #

   111 do_test colname-3.1 {

   112   db eval {

   113     PRAGMA short_column_names=OFF;

   114     PRAGMA full_column_names=OFF;

   115     CREATE VIEW v3 AS SELECT tabC.a, txyZ.x, *

   116       FROM tabc, txyz ORDER BY 1 LIMIT 1;

   117     CREATE VIEW v4 AS SELECT tabC.a, txyZ.x, tboTh.a, tbotH.x, * 

   118       FROM tabc, txyz, tboth ORDER BY 1 LIMIT 1;

   119   }

   120   execsql2 {

   121     SELECT * FROM tabc;

   122   }

   123 } {a 1 b 2 c 3}

   124 do_test colname-3.2 {

   125   execsql2 {

   126     SELECT Tabc.a, tAbc.b, taBc.c FROM tabc

   127   }

   128 } {Tabc.a 1 tAbc.b 2 taBc.c 3}

   129 do_test colname-3.3 {

   130   execsql2 {

   131     SELECT +tabc.a, -tabc.b, tabc.c FROM tabc

   132   }

   133 } {+tabc.a 1 -tabc.b -2 tabc.c 3}

   134 do_test colname-3.4 {

   135   execsql2 {

   136     SELECT +tabc.a AS AAA, -tabc.b AS BBB, tabc.c CCC FROM tabc

   137   }

   138 } {AAA 1 BBB -2 CCC 3}

   139 do_test colname-3.5 {

   140   execsql2 {

   141     SELECT Tabc.a, Txyz.x, * FROM tabc, txyz;

   142   }

   143 } {Tabc.a 1 Txyz.x 4 a 1 b 2 c 3 x 4 y 5 z 6}

   144 do_test colname-3.6 {

   145   execsql2 {

   146     SELECT tabc.*, txyz.* FROM tabc, txyz;

   147   }

   148 } {a 1 b 2 c 3 x 4 y 5 z 6}

   149 do_test colname-3.7 {

   150   execsql2 {

   151     SELECT * FROM tabc, txyz, tboth;

   152   }

   153 } {a 11 b 12 c 13 x 14 y 15 z 16 a 11 b 12 c 13 x 14 y 15 z 16}

   154 do_test colname-3.8 {

   155   execsql2 {

   156     SELECT v1.a, * FROM v1 ORDER BY 2;

   157   }

   158 } {v1.a 1 a 1 x 4 a:1 1 b 2 c 3 x:1 4 y 5 z 6}

   159 do_test colname-3.9 {

   160   execsql2 {

   161     SELECT * FROM v2 ORDER BY 2;

   162   }

   163 } {a 1 x 4 a:1 11 x:1 14 a:2 1 b 2 c 3 x:2 4 y 5 z 6 a:3 11 b:1 12 c:1 13 x:3 14 y:1 15 z:1 16}

   164 do_test colname-3.10 {

   165   execsql2 {

   166     SELECT * FROM v3 ORDER BY 2;

   167   }

   168 } {a 1 x 4 a:1 1 b 2 c 3 x:1 4 y 5 z 6}

   169 do_test colname-3.11 {

   170   execsql2 {

   171     SELECT * FROM v4 ORDER BY 2;

   172   }

   173 } {a 1 x 4 a:1 11 x:1 14 a:2 1 b 2 c 3 x:2 4 y 5 z 6 a:3 11 b:1 12 c:1 13 x:3 14 y:1 15 z:1 16}

   174 

   175 # Test for short=OFF and full=ON

   176 #

   177 do_test colname-4.1 {

   178   db eval {

   179     PRAGMA short_column_names=OFF;

   180     PRAGMA full_column_names=ON;

   181     CREATE VIEW v5 AS SELECT tabC.a, txyZ.x, *

   182       FROM tabc, txyz ORDER BY 1 LIMIT 1;

   183     CREATE VIEW v6 AS SELECT tabC.a, txyZ.x, tboTh.a, tbotH.x, * 

   184       FROM tabc, txyz, tboth ORDER BY 1 LIMIT 1;

   185   }

   186   execsql2 {

   187     SELECT * FROM tabc;

   188   }

   189 } {tabc.a 1 tabc.b 2 tabc.c 3}

   190 do_test colname-4.2 {

   191   execsql2 {

   192     SELECT Tabc.a, tAbc.b, taBc.c FROM tabc

   193   }

   194 } {tabc.a 1 tabc.b 2 tabc.c 3}

   195 do_test colname-4.3 {

   196   execsql2 {

   197     SELECT +tabc.a, -tabc.b, tabc.c FROM tabc

   198   }

   199 } {+tabc.a 1 -tabc.b -2 tabc.c 3}

   200 do_test colname-4.4 {

   201   execsql2 {

   202     SELECT +tabc.a AS AAA, -tabc.b AS BBB, tabc.c CCC FROM tabc

   203   }

   204 } {AAA 1 BBB -2 CCC 3}

   205 do_test colname-4.5 {

   206   execsql2 {

   207     SELECT Tabc.a, Txyz.x, * FROM tabc, txyz;

   208   }

   209 } {tabc.a 1 txyz.x 4 tabc.a 1 tabc.b 2 tabc.c 3 txyz.x 4 txyz.y 5 txyz.z 6}

   210 do_test colname-4.6 {

   211   execsql2 {

   212     SELECT tabc.*, txyz.* FROM tabc, txyz;

   213   }

   214 } {tabc.a 1 tabc.b 2 tabc.c 3 txyz.x 4 txyz.y 5 txyz.z 6}

   215 do_test colname-4.7 {

   216   execsql2 {

   217     SELECT * FROM tabc, txyz, tboth;

   218   }

   219 } {tabc.a 1 tabc.b 2 tabc.c 3 txyz.x 4 txyz.y 5 txyz.z 6 tboth.a 11 tboth.b 12 tboth.c 13 tboth.x 14 tboth.y 15 tboth.z 16}

   220 do_test colname-4.8 {

   221   execsql2 {

   222     SELECT * FROM v1 ORDER BY 2;

   223   }

   224 } {v1.a 1 v1.x 4 v1.a:1 1 v1.b 2 v1.c 3 v1.x:1 4 v1.y 5 v1.z 6}

   225 do_test colname-4.9 {

   226   execsql2 {

   227     SELECT * FROM v2 ORDER BY 2;

   228   }

   229 } {v2.a 1 v2.x 4 v2.a:1 11 v2.x:1 14 v2.a:2 1 v2.b 2 v2.c 3 v2.x:2 4 v2.y 5 v2.z 6 v2.a:3 11 v2.b:1 12 v2.c:1 13 v2.x:3 14 v2.y:1 15 v2.z:1 16}

   230 do_test colname-4.10 {

   231   execsql2 {

   232     SELECT * FROM v3 ORDER BY 2;

   233   }

   234 } {v3.a 1 v3.x 4 v3.a:1 1 v3.b 2 v3.c 3 v3.x:1 4 v3.y 5 v3.z 6}

   235 do_test colname-4.11 {

   236   execsql2 {

   237     SELECT * FROM v4 ORDER BY 2;

   238   }

   239 } {v4.a 1 v4.x 4 v4.a:1 11 v4.x:1 14 v4.a:2 1 v4.b 2 v4.c 3 v4.x:2 4 v4.y 5 v4.z 6 v4.a:3 11 v4.b:1 12 v4.c:1 13 v4.x:3 14 v4.y:1 15 v4.z:1 16}

   240 do_test colname-4.12 {

   241   execsql2 {

   242     SELECT * FROM v5 ORDER BY 2;

   243   }

   244 } {v5.a 1 v5.x 4 v5.a:1 1 v5.b 2 v5.c 3 v5.x:1 4 v5.y 5 v5.z 6}

   245 do_test colname-4.13 {

   246   execsql2 {

   247     SELECT * FROM v6 ORDER BY 2;

   248   }

   249 } {v6.a 1 v6.x 4 v6.a:1 11 v6.x:1 14 v6.a:2 1 v6.b 2 v6.c 3 v6.x:2 4 v6.y 5 v6.z 6 v6.a:3 11 v6.b:1 12 v6.c:1 13 v6.x:3 14 v6.y:1 15 v6.z:1 16}

   250 

   251 # ticket #3229

   252 do_test colname-5.1 {

   253   lreplace [db eval {

   254     SELECT x.* FROM sqlite_master X LIMIT 1;

   255   }] 3 3 x

   256 } {table tabc tabc x {CREATE TABLE tabc(a,b,c)}}

   257 

   258 # ticket #3370, #3371, #3372

   259 #

   260 do_test colname-6.1 {

   261   db close

   262   sqlite3 db test.db

   263   db eval {

   264     CREATE TABLE t6(a, ['a'], ["a"], "[a]", [`a`]);

   265     INSERT INTO t6 VALUES(1,2,3,4,5);

   266   }

   267   execsql2 {SELECT * FROM t6}

   268 } {a 1 'a' 2 {"a"} 3 {[a]} 4 `a` 5}

   269 do_test colname-6.2 {

   270   execsql2 {SELECT ['a'], [`a`], "[a]", [a], ["a"] FROM t6}

   271 } {'a' 2 `a` 5 {[a]} 4 a 1 {"a"} 3}

   272 do_test colname-6.3 {

   273   execsql2 {SELECT "'a'", "`a`", "[a]", "a", """a""" FROM t6}

   274 } {'a' 2 `a` 5 {[a]} 4 a 1 {"a"} 3}

   275 do_test colname-6.4 {

   276   execsql2 {SELECT `'a'`, ```a```, `[a]`, `a`, `"a"` FROM t6}

   277 } {'a' 2 `a` 5 {[a]} 4 a 1 {"a"} 3}

   278 do_test colname-6.11 {

   279   execsql2 {SELECT a, max(a) AS m FROM t6}

   280 } {a 1 m 1}

   281 do_test colname-6.12 {

   282   execsql2 {SELECT `a`, max(a) AS m FROM t6}

   283 } {a 1 m 1}

   284 do_test colname-6.13 {

   285   execsql2 {SELECT "a", max(a) AS m FROM t6}

   286 } {a 1 m 1}

   287 do_test colname-6.14 {

   288   execsql2 {SELECT [a], max(a) AS m FROM t6}

   289 } {a 1 m 1}

   290 do_test colname-6.15 {

   291   execsql2 {SELECT t6.a, max(a) AS m FROM t6}

   292 } {a 1 m 1}

   293 do_test colname-6.16 {

   294   execsql2 {SELECT ['a'], max(['a']) AS m FROM t6}

   295 } {'a' 2 m 2}

   296 do_test colname-6.17 {

   297   execsql2 {SELECT ["a"], max(["a"]) AS m FROM t6}

   298 } {{"a"} 3 m 3}

   299 do_test colname-6.18 {

   300   execsql2 {SELECT "[a]", max("[a]") AS m FROM t6}

   301 } {{[a]} 4 m 4}

   302 do_test colname-6.19 {

   303   execsql2 {SELECT "`a`", max([`a`]) AS m FROM t6}

   304 } {`a` 5 m 5}

   305 

   306 finish_test