Annotation of embedaddon/sqlite3/test/select1.test, revision 1.1
1.1 ! misho 1: # 2001 September 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. The
! 12: # focus of this file is testing the SELECT statement.
! 13: #
! 14: # $Id: select1.test,v 1.70 2009/05/28 01:00:56 drh Exp $
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # Try to select on a non-existant table.
! 20: #
! 21: do_test select1-1.1 {
! 22: set v [catch {execsql {SELECT * FROM test1}} msg]
! 23: lappend v $msg
! 24: } {1 {no such table: test1}}
! 25:
! 26:
! 27: execsql {CREATE TABLE test1(f1 int, f2 int)}
! 28:
! 29: do_test select1-1.2 {
! 30: set v [catch {execsql {SELECT * FROM test1, test2}} msg]
! 31: lappend v $msg
! 32: } {1 {no such table: test2}}
! 33: do_test select1-1.3 {
! 34: set v [catch {execsql {SELECT * FROM test2, test1}} msg]
! 35: lappend v $msg
! 36: } {1 {no such table: test2}}
! 37:
! 38: execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}
! 39:
! 40:
! 41: # Make sure the columns are extracted correctly.
! 42: #
! 43: do_test select1-1.4 {
! 44: execsql {SELECT f1 FROM test1}
! 45: } {11}
! 46: do_test select1-1.5 {
! 47: execsql {SELECT f2 FROM test1}
! 48: } {22}
! 49: do_test select1-1.6 {
! 50: execsql {SELECT f2, f1 FROM test1}
! 51: } {22 11}
! 52: do_test select1-1.7 {
! 53: execsql {SELECT f1, f2 FROM test1}
! 54: } {11 22}
! 55: do_test select1-1.8 {
! 56: execsql {SELECT * FROM test1}
! 57: } {11 22}
! 58: do_test select1-1.8.1 {
! 59: execsql {SELECT *, * FROM test1}
! 60: } {11 22 11 22}
! 61: do_test select1-1.8.2 {
! 62: execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1}
! 63: } {11 22 11 22}
! 64: do_test select1-1.8.3 {
! 65: execsql {SELECT 'one', *, 'two', * FROM test1}
! 66: } {one 11 22 two 11 22}
! 67:
! 68: execsql {CREATE TABLE test2(r1 real, r2 real)}
! 69: execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}
! 70:
! 71: do_test select1-1.9 {
! 72: execsql {SELECT * FROM test1, test2}
! 73: } {11 22 1.1 2.2}
! 74: do_test select1-1.9.1 {
! 75: execsql {SELECT *, 'hi' FROM test1, test2}
! 76: } {11 22 1.1 2.2 hi}
! 77: do_test select1-1.9.2 {
! 78: execsql {SELECT 'one', *, 'two', * FROM test1, test2}
! 79: } {one 11 22 1.1 2.2 two 11 22 1.1 2.2}
! 80: do_test select1-1.10 {
! 81: execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
! 82: } {11 1.1}
! 83: do_test select1-1.11 {
! 84: execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
! 85: } {11 1.1}
! 86: do_test select1-1.11.1 {
! 87: execsql {SELECT * FROM test2, test1}
! 88: } {1.1 2.2 11 22}
! 89: do_test select1-1.11.2 {
! 90: execsql {SELECT * FROM test1 AS a, test1 AS b}
! 91: } {11 22 11 22}
! 92: do_test select1-1.12 {
! 93: execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
! 94: FROM test2, test1}
! 95: } {11 2.2}
! 96: do_test select1-1.13 {
! 97: execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
! 98: FROM test1, test2}
! 99: } {1.1 22}
! 100:
! 101: set long {This is a string that is too big to fit inside a NBFS buffer}
! 102: do_test select1-2.0 {
! 103: execsql "
! 104: DROP TABLE test2;
! 105: DELETE FROM test1;
! 106: INSERT INTO test1 VALUES(11,22);
! 107: INSERT INTO test1 VALUES(33,44);
! 108: CREATE TABLE t3(a,b);
! 109: INSERT INTO t3 VALUES('abc',NULL);
! 110: INSERT INTO t3 VALUES(NULL,'xyz');
! 111: INSERT INTO t3 SELECT * FROM test1;
! 112: CREATE TABLE t4(a,b);
! 113: INSERT INTO t4 VALUES(NULL,'$long');
! 114: SELECT * FROM t3;
! 115: "
! 116: } {abc {} {} xyz 11 22 33 44}
! 117:
! 118: # Error messges from sqliteExprCheck
! 119: #
! 120: do_test select1-2.1 {
! 121: set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg]
! 122: lappend v $msg
! 123: } {1 {wrong number of arguments to function count()}}
! 124: do_test select1-2.2 {
! 125: set v [catch {execsql {SELECT count(f1) FROM test1}} msg]
! 126: lappend v $msg
! 127: } {0 2}
! 128: do_test select1-2.3 {
! 129: set v [catch {execsql {SELECT Count() FROM test1}} msg]
! 130: lappend v $msg
! 131: } {0 2}
! 132: do_test select1-2.4 {
! 133: set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg]
! 134: lappend v $msg
! 135: } {0 2}
! 136: do_test select1-2.5 {
! 137: set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
! 138: lappend v $msg
! 139: } {0 3}
! 140: do_test select1-2.5.1 {
! 141: execsql {SELECT count(*),count(a),count(b) FROM t3}
! 142: } {4 3 3}
! 143: do_test select1-2.5.2 {
! 144: execsql {SELECT count(*),count(a),count(b) FROM t4}
! 145: } {1 0 1}
! 146: do_test select1-2.5.3 {
! 147: execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5}
! 148: } {0 0 0}
! 149: do_test select1-2.6 {
! 150: set v [catch {execsql {SELECT min(*) FROM test1}} msg]
! 151: lappend v $msg
! 152: } {1 {wrong number of arguments to function min()}}
! 153: do_test select1-2.7 {
! 154: set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
! 155: lappend v $msg
! 156: } {0 11}
! 157: do_test select1-2.8 {
! 158: set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
! 159: lappend v [lsort $msg]
! 160: } {0 {11 33}}
! 161: do_test select1-2.8.1 {
! 162: execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
! 163: } {11}
! 164: do_test select1-2.8.2 {
! 165: execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
! 166: } {11}
! 167: do_test select1-2.8.3 {
! 168: execsql {SELECT min(b), min(b) FROM t4}
! 169: } [list $long $long]
! 170: do_test select1-2.9 {
! 171: set v [catch {execsql {SELECT MAX(*) FROM test1}} msg]
! 172: lappend v $msg
! 173: } {1 {wrong number of arguments to function MAX()}}
! 174: do_test select1-2.10 {
! 175: set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
! 176: lappend v $msg
! 177: } {0 33}
! 178: do_test select1-2.11 {
! 179: set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg]
! 180: lappend v [lsort $msg]
! 181: } {0 {22 44}}
! 182: do_test select1-2.12 {
! 183: set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg]
! 184: lappend v [lsort $msg]
! 185: } {0 {23 45}}
! 186: do_test select1-2.13 {
! 187: set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg]
! 188: lappend v $msg
! 189: } {0 34}
! 190: do_test select1-2.13.1 {
! 191: execsql {SELECT coalesce(max(a),'xyzzy') FROM t3}
! 192: } {abc}
! 193: do_test select1-2.13.2 {
! 194: execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3}
! 195: } {xyzzy}
! 196: do_test select1-2.14 {
! 197: set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
! 198: lappend v $msg
! 199: } {1 {wrong number of arguments to function SUM()}}
! 200: do_test select1-2.15 {
! 201: set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
! 202: lappend v $msg
! 203: } {0 44}
! 204: do_test select1-2.16 {
! 205: set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]
! 206: lappend v $msg
! 207: } {1 {wrong number of arguments to function sum()}}
! 208: do_test select1-2.17 {
! 209: set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
! 210: lappend v $msg
! 211: } {0 45}
! 212: do_test select1-2.17.1 {
! 213: execsql {SELECT sum(a) FROM t3}
! 214: } {44.0}
! 215: do_test select1-2.18 {
! 216: set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
! 217: lappend v $msg
! 218: } {1 {no such function: XYZZY}}
! 219: do_test select1-2.19 {
! 220: set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
! 221: lappend v $msg
! 222: } {0 44}
! 223: do_test select1-2.20 {
! 224: set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]
! 225: lappend v $msg
! 226: } {1 {misuse of aggregate function min()}}
! 227:
! 228: # Ticket #2526
! 229: #
! 230: do_test select1-2.21 {
! 231: catchsql {
! 232: SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10
! 233: }
! 234: } {1 {misuse of aliased aggregate m}}
! 235: do_test select1-2.22 {
! 236: catchsql {
! 237: SELECT coalesce(min(f1)+5,11) AS m FROM test1
! 238: GROUP BY f1
! 239: HAVING max(m+5)<10
! 240: }
! 241: } {1 {misuse of aliased aggregate m}}
! 242: do_test select1-2.23 {
! 243: execsql {
! 244: CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
! 245: INSERT INTO tkt2526 VALUES('x','y',NULL);
! 246: INSERT INTO tkt2526 VALUES('x','z',NULL);
! 247: }
! 248: catchsql {
! 249: SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
! 250: }
! 251: } {1 {misuse of aliased aggregate cn}}
! 252:
! 253: # WHERE clause expressions
! 254: #
! 255: do_test select1-3.1 {
! 256: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg]
! 257: lappend v $msg
! 258: } {0 {}}
! 259: do_test select1-3.2 {
! 260: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg]
! 261: lappend v $msg
! 262: } {0 11}
! 263: do_test select1-3.3 {
! 264: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg]
! 265: lappend v $msg
! 266: } {0 11}
! 267: do_test select1-3.4 {
! 268: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg]
! 269: lappend v [lsort $msg]
! 270: } {0 {11 33}}
! 271: do_test select1-3.5 {
! 272: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg]
! 273: lappend v [lsort $msg]
! 274: } {0 33}
! 275: do_test select1-3.6 {
! 276: set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg]
! 277: lappend v [lsort $msg]
! 278: } {0 33}
! 279: do_test select1-3.7 {
! 280: set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg]
! 281: lappend v [lsort $msg]
! 282: } {0 33}
! 283: do_test select1-3.8 {
! 284: set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg]
! 285: lappend v [lsort $msg]
! 286: } {0 {11 33}}
! 287: do_test select1-3.9 {
! 288: set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg]
! 289: lappend v $msg
! 290: } {1 {wrong number of arguments to function count()}}
! 291:
! 292: # ORDER BY expressions
! 293: #
! 294: do_test select1-4.1 {
! 295: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg]
! 296: lappend v $msg
! 297: } {0 {11 33}}
! 298: do_test select1-4.2 {
! 299: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg]
! 300: lappend v $msg
! 301: } {0 {33 11}}
! 302: do_test select1-4.3 {
! 303: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
! 304: lappend v $msg
! 305: } {0 {11 33}}
! 306: do_test select1-4.4 {
! 307: set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
! 308: lappend v $msg
! 309: } {1 {misuse of aggregate: min()}}
! 310:
! 311: # The restriction not allowing constants in the ORDER BY clause
! 312: # has been removed. See ticket #1768
! 313: #do_test select1-4.5 {
! 314: # catchsql {
! 315: # SELECT f1 FROM test1 ORDER BY 8.4;
! 316: # }
! 317: #} {1 {ORDER BY terms must not be non-integer constants}}
! 318: #do_test select1-4.6 {
! 319: # catchsql {
! 320: # SELECT f1 FROM test1 ORDER BY '8.4';
! 321: # }
! 322: #} {1 {ORDER BY terms must not be non-integer constants}}
! 323: #do_test select1-4.7.1 {
! 324: # catchsql {
! 325: # SELECT f1 FROM test1 ORDER BY 'xyz';
! 326: # }
! 327: #} {1 {ORDER BY terms must not be non-integer constants}}
! 328: #do_test select1-4.7.2 {
! 329: # catchsql {
! 330: # SELECT f1 FROM test1 ORDER BY -8.4;
! 331: # }
! 332: #} {1 {ORDER BY terms must not be non-integer constants}}
! 333: #do_test select1-4.7.3 {
! 334: # catchsql {
! 335: # SELECT f1 FROM test1 ORDER BY +8.4;
! 336: # }
! 337: #} {1 {ORDER BY terms must not be non-integer constants}}
! 338: #do_test select1-4.7.4 {
! 339: # catchsql {
! 340: # SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
! 341: # }
! 342: #} {1 {ORDER BY terms must not be non-integer constants}}
! 343:
! 344: do_test select1-4.5 {
! 345: execsql {
! 346: SELECT f1 FROM test1 ORDER BY 8.4
! 347: }
! 348: } {11 33}
! 349: do_test select1-4.6 {
! 350: execsql {
! 351: SELECT f1 FROM test1 ORDER BY '8.4'
! 352: }
! 353: } {11 33}
! 354:
! 355: do_test select1-4.8 {
! 356: execsql {
! 357: CREATE TABLE t5(a,b);
! 358: INSERT INTO t5 VALUES(1,10);
! 359: INSERT INTO t5 VALUES(2,9);
! 360: SELECT * FROM t5 ORDER BY 1;
! 361: }
! 362: } {1 10 2 9}
! 363: do_test select1-4.9.1 {
! 364: execsql {
! 365: SELECT * FROM t5 ORDER BY 2;
! 366: }
! 367: } {2 9 1 10}
! 368: do_test select1-4.9.2 {
! 369: execsql {
! 370: SELECT * FROM t5 ORDER BY +2;
! 371: }
! 372: } {2 9 1 10}
! 373: do_test select1-4.10.1 {
! 374: catchsql {
! 375: SELECT * FROM t5 ORDER BY 3;
! 376: }
! 377: } {1 {1st ORDER BY term out of range - should be between 1 and 2}}
! 378: do_test select1-4.10.2 {
! 379: catchsql {
! 380: SELECT * FROM t5 ORDER BY -1;
! 381: }
! 382: } {1 {1st ORDER BY term out of range - should be between 1 and 2}}
! 383: do_test select1-4.11 {
! 384: execsql {
! 385: INSERT INTO t5 VALUES(3,10);
! 386: SELECT * FROM t5 ORDER BY 2, 1 DESC;
! 387: }
! 388: } {2 9 3 10 1 10}
! 389: do_test select1-4.12 {
! 390: execsql {
! 391: SELECT * FROM t5 ORDER BY 1 DESC, b;
! 392: }
! 393: } {3 10 2 9 1 10}
! 394: do_test select1-4.13 {
! 395: execsql {
! 396: SELECT * FROM t5 ORDER BY b DESC, 1;
! 397: }
! 398: } {1 10 3 10 2 9}
! 399:
! 400:
! 401: # ORDER BY ignored on an aggregate query
! 402: #
! 403: do_test select1-5.1 {
! 404: set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
! 405: lappend v $msg
! 406: } {0 33}
! 407:
! 408: execsql {CREATE TABLE test2(t1 text, t2 text)}
! 409: execsql {INSERT INTO test2 VALUES('abc','xyz')}
! 410:
! 411: # Check for column naming
! 412: #
! 413: do_test select1-6.1 {
! 414: set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
! 415: lappend v $msg
! 416: } {0 {f1 11 f1 33}}
! 417: do_test select1-6.1.1 {
! 418: db eval {PRAGMA full_column_names=on}
! 419: set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
! 420: lappend v $msg
! 421: } {0 {test1.f1 11 test1.f1 33}}
! 422: do_test select1-6.1.2 {
! 423: set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
! 424: lappend v $msg
! 425: } {0 {f1 11 f1 33}}
! 426: do_test select1-6.1.3 {
! 427: set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
! 428: lappend v $msg
! 429: } {0 {f1 11 f2 22}}
! 430: do_test select1-6.1.4 {
! 431: set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
! 432: db eval {PRAGMA full_column_names=off}
! 433: lappend v $msg
! 434: } {0 {f1 11 f2 22}}
! 435: do_test select1-6.1.5 {
! 436: set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
! 437: lappend v $msg
! 438: } {0 {f1 11 f2 22}}
! 439: do_test select1-6.1.6 {
! 440: set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
! 441: lappend v $msg
! 442: } {0 {f1 11 f2 22}}
! 443: do_test select1-6.2 {
! 444: set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
! 445: lappend v $msg
! 446: } {0 {xyzzy 11 xyzzy 33}}
! 447: do_test select1-6.3 {
! 448: set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
! 449: lappend v $msg
! 450: } {0 {xyzzy 11 xyzzy 33}}
! 451: do_test select1-6.3.1 {
! 452: set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]
! 453: lappend v $msg
! 454: } {0 {{xyzzy } 11 {xyzzy } 33}}
! 455: do_test select1-6.4 {
! 456: set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
! 457: lappend v $msg
! 458: } {0 {xyzzy 33 xyzzy 77}}
! 459: do_test select1-6.4a {
! 460: set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
! 461: lappend v $msg
! 462: } {0 {f1+F2 33 f1+F2 77}}
! 463: do_test select1-6.5 {
! 464: set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
! 465: lappend v $msg
! 466: } {0 {test1.f1+F2 33 test1.f1+F2 77}}
! 467: do_test select1-6.5.1 {
! 468: execsql2 {PRAGMA full_column_names=on}
! 469: set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
! 470: execsql2 {PRAGMA full_column_names=off}
! 471: lappend v $msg
! 472: } {0 {test1.f1+F2 33 test1.f1+F2 77}}
! 473: do_test select1-6.6 {
! 474: set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2
! 475: ORDER BY f2}} msg]
! 476: lappend v $msg
! 477: } {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}
! 478: do_test select1-6.7 {
! 479: set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2
! 480: ORDER BY f2}} msg]
! 481: lappend v $msg
! 482: } {0 {f1 11 t1 abc f1 33 t1 abc}}
! 483: do_test select1-6.8 {
! 484: set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B
! 485: ORDER BY f2}} msg]
! 486: lappend v $msg
! 487: } {1 {ambiguous column name: f1}}
! 488: do_test select1-6.8b {
! 489: set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
! 490: ORDER BY f2}} msg]
! 491: lappend v $msg
! 492: } {1 {ambiguous column name: f2}}
! 493: do_test select1-6.8c {
! 494: set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A
! 495: ORDER BY f2}} msg]
! 496: lappend v $msg
! 497: } {1 {ambiguous column name: A.f1}}
! 498: do_test select1-6.9.1 {
! 499: set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
! 500: ORDER BY A.f1, B.f1}} msg]
! 501: lappend v $msg
! 502: } {0 {11 11 11 33 33 11 33 33}}
! 503: do_test select1-6.9.2 {
! 504: set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
! 505: ORDER BY A.f1, B.f1}} msg]
! 506: lappend v $msg
! 507: } {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}}
! 508:
! 509: do_test select1-6.9.3 {
! 510: db eval {
! 511: PRAGMA short_column_names=OFF;
! 512: PRAGMA full_column_names=OFF;
! 513: }
! 514: execsql2 {
! 515: SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
! 516: }
! 517: } {{test1 . f1} 11 {test1 . f2} 22}
! 518: do_test select1-6.9.4 {
! 519: db eval {
! 520: PRAGMA short_column_names=OFF;
! 521: PRAGMA full_column_names=ON;
! 522: }
! 523: execsql2 {
! 524: SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
! 525: }
! 526: } {test1.f1 11 test1.f2 22}
! 527: do_test select1-6.9.5 {
! 528: db eval {
! 529: PRAGMA short_column_names=OFF;
! 530: PRAGMA full_column_names=ON;
! 531: }
! 532: execsql2 {
! 533: SELECT 123.45;
! 534: }
! 535: } {123.45 123.45}
! 536: do_test select1-6.9.6 {
! 537: execsql2 {
! 538: SELECT * FROM test1 a, test1 b LIMIT 1
! 539: }
! 540: } {a.f1 11 a.f2 22 b.f1 11 b.f2 22}
! 541: do_test select1-6.9.7 {
! 542: set x [execsql2 {
! 543: SELECT * FROM test1 a, (select 5, 6) LIMIT 1
! 544: }]
! 545: regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
! 546: set x
! 547: } {a.f1 11 a.f2 22 sqlite_subquery.5 5 sqlite_subquery.6 6}
! 548: do_test select1-6.9.8 {
! 549: set x [execsql2 {
! 550: SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1
! 551: }]
! 552: regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
! 553: set x
! 554: } {a.f1 11 a.f2 22 b.x 5 b.y 6}
! 555: do_test select1-6.9.9 {
! 556: execsql2 {
! 557: SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
! 558: }
! 559: } {test1.f1 11 test1.f2 22}
! 560: do_test select1-6.9.10 {
! 561: execsql2 {
! 562: SELECT f1, t1 FROM test1, test2 LIMIT 1
! 563: }
! 564: } {test1.f1 11 test2.t1 abc}
! 565: do_test select1-6.9.11 {
! 566: db eval {
! 567: PRAGMA short_column_names=ON;
! 568: PRAGMA full_column_names=ON;
! 569: }
! 570: execsql2 {
! 571: SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
! 572: }
! 573: } {test1.f1 11 test1.f2 22}
! 574: do_test select1-6.9.12 {
! 575: execsql2 {
! 576: SELECT f1, t1 FROM test1, test2 LIMIT 1
! 577: }
! 578: } {test1.f1 11 test2.t1 abc}
! 579: do_test select1-6.9.13 {
! 580: db eval {
! 581: PRAGMA short_column_names=ON;
! 582: PRAGMA full_column_names=OFF;
! 583: }
! 584: execsql2 {
! 585: SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
! 586: }
! 587: } {f1 11 f1 11}
! 588: do_test select1-6.9.14 {
! 589: execsql2 {
! 590: SELECT f1, t1 FROM test1, test2 LIMIT 1
! 591: }
! 592: } {f1 11 t1 abc}
! 593: do_test select1-6.9.15 {
! 594: db eval {
! 595: PRAGMA short_column_names=OFF;
! 596: PRAGMA full_column_names=ON;
! 597: }
! 598: execsql2 {
! 599: SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
! 600: }
! 601: } {test1.f1 11 test1.f1 11}
! 602: do_test select1-6.9.16 {
! 603: execsql2 {
! 604: SELECT f1, t1 FROM test1, test2 LIMIT 1
! 605: }
! 606: } {test1.f1 11 test2.t1 abc}
! 607:
! 608:
! 609: db eval {
! 610: PRAGMA short_column_names=ON;
! 611: PRAGMA full_column_names=OFF;
! 612: }
! 613:
! 614: ifcapable compound {
! 615: do_test select1-6.10 {
! 616: set v [catch {execsql2 {
! 617: SELECT f1 FROM test1 UNION SELECT f2 FROM test1
! 618: ORDER BY f2;
! 619: }} msg]
! 620: lappend v $msg
! 621: } {0 {f1 11 f1 22 f1 33 f1 44}}
! 622: do_test select1-6.11 {
! 623: set v [catch {execsql2 {
! 624: SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
! 625: ORDER BY f2+101;
! 626: }} msg]
! 627: lappend v $msg
! 628: } {1 {1st ORDER BY term does not match any column in the result set}}
! 629:
! 630: # Ticket #2296
! 631: ifcapable subquery&&compound {
! 632: do_test select1-6.20 {
! 633: execsql {
! 634: CREATE TABLE t6(a TEXT, b TEXT);
! 635: INSERT INTO t6 VALUES('a','0');
! 636: INSERT INTO t6 VALUES('b','1');
! 637: INSERT INTO t6 VALUES('c','2');
! 638: INSERT INTO t6 VALUES('d','3');
! 639: SELECT a FROM t6 WHERE b IN
! 640: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
! 641: ORDER BY 1 LIMIT 1)
! 642: }
! 643: } {a}
! 644: do_test select1-6.21 {
! 645: execsql {
! 646: SELECT a FROM t6 WHERE b IN
! 647: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
! 648: ORDER BY 1 DESC LIMIT 1)
! 649: }
! 650: } {d}
! 651: do_test select1-6.22 {
! 652: execsql {
! 653: SELECT a FROM t6 WHERE b IN
! 654: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
! 655: ORDER BY b LIMIT 2)
! 656: ORDER BY a;
! 657: }
! 658: } {a b}
! 659: do_test select1-6.23 {
! 660: execsql {
! 661: SELECT a FROM t6 WHERE b IN
! 662: (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
! 663: ORDER BY x DESC LIMIT 2)
! 664: ORDER BY a;
! 665: }
! 666: } {b d}
! 667: }
! 668:
! 669: } ;#ifcapable compound
! 670:
! 671: do_test select1-7.1 {
! 672: set v [catch {execsql {
! 673: SELECT f1 FROM test1 WHERE f2=;
! 674: }} msg]
! 675: lappend v $msg
! 676: } {1 {near ";": syntax error}}
! 677: ifcapable compound {
! 678: do_test select1-7.2 {
! 679: set v [catch {execsql {
! 680: SELECT f1 FROM test1 UNION SELECT WHERE;
! 681: }} msg]
! 682: lappend v $msg
! 683: } {1 {near "WHERE": syntax error}}
! 684: } ;# ifcapable compound
! 685: do_test select1-7.3 {
! 686: set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
! 687: lappend v $msg
! 688: } {1 {near "as": syntax error}}
! 689: do_test select1-7.4 {
! 690: set v [catch {execsql {
! 691: SELECT f1 FROM test1 ORDER BY;
! 692: }} msg]
! 693: lappend v $msg
! 694: } {1 {near ";": syntax error}}
! 695: do_test select1-7.5 {
! 696: set v [catch {execsql {
! 697: SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;
! 698: }} msg]
! 699: lappend v $msg
! 700: } {1 {near "where": syntax error}}
! 701: do_test select1-7.6 {
! 702: set v [catch {execsql {
! 703: SELECT count(f1,f2 FROM test1;
! 704: }} msg]
! 705: lappend v $msg
! 706: } {1 {near "FROM": syntax error}}
! 707: do_test select1-7.7 {
! 708: set v [catch {execsql {
! 709: SELECT count(f1,f2+) FROM test1;
! 710: }} msg]
! 711: lappend v $msg
! 712: } {1 {near ")": syntax error}}
! 713: do_test select1-7.8 {
! 714: set v [catch {execsql {
! 715: SELECT f1 FROM test1 ORDER BY f2, f1+;
! 716: }} msg]
! 717: lappend v $msg
! 718: } {1 {near ";": syntax error}}
! 719: do_test select1-7.9 {
! 720: catchsql {
! 721: SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2;
! 722: }
! 723: } {1 {near "ORDER": syntax error}}
! 724:
! 725: do_test select1-8.1 {
! 726: execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}
! 727: } {11 33}
! 728: do_test select1-8.2 {
! 729: execsql {
! 730: SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'
! 731: ORDER BY f1
! 732: }
! 733: } {11}
! 734: do_test select1-8.3 {
! 735: execsql {
! 736: SELECT f1 FROM test1 WHERE 5-3==2
! 737: ORDER BY f1
! 738: }
! 739: } {11 33}
! 740:
! 741: # TODO: This test is failing because f1 is now being loaded off the
! 742: # disk as a vdbe integer, not a string. Hence the value of f1/(f1-11)
! 743: # changes because of rounding. Disable the test for now.
! 744: if 0 {
! 745: do_test select1-8.4 {
! 746: execsql {
! 747: SELECT coalesce(f1/(f1-11),'x'),
! 748: coalesce(min(f1/(f1-11),5),'y'),
! 749: coalesce(max(f1/(f1-33),6),'z')
! 750: FROM test1 ORDER BY f1
! 751: }
! 752: } {x y 6 1.5 1.5 z}
! 753: }
! 754: do_test select1-8.5 {
! 755: execsql {
! 756: SELECT min(1,2,3), -max(1,2,3)
! 757: FROM test1 ORDER BY f1
! 758: }
! 759: } {1 -3 1 -3}
! 760:
! 761:
! 762: # Check the behavior when the result set is empty
! 763: #
! 764: # SQLite v3 always sets r(*).
! 765: #
! 766: # do_test select1-9.1 {
! 767: # catch {unset r}
! 768: # set r(*) {}
! 769: # db eval {SELECT * FROM test1 WHERE f1<0} r {}
! 770: # set r(*)
! 771: # } {}
! 772: do_test select1-9.2 {
! 773: execsql {PRAGMA empty_result_callbacks=on}
! 774: catch {unset r}
! 775: set r(*) {}
! 776: db eval {SELECT * FROM test1 WHERE f1<0} r {}
! 777: set r(*)
! 778: } {f1 f2}
! 779: ifcapable subquery {
! 780: do_test select1-9.3 {
! 781: set r(*) {}
! 782: db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
! 783: set r(*)
! 784: } {f1 f2}
! 785: }
! 786: do_test select1-9.4 {
! 787: set r(*) {}
! 788: db eval {SELECT * FROM test1 ORDER BY f1} r {}
! 789: set r(*)
! 790: } {f1 f2}
! 791: do_test select1-9.5 {
! 792: set r(*) {}
! 793: db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
! 794: set r(*)
! 795: } {f1 f2}
! 796: unset r
! 797:
! 798: # Check for ORDER BY clauses that refer to an AS name in the column list
! 799: #
! 800: do_test select1-10.1 {
! 801: execsql {
! 802: SELECT f1 AS x FROM test1 ORDER BY x
! 803: }
! 804: } {11 33}
! 805: do_test select1-10.2 {
! 806: execsql {
! 807: SELECT f1 AS x FROM test1 ORDER BY -x
! 808: }
! 809: } {33 11}
! 810: do_test select1-10.3 {
! 811: execsql {
! 812: SELECT f1-23 AS x FROM test1 ORDER BY abs(x)
! 813: }
! 814: } {10 -12}
! 815: do_test select1-10.4 {
! 816: execsql {
! 817: SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)
! 818: }
! 819: } {-12 10}
! 820: do_test select1-10.5 {
! 821: execsql {
! 822: SELECT f1-22 AS x, f2-22 as y FROM test1
! 823: }
! 824: } {-11 0 11 22}
! 825: do_test select1-10.6 {
! 826: execsql {
! 827: SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50
! 828: }
! 829: } {11 22}
! 830: do_test select1-10.7 {
! 831: execsql {
! 832: SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x
! 833: }
! 834: } {11 33}
! 835:
! 836: # Check the ability to specify "TABLE.*" in the result set of a SELECT
! 837: #
! 838: do_test select1-11.1 {
! 839: execsql {
! 840: DELETE FROM t3;
! 841: DELETE FROM t4;
! 842: INSERT INTO t3 VALUES(1,2);
! 843: INSERT INTO t4 VALUES(3,4);
! 844: SELECT * FROM t3, t4;
! 845: }
! 846: } {1 2 3 4}
! 847: do_test select1-11.2.1 {
! 848: execsql {
! 849: SELECT * FROM t3, t4;
! 850: }
! 851: } {1 2 3 4}
! 852: do_test select1-11.2.2 {
! 853: execsql2 {
! 854: SELECT * FROM t3, t4;
! 855: }
! 856: } {a 3 b 4 a 3 b 4}
! 857: do_test select1-11.4.1 {
! 858: execsql {
! 859: SELECT t3.*, t4.b FROM t3, t4;
! 860: }
! 861: } {1 2 4}
! 862: do_test select1-11.4.2 {
! 863: execsql {
! 864: SELECT "t3".*, t4.b FROM t3, t4;
! 865: }
! 866: } {1 2 4}
! 867: do_test select1-11.5.1 {
! 868: execsql2 {
! 869: SELECT t3.*, t4.b FROM t3, t4;
! 870: }
! 871: } {a 1 b 4 b 4}
! 872: do_test select1-11.6 {
! 873: execsql2 {
! 874: SELECT x.*, y.b FROM t3 AS x, t4 AS y;
! 875: }
! 876: } {a 1 b 4 b 4}
! 877: do_test select1-11.7 {
! 878: execsql {
! 879: SELECT t3.b, t4.* FROM t3, t4;
! 880: }
! 881: } {2 3 4}
! 882: do_test select1-11.8 {
! 883: execsql2 {
! 884: SELECT t3.b, t4.* FROM t3, t4;
! 885: }
! 886: } {b 4 a 3 b 4}
! 887: do_test select1-11.9 {
! 888: execsql2 {
! 889: SELECT x.b, y.* FROM t3 AS x, t4 AS y;
! 890: }
! 891: } {b 4 a 3 b 4}
! 892: do_test select1-11.10 {
! 893: catchsql {
! 894: SELECT t5.* FROM t3, t4;
! 895: }
! 896: } {1 {no such table: t5}}
! 897: do_test select1-11.11 {
! 898: catchsql {
! 899: SELECT t3.* FROM t3 AS x, t4;
! 900: }
! 901: } {1 {no such table: t3}}
! 902: ifcapable subquery {
! 903: do_test select1-11.12 {
! 904: execsql2 {
! 905: SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
! 906: }
! 907: } {a 1 b 2}
! 908: do_test select1-11.13 {
! 909: execsql2 {
! 910: SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
! 911: }
! 912: } {a 1 b 2}
! 913: do_test select1-11.14 {
! 914: execsql2 {
! 915: SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
! 916: }
! 917: } {a 1 b 2 max(a) 3 max(b) 4}
! 918: do_test select1-11.15 {
! 919: execsql2 {
! 920: SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
! 921: }
! 922: } {max(a) 3 max(b) 4 a 1 b 2}
! 923: }
! 924: do_test select1-11.16 {
! 925: execsql2 {
! 926: SELECT y.* FROM t3 as y, t4 as z
! 927: }
! 928: } {a 1 b 2}
! 929:
! 930: # Tests of SELECT statements without a FROM clause.
! 931: #
! 932: do_test select1-12.1 {
! 933: execsql2 {
! 934: SELECT 1+2+3
! 935: }
! 936: } {1+2+3 6}
! 937: do_test select1-12.2 {
! 938: execsql2 {
! 939: SELECT 1,'hello',2
! 940: }
! 941: } {1 1 'hello' hello 2 2}
! 942: do_test select1-12.3 {
! 943: execsql2 {
! 944: SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
! 945: }
! 946: } {a 1 b hello c 2}
! 947: do_test select1-12.4 {
! 948: execsql {
! 949: DELETE FROM t3;
! 950: INSERT INTO t3 VALUES(1,2);
! 951: }
! 952: } {}
! 953:
! 954: ifcapable compound {
! 955: do_test select1-12.5 {
! 956: execsql {
! 957: SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
! 958: }
! 959: } {1 2 3 4}
! 960:
! 961: do_test select1-12.6 {
! 962: execsql {
! 963: SELECT 3, 4 UNION SELECT * FROM t3;
! 964: }
! 965: } {1 2 3 4}
! 966: } ;# ifcapable compound
! 967:
! 968: ifcapable subquery {
! 969: do_test select1-12.7 {
! 970: execsql {
! 971: SELECT * FROM t3 WHERE a=(SELECT 1);
! 972: }
! 973: } {1 2}
! 974: do_test select1-12.8 {
! 975: execsql {
! 976: SELECT * FROM t3 WHERE a=(SELECT 2);
! 977: }
! 978: } {}
! 979: }
! 980:
! 981: ifcapable {compound && subquery} {
! 982: do_test select1-12.9 {
! 983: execsql2 {
! 984: SELECT x FROM (
! 985: SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b
! 986: ) ORDER BY x;
! 987: }
! 988: } {x 1 x 3}
! 989: do_test select1-12.10 {
! 990: execsql2 {
! 991: SELECT z.x FROM (
! 992: SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b
! 993: ) AS 'z' ORDER BY x;
! 994: }
! 995: } {x 1 x 3}
! 996: } ;# ifcapable compound
! 997:
! 998:
! 999: # Check for a VDBE stack growth problem that existed at one point.
! 1000: #
! 1001: ifcapable subquery {
! 1002: do_test select1-13.1 {
! 1003: execsql {
! 1004: BEGIN;
! 1005: create TABLE abc(a, b, c, PRIMARY KEY(a, b));
! 1006: INSERT INTO abc VALUES(1, 1, 1);
! 1007: }
! 1008: for {set i 0} {$i<10} {incr i} {
! 1009: execsql {
! 1010: INSERT INTO abc SELECT a+(select max(a) FROM abc),
! 1011: b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
! 1012: }
! 1013: }
! 1014: execsql {COMMIT}
! 1015:
! 1016: # This used to seg-fault when the problem existed.
! 1017: execsql {
! 1018: SELECT count(
! 1019: (SELECT a FROM abc WHERE a = NULL AND b >= upper.c)
! 1020: ) FROM abc AS upper;
! 1021: }
! 1022: } {0}
! 1023: }
! 1024:
! 1025: foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] {
! 1026: db eval "DROP TABLE $tab"
! 1027: }
! 1028: db close
! 1029: sqlite3 db test.db
! 1030:
! 1031: do_test select1-14.1 {
! 1032: execsql {
! 1033: SELECT * FROM sqlite_master WHERE rowid>10;
! 1034: SELECT * FROM sqlite_master WHERE rowid=10;
! 1035: SELECT * FROM sqlite_master WHERE rowid<10;
! 1036: SELECT * FROM sqlite_master WHERE rowid<=10;
! 1037: SELECT * FROM sqlite_master WHERE rowid>=10;
! 1038: SELECT * FROM sqlite_master;
! 1039: }
! 1040: } {}
! 1041: do_test select1-14.2 {
! 1042: execsql {
! 1043: SELECT 10 IN (SELECT rowid FROM sqlite_master);
! 1044: }
! 1045: } {0}
! 1046:
! 1047: if {[db one {PRAGMA locking_mode}]=="normal"} {
! 1048: # Check that ticket #3771 has been fixed. This test does not
! 1049: # work with locking_mode=EXCLUSIVE so disable in that case.
! 1050: #
! 1051: do_test select1-15.1 {
! 1052: execsql {
! 1053: CREATE TABLE t1(a);
! 1054: CREATE INDEX i1 ON t1(a);
! 1055: INSERT INTO t1 VALUES(1);
! 1056: INSERT INTO t1 VALUES(2);
! 1057: INSERT INTO t1 VALUES(3);
! 1058: }
! 1059: } {}
! 1060: do_test select1-15.2 {
! 1061: sqlite3 db2 test.db
! 1062: execsql { DROP INDEX i1 } db2
! 1063: db2 close
! 1064: } {}
! 1065: do_test select1-15.3 {
! 1066: execsql { SELECT 2 IN (SELECT a FROM t1) }
! 1067: } {1}
! 1068: }
! 1069:
! 1070: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test