1# 2014-12-28 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# Verify that WITHOUT ROWID tables work correctly when the PRIMARY KEY 13# has redundant columns. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19do_execsql_test without_rowid6-100 { 20 CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID; 21 CREATE INDEX t1a ON t1(b, b); 22 WITH RECURSIVE 23 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000) 24 INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c; 25 ANALYZE; 26} {} 27do_execsql_test without_rowid6-101 { 28 SELECT name, key FROM pragma_index_xinfo('t1'); 29} {a 1 b 1 c 1 d 1 e 0} 30do_execsql_test without_rowid6-110 { 31 SELECT c FROM t1 WHERE a=123; 32} {x123y} 33do_execsql_test without_rowid6-120 { 34 SELECT c FROM t1 WHERE b=1123; 35} {x123y} 36do_execsql_test without_rowid6-130 { 37 SELECT c FROM t1 ORDER BY a DESC LIMIT 5; 38} {x1000y x999y x998y x997y x996y} 39do_execsql_test without_rowid6-140 { 40 SELECT c FROM t1 ORDER BY b LIMIT 5; 41} {x1y x2y x3y x4y x5y} 42 43# Column t1.b starts out as a unique index, but that index is 44# subsequently converted into a PRIMARY KEY. 45# 46do_execsql_test without_rowid6-200 { 47 DROP TABLE IF EXISTS t1; 48 CREATE TABLE t1( 49 a UNIQUE, 50 b UNIQUE, 51 c UNIQUE, 52 PRIMARY KEY(b) 53 ) WITHOUT ROWID; 54 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9); 55 SELECT a FROM t1 WHERE b>3 ORDER BY b; 56} {4 1} 57do_execsql_test without_rowid6-201 { 58 SELECT name, key FROM pragma_index_xinfo('t1'); 59} {b 1 a 0 c 0} 60do_execsql_test without_rowid6-210 { 61 EXPLAIN QUERY PLAN 62 SELECT a FROM t1 WHERE b>3 ORDER BY b; 63} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../} 64do_execsql_test without_rowid6-220 { 65 PRAGMA index_list(t1); 66} {/sqlite_autoindex_t1_2 1 pk/} 67 68do_execsql_test without_rowid6-300 { 69 DROP TABLE IF EXISTS t1; 70 CREATE TABLE t1( 71 a UNIQUE, 72 b PRIMARY KEY, 73 c UNIQUE, 74 UNIQUE(b) 75 ) WITHOUT ROWID; 76 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9); 77 SELECT a FROM t1 WHERE b>3 ORDER BY b; 78} {4 1} 79do_execsql_test without_rowid6-310 { 80 EXPLAIN QUERY PLAN 81 SELECT a FROM t1 WHERE b>3 ORDER BY b; 82} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../} 83do_execsql_test without_rowid6-320 { 84 PRAGMA index_list(t1); 85} {/sqlite_autoindex_t1_2 1 pk/} 86 87do_execsql_test without_rowid6-400 { 88 DROP TABLE IF EXISTS t1; 89 CREATE TABLE t1( 90 a UNIQUE, 91 b UNIQUE PRIMARY KEY, 92 c UNIQUE 93 ) WITHOUT ROWID; 94 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9); 95 SELECT a FROM t1 WHERE b>3 ORDER BY b; 96} {4 1} 97do_execsql_test without_rowid6-410 { 98 EXPLAIN QUERY PLAN 99 SELECT a FROM t1 WHERE b>3 ORDER BY b; 100} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../} 101do_execsql_test without_rowid6-420 { 102 PRAGMA index_list(t1); 103} {/sqlite_autoindex_t1_2 1 pk/} 104 105do_execsql_test without_rowid6-500 { 106 DROP TABLE IF EXISTS t1; 107 CREATE TABLE t1(a,b,c, 108 UNIQUE(b,c), 109 PRIMARY KEY(b,c) 110 ) WITHOUT ROWID; 111 INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9); 112 SELECT a FROM t1 WHERE b>3 ORDER BY b; 113} {4 1} 114do_execsql_test without_rowid6-501 { 115 SELECT name, key FROM pragma_index_xinfo('t1'); 116} {b 1 c 1 a 0} 117do_execsql_test without_rowid6-510 { 118 EXPLAIN QUERY PLAN 119 SELECT a FROM t1 WHERE b>3 ORDER BY b; 120} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../} 121do_execsql_test without_rowid6-520 { 122 PRAGMA index_list(t1); 123} {/sqlite_autoindex_t1_1 1 pk/} 124 125do_catchsql_test without_rowid6-600 { 126 CREATE TABLE t6(a,b,c,PRIMARY KEY(a,rowid,b))WITHOUT ROWID; 127} {1 {no such column: rowid}} 128 129 130finish_test 131