Repository navigation
INSERT INTO t SELECT gives empty rows when no selected column name is a column of the table #2760
Description
INSERT INTO t SELECT ... gives empty rows {} when none of the selected column names is a column of the table (or of the column list). SQL matches columns by position, so SQLite fills the rows. Seen in the sqllogictest run (evidence/in1.test, where it fails with "Cannot insert record, because it already exists in primary key index"), reproduced on develop (06211a0):
alasql('CREATE TABLE s (a INT, b INT)');
alasql('INSERT INTO s VALUES (1, 2), (3, 4)');
alasql('CREATE TABLE e (x INT, y INT)');
alasql('INSERT INTO e SELECT * FROM s'); // 2, rows [{}, {}]; SQLite: (1, 2), (3, 4)
alasql('CREATE TABLE f (x INT, y INT)');
alasql('INSERT INTO f (x, y) SELECT a, b FROM s'); // 2, rows [{}, {}]; SQLite: (1, 2), (3, 4)
alasql('CREATE TABLE g (a INT, b INT)');
alasql('INSERT INTO g SELECT a + 0, b + 0 FROM s'); // 2, rows [{}, {}]; SQLite: (1, 2), (3, 4)
alasql('CREATE TABLE t4 (a INTEGER UNIQUE)');
alasql('CREATE TABLE t5 (b INTEGER PRIMARY KEY)');
alasql('INSERT INTO t4 VALUES (2), (3), (4)');
alasql('INSERT INTO t5 SELECT * FROM t4'); // Error: ... already exists in primary key index
// SQLite: t5 holds 2, 3, 4Fix (4.x)
When no selected column name is a column of the target, match by position. Nobody can rely on empty rows, so this is not breaking.
Everything else keeps matching by name, because people rely on it:
alasql('CREATE TABLE d (b INT, a INT)');
alasql('INSERT INTO d SELECT * FROM s'); // [{b: 2, a: 1}, ...]: same names, other order, by name
alasql('CREATE TABLE h (a INT, z INT)');
alasql('INSERT INTO h SELECT a, b FROM s'); // [{a: 1}, ...]: one name matches, kept as todayThe test must show both: the empty-row cases filled by position, and the by-name cases unchanged.
Later
Matching by position in every case is for the sqlite dialect: #2773. The alasql dialect keeps matching by name.
Activity
[-]INSERT INTO t SELECT matches columns by name, not by position as in SQL[/-][+]INSERT INTO t SELECT gives empty rows when no selected column name is a column of the table[/+]