Repository navigation
MATRIX repeats or drops a value when two result columns have the same name: SELECT 0 AS c, 27 AS c gives [27, 27] #2765
Description
When two result columns have the same name, MATRIX (rows as arrays) repeats the last value or drops a column. With arrays there is no name to collide, so both values belong in the row. Seen in the sqllogictest run (SELECT 0 AS col2, 27 AS col2), reproduced on develop (06211a0), compared with SQLite 3.43.2 (.values()):
| Query | AlaSQL | SQLite |
|---|---|---|
MATRIX OF SELECT 0 AS c, 27 AS c |
[[27, 27]] |
[[0, 27]] |
MATRIX OF SELECT COUNT(*) AS c, 43 AS c FROM t |
[[43]] |
[[2, 43]] |
SELECT 0 AS c, 27 AS c (objects) |
[{c: 27}] |
[{c: 27}] (bun:sqlite objects too) |
RECORDSET OF SELECT 0 AS c, 27 AS c |
columns c, c; data [{c: 27}] |
With rows as objects, one key per name is the nature of the format, and SQLite's object form does the same. The array forms are built from the object row, so the first value is lost.
Not breaking for the array forms: today's answer is wrong. Related: #2636 (RECORDSET lists a shared column twice), #2726 (result columns decided in several places).
Activity
mathiasrw commentedon Oct 21, 2026
The same happens with
SELECT *over a join of two tables that share column names, the most common form in the sqllogictest run (test/random/aggregates/,SELECT * FROM tab0, tab1 AS cor0 ...). Tablesa(c0, c1)= (1, 10), (2, 20) andb(c0, c1)= (3, 30), (4, 40), (5, 50);develop06211a0, SQLite 3.43.2:MATRIX OF SELECT * FROM a CROSS JOIN b[[3, 30, 3, 30], [4, 40, 4, 40], ...](6 rows)[[1, 10, 3, 30], [1, 10, 4, 40], ...]SELECT * FROM a CROSS JOIN b(objects)[{c0: 3, c1: 30}, ...], 6 rowsSELECT DISTINCT * FROM a CROSS JOIN bSELECT COUNT(*) FROM (SELECT DISTINCT * FROM a CROSS JOIN b)For the array forms it is this issue.
DISTINCT *counting the merged object rows is listed as a possible breaking change: rows as objects merge the shared names by design, andDISTINCTworks on what the object keeps. Related: #2636.