mirror of
https://github.com/tursodatabase/limbo.git
synced 2025-12-23 08:21:09 +00:00
dc3cd84a70
SQLite rejects `CREATE INDEX idx ON t(rowid)` with "no such column: rowid" because rowid is a pseudo-column, not an actual column. Limbo was incorrectly allowing this. The fix removes the special exception for ROWID_STRS (rowid, _rowid_, oid) in validate_index_expression(). Now these identifiers are only allowed if they match an actual column name in the table (i.e., when shadowed). 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
103 lines
3.6 KiB
Tcl
Executable file
103 lines
3.6 KiB
Tcl
Executable file
#!/usr/bin/env tclsh
|
|
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
|
|
do_execsql_test_on_specific_db {:memory:} create-index-quoted-identifiers {
|
|
CREATE TABLE "t t" ("a a");
|
|
CREATE INDEX "idx idx" ON "t t" ("a a");
|
|
CREATE UNIQUE INDEX "unique idx idx" ON "t t" ("a a");
|
|
|
|
SELECT sql FROM sqlite_schema WHERE type='index';
|
|
} {
|
|
"CREATE INDEX \"idx idx\" ON \"t t\" (\"a a\")"
|
|
"CREATE UNIQUE INDEX \"unique idx idx\" ON \"t t\" (\"a a\")"
|
|
}
|
|
|
|
|
|
# single-column index key: creating unique index fails with duplicates
|
|
do_execsql_test_in_memory_any_error create-unique-index-with-duplicates-1 {
|
|
CREATE TABLE t1(a, b);
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t1 VALUES(1, 2);
|
|
CREATE UNIQUE INDEX idx1 ON t1(a);
|
|
}
|
|
|
|
# multi-column index key: creating unique index fails with duplicates
|
|
do_execsql_test_in_memory_any_error create-unique-index-with-duplicates-2 {
|
|
CREATE TABLE t2(a, b, c);
|
|
INSERT INTO t2 VALUES(1, 2, 3);
|
|
INSERT INTO t2 VALUES(1, 2, 4);
|
|
CREATE UNIQUE INDEX idx2 ON t2(a, b);
|
|
}
|
|
|
|
|
|
# single-column index key: creating unique index succeeds because NULLs are never equal
|
|
do_execsql_test_on_specific_db {:memory:} create-unique-index-with-duplicates-3 {
|
|
CREATE TABLE t3(a);
|
|
INSERT INTO t3 VALUES(NULL);
|
|
INSERT INTO t3 VALUES(NULL);
|
|
CREATE UNIQUE INDEX idx3 ON t3(a);
|
|
SELECT count(*) FROM t3;
|
|
} {2}
|
|
|
|
# multi-column index key: creating unique index succeeds because NULLs are never equal
|
|
do_execsql_test_on_specific_db {:memory:} create-unique-index-with-duplicates-4 {
|
|
CREATE TABLE t4(a, b);
|
|
INSERT INTO t4 VALUES(1, NULL);
|
|
INSERT INTO t4 VALUES(1, NULL);
|
|
CREATE UNIQUE INDEX idx4 ON t4(a, b);
|
|
SELECT count(*) FROM t4;
|
|
} {2}
|
|
|
|
# multi-column index key: creating unique index succeeds when all NULLs
|
|
do_execsql_test_on_specific_db {:memory:} create-unique-index-with-duplicates-5 {
|
|
CREATE TABLE t5(a, b);
|
|
INSERT INTO t5 VALUES(NULL, NULL);
|
|
INSERT INTO t5 VALUES(NULL, NULL);
|
|
CREATE UNIQUE INDEX idx5 ON t5(a, b);
|
|
SELECT count(*) FROM t5;
|
|
} {2}
|
|
|
|
# creating index on rowid (pseudo-column) should fail
|
|
# SQLite: "no such column: rowid", Limbo: "invalid expression in CREATE INDEX: rowid"
|
|
do_execsql_test_in_memory_error create-index-on-rowid {
|
|
CREATE TABLE t6(x);
|
|
CREATE INDEX idx6 ON t6(rowid);
|
|
} {rowid}
|
|
|
|
# creating index on _rowid_ (pseudo-column alias) should fail
|
|
# SQLite: "no such column: _rowid_", Limbo: "invalid expression in CREATE INDEX: _rowid_"
|
|
do_execsql_test_in_memory_error create-index-on-rowid-alias-1 {
|
|
CREATE TABLE t7(x);
|
|
CREATE INDEX idx7 ON t7(_rowid_);
|
|
} {_rowid_}
|
|
|
|
# creating index on oid (pseudo-column alias) should fail
|
|
# SQLite: "no such column: oid", Limbo: "invalid expression in CREATE INDEX: oid"
|
|
do_execsql_test_in_memory_error create-index-on-rowid-alias-2 {
|
|
CREATE TABLE t8(x);
|
|
CREATE INDEX idx8 ON t8(oid);
|
|
} {oid}
|
|
|
|
# creating index on shadowed rowid column should succeed
|
|
do_execsql_test_on_specific_db {:memory:} create-index-on-shadowed-rowid {
|
|
CREATE TABLE t9(rowid int, x);
|
|
CREATE INDEX idx9 ON t9(rowid);
|
|
SELECT name FROM sqlite_schema WHERE type='index';
|
|
} {idx9}
|
|
|
|
# creating index on shadowed _rowid_ column should succeed
|
|
do_execsql_test_on_specific_db {:memory:} create-index-on-shadowed-rowid-alias-1 {
|
|
CREATE TABLE t10(_rowid_ int, x);
|
|
CREATE INDEX idx10 ON t10(_rowid_);
|
|
SELECT name FROM sqlite_schema WHERE type='index';
|
|
} {idx10}
|
|
|
|
# creating index on shadowed oid column should succeed
|
|
do_execsql_test_on_specific_db {:memory:} create-index-on-shadowed-rowid-alias-2 {
|
|
CREATE TABLE t11(oid int, x);
|
|
CREATE INDEX idx11 ON t11(oid);
|
|
SELECT name FROM sqlite_schema WHERE type='index';
|
|
} {idx11}
|
|
|