VIEW defined with union all returns only the first row

[aarashrestha]

VIEW defined with union all returns only the first row

Affected versions: confirmed in 2.9.0 and 3.1.1 (latest as of 2026-05-22)

Summary

A view defined as select … union all select … union all select … returns
only the first leg of the union when queried. The same union all
chain evaluated inline (not wrapped in a view) returns every row.

Reproduction

Control — inline union all returns 3 rows:

select 'r' as Code union all
select 'g' as Code union all
select 'b' as Code;
-- result: 'r', 'g', 'b'

Bug — same chain inside a VIEW returns 1 row:

declare schema main

{
    view V as
        select 'r' as Code, 'Red' as Name
        union all select 'g' as Code, 'Green' as Name
        union all select 'b' as Code, 'Blue' as Name;
}

apply schema main;

select Code from V;

Expected: 3 rows: r, g, b.

Actual: 1 row: r.

Minimal Node.js reproduction

import { Database } from '@quereus/quereus';

const db = new Database();
await db.exec(`
    declare schema main
    { view V as
        select 'r' as Code, 'Red' as Name
        union all select 'g' as Code, 'Green' as Name
        union all select 'b' as Code, 'Blue' as Name;
    }
    apply schema main;
`);

let n = 0;
for await (const row of db.eval(`select Code from V`)) n++;
console.log(n); // expected: 3, actual: 1

Downstream impact

CHECK constraints that reference such views appear to "work" only when the
inserted value happens to match the first row of the view. Both forms

constraint X check (Color in (select Code from V))
constraint Y check (exists (select 1 from V where V.Code = Color))

reject every value beyond the first view row. Schema-driven enum
validation against views is effectively unusable in 2.9.0 / 3.1.1.

Test reference

stage-7-in-subquery.spec.ts
sub-tests A1, A2, A3