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