third_party/sqlite/src/test/resolver01.test

   1 # 2013-04-13
   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 #
  12 # This file tests features of the name resolver (the component that
  13 # figures out what identifiers in the SQL statement refer to) that
  14 # were fixed by ticket [2500cdb9be]
  15 #
  16 # See also tickets [1c69be2daf] and [f617ea3125] from 2013-08-14.
  17 #
  18
  19 set testdir [file dirname $argv0]
  20 source $testdir/tester.tcl
  21
  22 # "ORDER BY y" binds to the output result-set column named "y"
  23 # if available.  If no output column is named "y", then try to
  24 # bind against an input column named "y".
  25 #
  26 # This is classical SQL92 behavior.
  27 #
  28 do_test resolver01-1.1 {
  29   catchsql {
  30     CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22);
  31     CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44);
  32     SELECT 1 AS y FROM t1, t2 ORDER BY y;
  33   }
  34 } {0 1}
  35 do_test resolver01-1.2 {
  36   catchsql {
  37     SELECT 1 AS yy FROM t1, t2 ORDER BY y;
  38   }
  39 } {1 {ambiguous column name: y}}
  40 do_test resolver01-1.3 {
  41   catchsql {
  42     CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(11,44),(33,22);
  43     SELECT x AS y FROM t3 ORDER BY y;
  44   }
  45 } {0 {11 33}}
  46 do_test resolver01-1.4 {
  47   catchsql {
  48     SELECT x AS yy FROM t3 ORDER BY y;
  49   }
  50 } {0 {33 11}}
  51
  52 # SQLite allows the WHERE clause to reference output columns if there is
  53 # no other way to resolve the name.
  54 #
  55 do_test resolver01-1.5 {
  56   catchsql {
  57     SELECT x AS yy FROM t3 ORDER BY yy;
  58   }
  59 } {0 {11 33}}
  60 do_test resolver01-1.6 {
  61   catchsql {
  62     SELECT x AS yy FROM t3 ORDER BY 1;
  63   }
  64 } {0 {11 33}}
  65
  66 # The "ORDER BY y COLLATE nocase" form works the same as "ORDER BY y".
  67 # The "y" binds more tightly to output columns than to input columns.
  68 #
  69 # This is for compatibility with SQL92 and with historical SQLite behavior.
  70 # Note that PostgreSQL considers "y COLLATE nocase" to be an expression
  71 # and thus PostgreSQL treats this case as if it where the 3.x case below.
  72 #
  73 do_test resolver01-2.1 {
  74   catchsql {
  75     SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase;
  76   }
  77 } {0 2}
  78 do_test resolver01-2.2 {
  79   catchsql {
  80     SELECT 2 AS yy FROM t1, t2 ORDER BY y COLLATE nocase;
  81   }
  82 } {1 {ambiguous column name: y}}
  83 do_test resolver01-2.3 {
  84   catchsql {
  85     SELECT x AS y FROM t3 ORDER BY y COLLATE nocase;
  86   }
  87 } {0 {11 33}}
  88 do_test resolver01-2.4 {
  89   catchsql {
  90     SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase;
  91   }
  92 } {0 {33 11}}
  93 do_test resolver01-2.5 {
  94   catchsql {
  95     SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase;
  96   }
  97 } {0 {11 33}}
  98 do_test resolver01-2.6 {
  99   catchsql {
 100     SELECT x AS yy FROM t3 ORDER BY 1 COLLATE nocase;
 101   }
 102 } {0 {11 33}}
 103
 104 # But if the form is "ORDER BY expr" then bind more tightly to the
 105 # the input column names and only use the output column names if no
 106 # input column name matches.
 107 #
 108 # This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL.
 109 # Note that Oracle works differently.
 110 #
 111 do_test resolver01-3.1 {
 112   catchsql {
 113     SELECT 3 AS y FROM t1, t2 ORDER BY +y;
 114   }
 115 } {1 {ambiguous column name: y}}
 116 do_test resolver01-3.2 {
 117   catchsql {
 118     SELECT 2 AS yy FROM t1, t2 ORDER BY +y;
 119   }
 120 } {1 {ambiguous column name: y}}
 121 do_test resolver01-3.3 {
 122   catchsql {
 123     SELECT x AS y FROM t3 ORDER BY +y;
 124   }
 125 } {0 {33 11}}
 126 do_test resolver01-3.4 {
 127   catchsql {
 128     SELECT x AS yy FROM t3 ORDER BY +y;
 129   }
 130 } {0 {33 11}}
 131 do_test resolver01-3.5 {
 132   catchsql {
 133     SELECT x AS yy FROM t3 ORDER BY +yy
 134   }
 135 } {0 {11 33}}
 136
 137 # This is the test case given in ticket [f617ea3125e9] (with table name
 138 # changed from "t1" to "t4".  The behavior of (1) and (3) match with
 139 # PostgreSQL, but we intentionally break with PostgreSQL to provide
 140 # SQL92 behavior for case (2).
 141 #
 142 do_execsql_test resolver01-4.1 {
 143   CREATE TABLE t4(m CHAR(2));
 144   INSERT INTO t4 VALUES('az');
 145   INSERT INTO t4 VALUES('by');
 146   INSERT INTO t4 VALUES('cx');
 147   SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m;
 148   SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary;
 149   SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m);
 150 } {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x}
 151
 152 ##########################################################################
 153 # Test cases for ticket [1c69be2dafc28]:  Make sure the GROUP BY binds
 154 # more tightly to the input tables in all cases.
 155 #
 156 # This first case case has been wrong in SQLite for time out of mind.
 157 # For SQLite version 3.7.17 the answer was two rows, which is wrong.
 158 #
 159 do_execsql_test resolver01-5.1 {
 160   CREATE TABLE t5(m CHAR(2));
 161   INSERT INTO t5 VALUES('ax');
 162   INSERT INTO t5 VALUES('bx');
 163   INSERT INTO t5 VALUES('cy');
 164   SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2;
 165 } {1 x 1 x 1 y}
 166
 167 # This case is unambiguous and has always been correct.
 168 #
 169 do_execsql_test resolver01-5.2 {
 170   SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2;
 171 } {1 x 1 x 1 y}
 172
 173 # This case is not allowed in standard SQL, but SQLite allows and does
 174 # the sensible thing.
 175 #
 176 do_execsql_test resolver01-5.3 {
 177   SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2;
 178 } {1 y 2 x}
 179 do_execsql_test resolver01-5.4 {
 180   SELECT count(*), substr(m,2,1) AS mx FROM t5
 181    GROUP BY substr(m,2,1) ORDER BY 1, 2;
 182 } {1 y 2 x}
 183
 184 # These test case weere provided in the 2013-08-14 email from Rob Golsteijn
 185 # that originally reported the problem of ticket [1c69be2dafc28].
 186 #
 187 do_execsql_test resolver01-6.1 {
 188   CREATE TABLE t61(name);
 189   SELECT min(name) FROM t61 GROUP BY lower(name);
 190 } {}
 191 do_execsql_test resolver01-6.2 {
 192   SELECT min(name) AS name FROM t61 GROUP BY lower(name);
 193 } {}
 194 do_execsql_test resolver01-6.3 {
 195   CREATE TABLE t63(name);
 196   INSERT INTO t63 VALUES (NULL);
 197   INSERT INTO t63 VALUES ('abc');
 198   SELECT count(),
 199        NULLIF(name,'abc') AS name
 200     FROM t63
 201    GROUP BY lower(name);
 202 } {1 {} 1 {}}
 203
 204
 205
 206
 207
 208 finish_test