# Legend Cube — Known Issues

> **What this file is:** the known defects and gaps in Legend Cube, kept so later PRs can fix them. Each entry says
> what is wrong, where, and the suggested fix. [PROGRESS.md](PROGRESS.md) tracks status and [PLAN.md](PLAN.md) the
> plan.
>
> **Upkeep:** add an entry when a verification or a review finds something that is not fixed in the same change, and
> remove the entry in the PR that fixes it. Upstream defects (Studio and engine) are in PLAN.md Appendix B, not here.

## Bugs

### The node tooltip stays through a drag or a zoom

M3b.11's tooltip (MUI) opens 500 ms after the pointer rests on a node. A native drag of the node fires no mouse leave, so
a tooltip that opened stays during the drag and after the drop, until the pointer next moves off the node. A wheel zoom
with the tooltip open leaves it where it was, a few pixels over the grown node. Both clear on the next pointer move.
A fix controls the tooltip's `open` from react-dnd's `isDragging` and closes it on React Flow's `onMoveStart`.

### Entry links: the address keeps a history entry, a malformed one stops Legend Query, and search may miss the product

M3b.12's link (`?sourceType=dataProductAccessPoint&sourceId=…`) is taken out of the address with the navigator's
`updateCurrentLocation`, which pushes rather than replaces: Back returns to the link (the page, still mounted, doesn't
read it again; a reload there opens it again). A parameter with a malformed `%` stops Legend Query from loading before
Cube runs (the framework decodes the parameters as it loads). And the linked product is found with the catalog's
`search` for its id: a full-text search that ranks or cuts its answers could miss it, though the id is exact. Fixes:
a `replace` option on the navigator port; a lookup by id on the catalog port (`CubeDataProductCatalog`).

### Redacted printing shows a Pivot's values, as column names

Found in M6c.7's review. `printIR(…, {redactLiterals: true})` prints the values in `pivot(~c, [?], …)` as `?`, but the
`cast` and each `rename` name the value's columns by the values, and names are never redacted (PLAN §8.3), so the
values show there; and once a Pivot runs they are column names, which every later node's IR and `describeRedacted()`
show too. Nothing in the builder prints redacted yet. If it starts to, carry an origin on a name made from a value
and redact it, or document that a Pivot makes values into names.

## Engine issues to file

Upstream defects move to PLAN.md Appendix B once filed (M2.17). These wait for the user's go-ahead to post.

### SQL Server: `distinct()` then `limit()` renders `select top N distinct`

Found in M2's requirements (plan-only, not executed: no SQL Server in reach). On `SqlServer`, the engine writes a
relation `distinct()` followed by `limit(n)` as `select top n distinct …`
(`sqlServerExtension.pure:66` writes TOP before DISTINCT); T-SQL needs `select distinct top n …`. A Cube run hits it
whenever the engine keeps the DISTINCT in the same SELECT as a TOP: a Distinct at the node that runs (the run ends
with `limit(rowLimit + 1)`), or one followed by a Filter, a Sort or a Limit. A Rename or Restrict after the Distinct
moves it into a subquery, which SQL Server accepts. H2 accepts the order (the engine's own H2 test expects it), and
Sybase and Sybase IQ render `select distinct top` for the run's outermost limit (Sybase IQ gets a Limit inside the
query wrong another way: see below). Cube works around it (M2.13), padding every Distinct on SQL Server:
`->distinct()->extend(~cube_d: x | 1)->select(~[…])`, which plans `select top n … from (select distinct …, 1 as
"cube_d" …)`. Draft issue for finos/legend-engine:

```text
Title: SQL Server: distinct() followed by limit() generates SELECT TOP n DISTINCT, which T-SQL rejects

For `#>{db.S.T}#->distinct()->limit(10)` with a `SqlServer` connection, the plan's SQL is
`select top 10 distinct …`. SQL Server requires `select distinct top 10 …`. The TOP clause is written
before DISTINCT in `sqlServerExtension.pure` (line 66). Sybase and Sybase IQ already write
`select distinct top n`.
```

### `rewriteSliceAsWindowFunction` numbers rows by the first sort key only, and inside a `select distinct`

Found in M2's second verification (M2.16, plans only, engine `93d92b4`). For a `limit`, `drop` or `slice` inside a
query, Sybase IQ (`sybaseIQExtension.pure:265`), MemSQL (`memSQLExtension.pure:389`) and Spark
(`sparkSQLExtension.pure:260`) call `rewriteSliceAsWindowFunction` (`extensionDefaults.pure:39`), which numbers the
rows with `row_number() OVER (Order By <the first ORDER BY key only>)`: with ties on that key (ALFKI has six orders),
the plan doesn't decide which rows it keeps. The same rewrite copies the select with `distinct` kept
(`extensionDefaults.pure:67`), so `select distinct X, row_number() …` numbers every row and DISTINCT removes
nothing. Running the plans' SQL on H2 and SQLite gave the wrong rows ('Argentina' ×5 for five distinct countries).
The rewrite also names its numbering column `row_number` whatever the input has, so an input column of that name (any
case) gives two. Cube works around all three (M2.13, M2.16): row numbers of its own for Sybase IQ's Drop, Slice and
every Limit, and for MemSQL's Drop, and the padded Distinct on Sybase IQ. Spark SQL's extension calls the rewrite
too, but no connection can choose it (the protocol's `DatabaseType` has no SparkSQL); Databricks has its own
extension, which doesn't, and planned Cube's shapes right in the M2.16 plan test. Draft issue for
finos/legend-engine:

```text
Title: rewriteSliceAsWindowFunction orders by the first sort key only, and keeps DISTINCT

`#>{db.S.T}#->sort([~A->ascending(), ~B->descending()])->limit(5)->limit(1001)` on Sybase IQ plans
`row_number() OVER (Order By A asc)` for the inner limit, ignoring `B`, so ties on `A` take any rows.
MemSQL does the same for `drop()`. And `->distinct()->limit(5)->limit(1001)` on Sybase IQ plans
`select distinct X, row_number() OVER (…) …`, where the window makes every row distinct.

The rewrite (`extensionDefaults.pure`, lines 39 and 67) should order by every sort key and number the
rows of the distinct query, not within it. It also names the numbering column `row_number` whatever
the input's columns are (line 43).
```

### ClickHouse: a Drop after a descending sort key writes `nulls firstoffset m`

Found in M2's third check (M2.16, plans only). After a sort whose last key is descending, the engine's ClickHouse SQL
for a `drop(m)` joins the null ordering and the offset into one word, `… desc nulls firstoffset 10`
(`clickHouseExtension.pure:252` adds `offset` with no leading space), which ClickHouse can't parse (inferred from the
SQL; no ClickHouse server was run). Cube writes ClickHouse's Drop through row numbers. Draft issue for
finos/legend-engine:

```text
Title: ClickHouse: offset is written with no space after "nulls first"

`#>{db.S.T}#->sort(~A->descending())->drop(10)->limit(1001)` on ClickHouse plans
`order by … desc nulls firstoffset 10`. `clickHouseExtension.pure` line 252 should put a space
before `offset`.
```

### A duplicate column from `rename` or `select` fails with HTTP 500 and no source location

Found in M2's requirements (rename and select probes on `93d92b4`). `#>{db.S.T}#->rename(~ORDER_ID, ~CUSTOMER_ID)`
and `#>{db.S.T}#->select(~[ORDER_ID, ORDER_ID])` fail with `Compilation error at ??, "The relation contains
duplicates: [X]"` as an HTTP 500, not a 400 compilation error with a source location, so the error can't be placed on
a node. Cube's own validation refuses both before they reach the engine (Rename's collision check, Restrict's
duplicates), so Cube users don't see it. Draft issue for finos/legend-engine:

```text
Title: Duplicate columns from rename/select return HTTP 500 with no source information

`->rename(~A, ~B)` where `B` exists, or `->select(~[A, A])`, gives
`Compilation error at ??, "The relation contains duplicates: [B]"` as a 500. It should be a
compilation error (400) with the call's source information, like other typing errors.
```

### A GROUP BY written by the alias, which a column of the subquery can shadow

Found in M4's requirements and pinned in M4.8 (plans only, engine `93d92b4`). After `rename(~SHIP_COUNTRY, ~X)` and
`rename(~SHIP_CITY, ~SHIP_COUNTRY)`, a `groupBy(~[SHIP_COUNTRY], …)` selects `"orders_0"."SHIP_CITY" as "SHIP_COUNTRY"`
from a subquery that still has the table's `SHIP_COUNTRY` column. Ten database types are written `GROUP BY` the
expression or the position, which is right; nine (H2, Sybase IQ, MemSQL, Spanner, Redshift, Hive, BigQuery, ClickHouse,
Composite) are written `GROUP BY "SHIP_COUNTRY"`, which a database that looks the name up in the subquery first reads
as the country: MySQL-style MemSQL may then group by country with arbitrary cities, and Postgres-style Redshift or
Hive fail. H2 reads the alias and gives the 70 cities ✅; the others are inferred from their documented rules, never
run. `LegendCubeDialects.engine-roundtrip-test.ts` pins each database's form. Cube has no workaround: a `select` before
the group doesn't change the SQL (the engine folds it in); grouping by a temporary key would. Draft issue for
finos/legend-engine:

```text
Title: groupBy after a rename writes GROUP BY the alias, which a column of the subquery can shadow

`#>{db.S.ORDERS}#->rename(~SHIP_COUNTRY, ~X)->rename(~SHIP_CITY, ~SHIP_COUNTRY)
->groupBy(~[SHIP_COUNTRY], ~[n: x|$x.ORDER_ID : y|$y->count()])` selects
`"orders_0"."SHIP_CITY" as "SHIP_COUNTRY"` from a subquery that still has the table's
`SHIP_COUNTRY`, then writes `group by "SHIP_COUNTRY"` on H2, Sybase IQ, MemSQL, Spanner,
Redshift, Hive, BigQuery and ClickHouse. A database that resolves GROUP BY names against the
FROM clause first groups by the other column. SQL Server, DB2, Oracle and Trino write the
expression and Postgres and Snowflake the position, which are unambiguous; every dialect
could do the same.
```

### Distinct Value, Min and Max of a Boolean write `max()` over a bit or boolean

Found in M4's requirements (plans only); Min and Max in M6d's (`c4-boolean-column`, plans). Min and Max of a Boolean
column are written `min(b)` and `max(b)` on SQL Server, Sybase and Postgres, and `uniqueValueOnly()` is written
`case when count(distinct(x)) = 1 then max(x) else null end`; on a Boolean column that is `max()` over a `bit`
(SQL Server, Sybase), which they reject, or over a `boolean` (Postgres), which has no `max`. MemSQL and Oracle convert
the Boolean to text first. Cube offers Distinct Value on Boolean, as the spec does (PLAN §11.5), and Min and Max from
M6d (§11.14), so such a Group plans but would fail on those databases (inferred, not run). Draft issue for
finos/legend-engine:

```text
Title: min(), max() and uniqueValueOnly() over a Boolean write max() that SQL Server, Sybase and Postgres reject

`->groupBy(~[K], ~[u: x|$x.B : y|$y->uniqueValueOnly()])` with a Boolean `B` is written
`case when count(distinct("t_0".B)) = 1 then max("t_0".B) else null end`. SQL Server and
Sybase reject MAX over bit, and Postgres has no max(boolean). MemSQL and Oracle already
convert the Boolean to text before max; SQL Server, Sybase and Postgres could do the same,
or use bool_or / a cast to integer.
```

### SQL Server sums an `int` column as `int`

Found in M4's requirements (plans only). `sum()` over an `int` column is written `sum(x)`, which SQL Server types as
`int`: a sum past 2,147,483,647 fails with an arithmetic overflow. The engine and Cube type it `Integer` (PLAN §5.7),
whose range is a Java long. Inferred from SQL Server's typing rules, not run. Draft issue for finos/legend-engine:

```text
Title: SQL Server: sum() over an int column overflows past 2^31

`->groupBy(~[K], ~[s: x|$x.I : y|$y->sum()])` with an `int` column `I` is written
`sum("t_0".I)` on SQL Server, which returns int and fails with an arithmetic overflow once
the sum passes 2,147,483,647, though Pure types the result as Integer (a long). Writing
`sum(cast("t_0".I as bigint))` on SQL Server would match the Pure type.
```

### A window `count()` loses its OVER clause

Found in M5's requirements; run on H2 and planned on every database type again in M5.10 (engine `93d92b4`). In a
window, `count()` types as Integer but is written as a plain aggregate:
`->extend(over(~[SHIP_COUNTRY]), ~[c:{p,w,r|$r.SHIP_REGION}:y|$y->count()])`
plans `count("orders_0".SHIP_REGION) as "c"`, with no OVER, on all 20 database types ✅ (even Spanner, Presto and
Composite, which refuse every other window column). Run on H2, it fails: `Column "orders_1.ORDER_ID" must be in the
GROUP BY list` ✅. `size()` plans `count(x) over (…)`. Cube writes a window's Count and Count Rows with `size()`
(`PartitionEmitter.ts`), and `LegendCubeDialects.engine-roundtrip-test.ts` pins `count(…) over` on the 17 window
types, and no `count(` without OVER in any select but a Group's GROUP BY. Draft issue for finos/legend-engine:

```text
Title: count() in a window extend is written without its OVER clause

`#>{db.S.T}#->extend(over(~[P]), ~[c:{p,w,r|$r.X}:y|$y->count()])` compiles, typed Integer, but
plans `count("t_0".X) as "c"` with no `OVER (Partition By "t_0".P)` on every database type, so it
runs as an aggregate without a GROUP BY and fails (H2: "must be in the GROUP BY list").
`$y->size()` in the same place plans `count("t_0".X) OVER (…)`. count() should keep its window,
as size() does, or fail to compile.
```

### A filter after a single-form window extend runs before the window, or is dropped

Found in M5's requirements; run on H2 and planned on every database type again in M5.10 (engine `93d92b4`). A
filter after an `extend` whose window column is in the single form, `~c:…`, is folded into the window's SELECT:

- On an input column it becomes the window's WHERE, so it runs first:
  `->extend(over(~SHIP_COUNTRY), ~c:{p,w,r|$r.ORDER_ID}:y|$y->size())->filter(r|$r.ORDER_ID < 10252)->filter(r|$r.SHIP_COUNTRY == 'France')`
  counts France's orders as 2, not 77, on H2 ✅ (`CubeWindowIsolation.engine-roundtrip-test.ts` runs the single form
  unbound, 2, and bound by a let, 77; the unbound array form also gives 77 ✅, in a probe), and every one of the 17
  window types plans the WHERE in the window's SELECT ✅.
- On the window column (`->filter(x|$x.c > 100)`) it is written as QUALIFY on 4 types (H2, Snowflake, Databricks,
  DuckDB), refused on 6 (Postgres, MemSQL and Spanner: "QUALIFY grammar is not supported"; Redshift, Hive and
  Composite: "QUALIFY is not supported") and silently dropped, every row returned, on 9 (SQL Server, Sybase, Sybase IQ,
  DB2, Oracle, Trino, BigQuery, Athena, ClickHouse) ✅ (plans). Presto refuses any window.

The array form, `~[c:…]`, plans both filters after the window on all 17 ✅. Cube writes every window in the array form
and binds every Partition that isn't the capture with a `let` (PLAN §8.6, §11.6), whose WITH keeps the filter
outside; the plan-only test pins the WITH, the WHERE outside the window's select and no QUALIFY. Draft issue for
finos/legend-engine:

```text
Title: A filter after a single-form window extend runs before the window, or is dropped

`#>{db.S.T}#->extend(over(~P), ~c:{p,w,r|$r.X}:y|$y->size())->filter(x|$x.Y == 'v')` puts
`where Y = 'v'` in the same SELECT as `count(X) OVER (Partition By P)`, so the window counts only
the filtered rows (H2: 2 instead of 77). With `->filter(x|$x.c > 100)` the predicate becomes a
QUALIFY on H2, Snowflake, Databricks and DuckDB, an error ("QUALIFY ... is not supported") on
Postgres, MemSQL, Spanner, Redshift, Hive and Composite, and disappears from the SQL on SqlServer,
Sybase, SybaseIQ, DB2, Oracle, Trino, BigQuery, Athena and ClickHouse. The array form `~[c:…]`
plans a subselect and is right on all of them; the single form should be too.
```

### A rank with no ORDER BY plans, and fails only on the database

Found in M5's requirements; run on H2 and planned on every database type again in M5.10 (engine `93d92b4`).
`->extend(over(~[SHIP_COUNTRY]), ~[rk:{p,w,r|$p->rank($w,$r)}])` types (`rk` Integer) and plans
`rank() over (partition by …)`, with no ORDER BY, on all 17 window types ✅. Run on H2, it fails as an HTTP 500 with
no source location: `Syntax error in SQL statement "RANK() OVER (PARTITION BY … ORDER BY NULL[*])"; expected
"ORDER BY"` ✅. Other databases weren't run: SQL Server requires an ORDER BY for ranking functions, while Postgres
ranks every row 1 (💭, vendor documentation); `denseRank` and `rowNumber` with no sort weren't probed (💭). Cube
refuses a Rank, Dense Rank or Row Number with no sort before running
(`Aggregation function "<a>" requires at least one sort column.`). Draft issue for finos/legend-engine:

```text
Title: rank() over a window with no sort compiles and plans, then fails in the database

`#>{db.S.T}#->extend(over(~[P]), ~[rk:{p,w,r|$p->rank($w,$r)}])` compiles and plans
`rank() over (partition by P)` with no ORDER BY on every database type with windows. H2 then
fails with a syntax error ("expected ORDER BY") as an HTTP 500 with no source information. A
rank over a window with no sort keys should be a compilation error at the call.
```

### A windowed `count(distinct …)` plans on every database, though some refuse it

Found in M5's requirements; run on H2 and pinned in the plan-only test in M5.10 (engine `93d92b4`).
`->extend(over(~[SHIP_COUNTRY], [~ORDER_DATE->ascending()]), ~[dc:{p,w,r|$r.CUSTOMER_ID}:y|$y->distinct()->size()])`
plans `count(distinct(x)) over (…)`, and `uniqueValueOnly()` plans
`case when count(distinct(x)) over (…) = 1 then max(x) over (…) else null end`, sorted or not, on all 17 window types
✅; H2 runs them (France's running Distinct Count 1, 2, 3, and 10 over the whole partition ✅). At least Postgres, SQL
Server, Databricks and Trino (and so Athena, on Trino) reject DISTINCT in a window aggregate, and Oracle and BigQuery
with an ORDER BY in the window (💭,
vendor documentation; none was run here), so such a query plans and then fails in the database. Cube offers windowed Distinct Count and Distinct Value natively, with an editor note on where
they fail (PLAN §11.6, Q3), and the plan-only test pins each database's form (`WINDOWED_DISTINCT`). Draft issue for
finos/legend-engine:

```text
Title: distinct()->size() and uniqueValueOnly() in a window plan where DISTINCT in a window is rejected

`->extend(over(~[P], [~O->ascending()]), ~[d:{p,w,r|$r.X}:y|$y->distinct()->size()])` plans
`count(distinct(X)) OVER (Partition By P Order By O asc)` on every database type with windows,
and uniqueValueOnly() plans `case when count(distinct(X)) OVER (…) = 1 then max(X) OVER (…)`.
Postgres, SQL Server, Databricks and Trino reject DISTINCT in a window aggregate (Oracle and
BigQuery with an ORDER BY), so the error only comes from the database. Those dialects could
refuse it when planning, with a clear message, as they refuse QUALIFY.
```

### One nested subselect per window column, even inside one extend

Found in M5.5's review and planned again in M5.10 (engine `93d92b4`).
`->extend(over(~[SHIP_COUNTRY]), ~[a:{p,w,r|$r.EMPLOYEE_ID}:y|$y->sum(), b:{p,w,r|$r.ORDER_ID}:y|$y->max(), c:{p,w,r|$r.ORDER_ID}:y|$y->min()])->limit(1001)`
plans three nested selects, each computing one window column over the select inside it, on H2, Postgres and SQL
Server ✅. With nothing after the extend it is one select with three OVER clauses ✅; a filter after it, or a `let`
that binds it, nests it the same way ✅. The rows are right, but a Partition of N functions is N levels deep, each
selecting every column again, and a Partition of a Partition adds its own. Cube has no workaround: a run always ends
with a limit, so every Partition nests. `LegendCubeDialects.engine-roundtrip-test.ts` pins one select with an OVER per
window column on H2. Draft issue for finos/legend-engine:

```text
Title: A window extend of several columns plans one nested subselect per column

`#>{db.S.T}#->extend(over(~[P]), ~[a:{p,w,r|$r.X}:y|$y->sum(), b:{p,w,r|$r.X}:y|$y->max(),
c:{p,w,r|$r.X}:y|$y->min()])->limit(10)` plans three nested selects with one OVER each, though
the three columns share one window and none reads another. Without the limit it is one select
with three OVER clauses. The columns of one extend could stay in one select whatever follows it.
```

### ClickHouse: First and Last skip NULLs, and Lag and Lead fill a non-Nullable column's edges

Found in M5b.6's review (PLAN §11.9); planned, not run (no ClickHouse here): the SQL is pinned in
`LegendCubeDialects.engine-roundtrip-test.ts`, and its meaning is ClickHouse's documentation 💭. On ClickHouse the
engine writes First as `first_value(x) over (…)` with no `RESPECT NULLS`, and ClickHouse's `first_value` skips NULLs:
with a NULL in the partition's first rows, First varies from row to row instead of being the first row's value, and
Last (First over the reversed sort) gives the last non-null value. It writes Lag and Lead as
`lagInFrame(x, n) over (… rows between unbounded preceding and unbounded following)` with no default, and
`lagInFrame` then gives the column type's default (0, '', 1970-01-01) at the partition's edges for a column that
isn't `Nullable(T)`, ClickHouse's default, instead of empty. Both are wrong rows with no error. Cube can't write
either correction through Pure. Draft issue for finos/legend-engine:

```text
Title: ClickHouse first_value/last_value skip NULLs, and lagInFrame/leadInFrame fill edges with the type's default

`->extend(over(~[P], [~O->ascending()]), ~[f:{p,w,r|$p->first($w,$r).X}, l:{p,w,r|$p->lag($r, 1).X}])`
plans `first_value(X) OVER (…)` and `lagInFrame(X, 1) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)` on ClickHouse. ClickHouse's first_value/last_value skip NULLs unless
`RESPECT NULLS` is given, so a NULL in the partition changes first()/last(); and lagInFrame/leadInFrame
return the column type's default outside the frame for a non-Nullable column, where lag()/lead() should
give empty. The ClickHouse dialect could write `first_value(X) RESPECT NULLS` and
`lagInFrame(toNullable(X), n)`.
```

### DuckDB: `%` and `_` in `startsWith`, `endsWith` and `contains` match anything

Found in M6b's probes (PLAN §11.11), run on DuckDB through the engine ✅ (`filter-expr-requirements/p3-values.out`
F9, F10 in the evidence folder). The engine escapes `%` and `_` in these functions' LIKE patterns with `\`
(`escapeLikeExprDefault`, Appendix B) and writes no `ESCAPE` clause. H2 reads `\` as LIKE's escape character; DuckDB
has none by default, so `contains('%')` becomes `like '%\%%'`, a backslash followed by anything: the row `a\b`
matched and the row `a%b` didn't. Wrong rows with no error, on a DuckDB direct connection, for the column Filter's
StartsWith, EndsWith and Contains (and their negations) as for a Filter by Expression: the column Filter's Contains
`%` and `_` were run on a DuckDB direct connection (M6b) and each returned the backslash's row; H2 returned the right
ones. Cube can't write an `ESCAPE` clause through Pure, so it refuses `%` and `_` in these values for DuckDB cubes,
in both filters, as it refuses `\` everywhere (PLAN §8.2, after M6b.9), until the engine is fixed. The plan-only test
`Patterns given % or _, as H2 and DuckDB plan them` (`LegendCubeDialects.engine-roundtrip-test.ts`) pins the missing
`ESCAPE` clause: it fails once the engine writes one, and the refusal can go. Draft issue for finos/legend-engine:

```text
Title: LIKE patterns from startsWith/endsWith/contains need an ESCAPE clause (DuckDB matches wrongly)

`->filter(x|$x.NAME->toOne()->contains('%'))` plans `"t_0".name like '%\%%'` on DuckDB. The pattern escapes
`%` and `_` with `\`, but DuckDB has no default LIKE escape character, so `\%` matches a backslash followed
by anything. Writing `like '%\%%' escape '\'` (and escaping `\` itself, which H2 also needs) would fix both
dialects.
```

### Trino and Athena: `pivot()` plans with no PIVOT

Found in M6c's probes (PLAN §11.12), planned through the engine ✅ (`pivot-requirements/p3-static.out` A in the
evidence folder). A static `pivot(~SHIP_VIA, [1, 2, 3], …)` with its `cast` plans on Trino and Athena, but the SQL has
no `PIVOT`: the outer select reads `"'1__|__f'"` from a subquery that only has `SHIP_COUNTRY`, `SHIP_VIA` and
`FREIGHT`, so the database would refuse it. The other 13 types without pivot assert "pivot is not supported"; these
two should too, or write the pivot. Cube refuses a Pivot on both (PLAN §11.12). Draft issue for finos/legend-engine:

```text
Title: pivot() on Trino and Athena plans SQL without the PIVOT

`->pivot(~SHIP_VIA, [1, 2, 3], ~f: x|$x.FREIGHT : y|$y->plus())->cast(@Relation<(…)>)` plans on Trino and Athena,
but the SQL selects the pivoted columns ("'1__|__f'", …) from a subquery that never pivots. Other dialects without
pivot support fail with "pivot is not supported"; Trino and Athena should fail the same way, or write the pivot.
```

### `startsWith`, `endsWith` and `contains` given a column write the column's SQL as the pattern's text

Found in M6d's requirements ✅ (`cleanup-requirements/c1-patterns`, `c2-plan-patterns` in the evidence folder). Given a
pattern that isn't a literal, `$x.CITY->contains($x.REGION->toOne())` is written `like '%"orders\_0".SHIP\_REGIO%'` on
18 types: the column's SQL, cut short, as the pattern's text, so DuckDB returned no rows. H2 writes
`like concat('%%', b, '%%')`, matching the column's `%` and `_` as wildcards, and fails `startsWith` and `endsWith`.
Only Snowflake writes it right (`contains(a, b)`). Texts joined with `+` fail on H2 and DuckDB. Cube refuses a
non-literal pattern unless the database is Snowflake (PLAN §11.14). Draft issue for finos/legend-engine:

```text
Title: startsWith/endsWith/contains with a non-literal pattern write the pattern's SQL as text

$x.A->contains($x.B->toOne()) plans as `a like '%"t_0".B%'` (the column's SQL, escaped and cut, inside the literal)
on Postgres, SQL Server, DuckDB, Databricks, MemSQL and 13 more; H2 writes like concat('%%', b, '%%'), so B's
own % and _ act as wildcards, and fails startsWith/endsWith. Snowflake writes contains(a, b). A non-literal pattern
should be written as an expression with its wildcards escaped (or with strpos/position), or refused.
```

### Databricks and MemSQL: a backtick inside a name ends it early

Found in M6c.7's review, planned on the local engine ✅. `processIdentifierWithQuoteChar` (`extensionDefaults.pure:677`)
wraps a name in the dialect's quote character and strips only `"`. Databricks and MemSQL quote with backticks, so a
name holding one ends early and the SQL fails to parse: for one, a static pivot's own column name, which holds the
value's text, for a value with a backtick. Cube refuses a backtick in every name it gives a column, a pivot's values
included (PLAN §11.14). Draft issue for finos/legend-engine:

```text
Title: Identifiers holding the dialect's quote character aren't escaped (Databricks, MemSQL)

processIdentifierWithQuoteChar (extensionDefaults.pure) wraps an identifier in the quote character and strips only
'"'. On Databricks and MemSQL, which quote with backticks, a column named q`t (from rename, extend or a static
pivot's value) gives `q`t`, which ends the identifier early. The quote character inside should be doubled.
```

### Schema exploration returns JSON and VARIANT columns as `Other` or text

Found in the Variant analysis (PLAN §11.20), run on the local engine 🧪 (`variant/probe-cube-path.out.txt` in the
evidence folder). `SchemaExportation.java` maps only the standard JDBC types, so a DuckDB `JSON` column is explored as
`Other`, which Cube leaves out of a direct connection's table: the column isn't there at all. 💭 A Snowflake `VARIANT`
and a Postgres `jsonb` likely come back as `Other` or a string too. Cube offers Parse as JSON on a String column
meanwhile. Draft issue for finos/legend-engine:

```text
Title: Schema exploration doesn't map JSON/VARIANT columns to SemiStructured

The database builder's schema exploration (SchemaExportation) maps java.sql.Types only: a DuckDB JSON column is
explored as Other (and a Snowflake VARIANT, Postgres json/jsonb likely as Other or Varchar). Explored databases then
lose the column's semi-structured type, so get()/to()/flatten can't be used on it. Mapping these by TYPE_NAME
(JSON, VARIANT, OBJECT, ARRAY, json, jsonb) to SemiStructured would let them through.
```

### `toMany(@String)->contains(v)` on a Variant array writes invalid SQL on DuckDB

Found in the Variant analysis, run on the local engine 🧪 (`variant/probe-engine.out.txt`).
`$x.payload->get('tags')->toMany(@String)->contains('b')` is written
`array_contains(((payload->'tags')->>'$'), 'b')`: the array read as one text, which DuckDB refuses. The same through
`toMany(@Variant)->map(t | $t->to(@String))->contains('b')` is written over the array and returns the right rows; Cube
writes that form. Draft issue for finos/legend-engine:

```text
Title: toMany(@String)->contains() on a variant array writes array_contains over the array's text (DuckDB)

$x.v->get('tags')->toMany(@String)->contains('b') plans as array_contains(((v->'tags')->>'$'), 'b') on DuckDB, which
fails: "No function matches array_contains(VARCHAR, STRING_LITERAL)". toMany(@Variant)->map(t|$t->to(@String))
->contains('b') plans as array_contains(apply(cast(v->'tags' as JSON[]), ...), 'b') and works.
```

### `exists` over a Variant array fails while planning

Found in the Variant analysis, run on the local engine 🧪.
`$x.payload->get('items')->toMany(@Variant)->exists(i | $i->get('sku')->to(@String) == 'z')` fails with "Cannot cast a
collection of size 0 to multiplicity [1]" (`pureToSQLQuery.pure` line 6030), where `filter(…)->size() > 0` works. Cube
doesn't offer `exists`. Draft issue for finos/legend-engine:

```text
Title: exists() over toMany(@Variant) fails in plan generation

|#>{db.s.t}#->filter(x|$x.payload->get('items')->toMany(@Variant)->exists(i|$i->get('sku')->to(@String) == 'z'))
fails on DuckDB with "Cannot cast a collection of size 0 to multiplicity [1]" (pureToSQLQuery.pure:6030). The same
condition through filter(...)->size() > 0 plans and runs.
```

### `lateral(…flatten(~c))` types the element `[1]`, though a row with an empty array gets none

Found building Explode Array (V.6), run on the local engine 🧪 (`CubeVariant.engine-roundtrip-test.ts`). On DuckDB, a row
whose array is empty, or whose document is null, comes back once with the element null, as every dialect's SQL writes
it (Snowflake `outer => true`, Databricks `explode_outer`, Postgres `left join lateral`, DuckDB `[NULL]` 📄), yet
`lambdaRelationType` types the element `Variant[1]`. Cube says nullable. Draft issue for finos/legend-engine:

```text
Title: The column flatten() adds inside lateral() is typed [1], but is empty for an empty or missing array

|#>{db.s.t}#->lateral(x|$x.payload->get('items')->toMany(@Variant)->flatten(~item)) types item as Variant[1]. Every
dialect keeps a row whose array is empty or null (outer flatten, explode_outer, left join lateral, unnest of [NULL]),
with item NULL, so the column should be [0..1].
```

### On Postgres, `flatten` reads each element as text, then casts it to `jsonb`

Read from the plan only 📄 (`LegendCubeDialects.engine-roundtrip-test.ts`, Explode Array): Postgres writes the lateral as
`select "value" #>> '{}' as value from jsonb_array_elements(…)` and the element as `cast(value as jsonb)`. `#>> '{}'`
gives a string element without its quotes, so an array of strings (`["a","b"]`) would cast `a` to `jsonb`, which isn't
JSON: likely an error. Not run, as no Postgres is set up here. Draft issue for finos/legend-engine, once confirmed:

```text
Title: Postgres: flatten() of a string array casts the unquoted text to jsonb

|#>{db.s.t}#->lateral(x|$x.payload->get('tags')->toMany(@Variant)->flatten(~tag)) plans on Postgres as
left join lateral (select "value" #>> '{}' as value from jsonb_array_elements(...)) ... cast(value as jsonb). For
tags = ["a"], #>> '{}' yields a (no quotes), and cast('a' as jsonb) is invalid JSON. Selecting "value" itself, a jsonb,
would keep each element as JSON.
```

## Direct connections, data products and ingest data sets

### The engine's schema exploration mistypes some columns

On H2 and DuckDB, `DECIMAL(10,2)` comes back as `Numeric(0,0)`; on DuckDB, `VARCHAR(5)` comes back as `Varchar(0)`; on
H2, `REAL` comes back as `Other`, so Cube leaves the column out of a direct connection's table (M6d.4's engine test). Cube keeps the
engine's types (precision isn't enforced in Cube), and no test asserts them. An engine issue to file.

### The source dialog's lists show no hover, and hide the picked row under the pointer

Found while recording the sources demo (2026-10-09), on Legend Query's light theme. The lists of the "Add a source"
dialog's tabs (tables in the Model and Database connection tabs, products and access points in the Data product tab)
paint a hovered row with `--color-bg-hover`, which is the dialog body's own grey (`#edf0f1`), so hovering shows
nothing. The hover style also wins over the picked row's `--color-bg-selected` (`#def3ff`), so the row just clicked
looks unpicked until the pointer leaves it. Suggested fix: apply the hover background only to rows that aren't picked,
and give hover a color that differs from the dialog body. Low; no behavior is wrong.

### Engine errors can echo setup SQL

A failed exploration's message may quote the connection's setup SQL. Cube shows the first line and the rest on demand,
in the tab only; it never logs it.

### Data products can't run on the open-source engine

The open-source engine rejects every data product construct (`#P`, a LakehouseRuntime with an environment and a
warehouse), so data product runs are tested with fakes and, on the engine, with stand-in functions only. The first
real run is in an internal deployment (Part B2).

### Re-checking saved data product sources

An import and Refresh read a data product cube's access points again from the deployed artifact at the cube's saved
version. An import uses the artifact this page visit already read, and Refresh reads it again, so a SNAPSHOT version
redeployed meanwhile shows on Refresh. A redeployed product never moves a cube to another version.

### Listing data products

- **The lite list:** Cube pages the lakehouse's lite list itself and stops on a page that isn't one, a missing or
  repeated cursor, or past 50 pages. The lakehouse client takes no abort signal, so closing the dialog stops further
  pages but not the page already asked for.
- **Marketplace search** (when Query's `marketplace.serverUrl` is set) reads one page of 100 matches. On a cube whose
  data products are fixed to one project, a typed search keeps that page's rows from the project, so it can show few of
  them; the cube's own products show when the search is empty.

### Ingest data sets can't be read or run on the open-source engine

The open-source engine can't parse an ingest definition's grammar and can't type or run `#I{…}#`, so ingest data sets
are tested with a fake catalog and, on the engine, with stand-in functions only (testing.md). The ingest servers'
answers, the parse of a real definition, the environment name and the warehouse are first checked in an internal
deployment (PLAN §11.2 Part B2).

### Ingest definitions: what Cube leaves out for now

- **No project version.** A definition is read from the ingest server by URN, through its grammar and the engine's
  parse, as Data Cube does. Cube doesn't know the version it was deployed from; reading it from Depot at that version
  is the TODO (PLAN §6.7).
- **Only SDLC-deployed definitions** (`alloy-git` URNs) are listed; ad hoc (`rest-api`) ones, and producers' own
  user-id environments, are left out. The tab counts the definitions it doesn't show.
- **Materialized views** (a data set whose source is a function) are listed disabled.
- **A data set re-checked on import** keeps its saved columns, with a warning, when its definition can't be read again
  (e.g. no longer deployed).

## Depot databases

- **Scale is unmeasured.** The project list isn't paged and has no search, and a version's first typing call compiles
  the project with its dependencies. Neither was measured at a real depot's size.
- **An outline downloads the version's whole model twice,** with and without its dependencies, in the browser. On a
  project with a large dependency closure the Project tab could be slow: the first thing to watch when testing
  deployed. The follow-up is the requirements' smaller classifier GETs (Databases and runtimes only).
- **The mock copies Studio's depot client.** The real depot's answers for versions (`snapshots=false`), `latest` and
  its wrappers weren't checked in a deployment.
- **No CI test sends a pointer:** CI runs no depot. The `cube-local` group does, by hand.
- **The engine caches a pointer's project per version,** so a sample project changed under the same version needs an
  engine restart (local only: a published release doesn't change).

## The results grid (M7)

- **A level is cut at the row limit.** Each level is fetched whole, up to the run's row limit (PLAN §11.10, the
  user's choice over paging); a level with more groups or rows says so above the grid, and the user raises the limit
  or adds a Filter.
- **Excel holds the rows shown, unformatted.** 'Excel (rows shown)' is ag-grid's export of the loaded rows: collapsed
  groups' rows aren't in it, and column formats aren't carried into the workbook (ag-grid's Excel styles would). Number
  columns are numbers there (an Excel style with `dataType: 'Number'`), as doubles, so a value past a double's
  precision is rounded in the workbook.
- **Local mode's totals are JS numbers.** Its Sum, Average, Min and Max read the exact text as doubles, so a total past
  a double's precision is rounded; server-side mode's are the database's.
- **Two float groups can merge.** A float key is matched by `In [v, fround(v)]`; on a 64-bit column that holds both a
  value and its 32-bit neighbour, expanding either shows both groups' rows. The engine returns each with its own text,
  so it would take data stored at 32 bits and widened.
- **No pivot server-side**, as in the spec (§12.3); local mode has ag-grid's.

## UX backlog (demo review, 2026-10-13)

From a frame-by-frame review of the full walkthrough (`videos/20-walkthrough-v3.webm` in the evidence folder). The
user picked the items marked **this pass** for the `cube-ux-pass` branch (PLAN §11.15); the rest are kept to weigh.

1. **Group-row averages show every digit** (`264.61327566137567`): round the display to the column's scale, or 2
   places. _This pass._
2. **Aggregated integers have no thousands separators** on group rows (`37171394`): group them, never the group key.
   _This pass._
3. **Unfinished steps look broken:** "Apply 0 Window Functions", "Aggregate all rows" with nothing set, "Extend
   with", "Filter by (blank)", "Join additional input". Show a muted "not set". _This pass._
4. **The Join's tooltip repeats its label** ("Join additional input") under the problem. _This pass._
5. **Palette labels keep the internal app's placeholders** ("Drop first `<x>` rows"). _This pass._
6. **Types in engine notation** (`Varchar(20)?`, `Numeric(18,2)`): show Text / Number / Date, "can be empty", the
   exact type on hover. _This pass._
7. **Node editors open scrolled with their top heading clipped, and cut their help notes off**; the column picker
   repeats the type beside the column.
8. **Every edit needs an F9:** an "auto-run after Apply" toggle for queries under the row limit.
9. **Expressions need `x | $x.` typed and a Validate:** column completion after `$x.`, validating on a pause.
10. **Choosing the step that runs is hidden** (right-click → Select): a visible "Run this step", a clearer marker on
    the running step. _This pass._
11. **Pivot works on no sample** (all H2): a DuckDB sample with a Pivot example. _This pass._
12. **No Redo, and a crowded header:** Redo (Cmd/Ctrl+Shift+Z), New, Import and Export under one menu. _This pass._
13. **Functions have two ways in** (the palette's Function, and Sample Data's Tables / Functions): keep one.

New features suggested by the review:

- **Column preview for tables**, as access points have. _This pass._
- **Copy, paste and duplicate steps.** _This pass._
- **Delete-key removal of a step.** _This pass._
- **Row counts on the nodes** after a run.
- **Search in the palette.**
- **Notes on steps**, saved with the cube once saving lands.

## Legend Cube in Studio (2026-10-14)

- **Runs in Studio are untested.** The local engine has no Lakehouse support, so a Studio cube's runs, the typing of a
  data product's access points, the ingest editor's grammar text, and Lineage and View SQL on a new materialized
  view were never seen working. The catalogs, the source dialog's screens, the canvas and Add MatView were (videos
  `25-studio-matview.webm` and `26-studio-matview-light.webm`, with a mock SDLC server and, for the access points'
  types only, a stand-in for the engine: `demo/studio-matview/` in the evidence folder). Check on an engine with
  Lakehouse support: a cube of one data set, or one access point, should return the rows Studio's own query of it
  does.
- **Does a workspace data product run?** Studio's own query of a data product sends the workspace as the model, as
  Cube now does, but whether the lakehouse resolves `#P{…}#` for a product that was never deployed is the engine's to
  say. If it can't, a data product cube in Studio types but doesn't run.
- **The run's runtime is rebuilt from the project's.** A run takes only the environment and the warehouse of the
  project's first lakehouse runtime; anything else on it (a connection pointer) is left out, and a project with two
  lakehouse runtimes can't pick the other.
- **A materialized view's windows aren't isolated.** The lambda Add MatView saves is the typing lambda's shape: it
  binds no window with a `let` as a run does (§8.6), so a filter over a window, written by the lakehouse, may read
  differently from the cube's run.
- **The workspace is sent whole.** Each typing and run sends every element of the workspace and its dependencies, as
  Studio's own queries do; a large project makes larger requests.
- **Secondary buttons in a light theme** (Cancel, Close) keep legend-art's light look, grey: its theme-aware variant
  would draw their text on a background of the same colour.

## Import Pure (2026-10-14, PLAN §11.18)

- **Per-database rewrites aren't read back.** A lambda Cube wrote for SQL Server, Sybase, Sybase IQ, DB2, ClickHouse
  or SingleStore (row numbers for a Drop, Slice or Limit, a padded Distinct) reads as other steps or is refused.
  Snowflake and Databricks have no rewrites, so Studio's matviews read back.
- **Not read:** services (Cube doesn't emit them yet); a `select` that reorders columns outside a Join or a
  Partition (no step does); a `distinct(~[…])` of some columns; a lambda with parameters; lets that bind something
  other than a step.
- **Query's sources from text alone:** in Query, a table binds on the cube's own model (a new cube asks for a table
  first), an ingest data set only when the cube's class and producer deployment are known (or the host has one of
  each), and a data product by its path through the catalog's search, which may not find it by path.
- **Rewritten, not refused:** a lambda Cube reads but writes another way (a sort before a filter, a hand-written
  join without Cube's select) is imported, with a banner; Save MatView then writes Cube's Pure, which reads the same
  rows. A plain negation on a column whose nullability isn't known is kept as a Filter by Expression.
- **Untested on a Lakehouse engine:** the local engine has no `#I{}#` or `#P{}#` grammar, so pasting such Pure as
  text, and typing an Extend over an ingest data set during the read, weren't seen working (the JSON of a matview
  was, in Studio, with the mock SDLC: `demo/studio-matview/probe-edit-matview.mjs`).

## Test gaps

None hides a known bug.

- **The grid tests were not independently verified** (demo-cut test run `m18-democut-tests`, 2026-10-07). The grid
  group's verifier hit the session limit, so `CubeGridRegion.test.tsx` was checked only by its writer, against the
  writer's own mutants.
- **Part A's extras** (M1.9 requirements, kept for later by the user on 2026-10-08). A.4's FULL join on one nullable
  key checks Cube's merged-key nullability but not against the engine's answer: a check must allow Cube ⊇ engine and
  never pin the engine's `[1]`, a known engine defect (PLAN Appendix B). A.7's negatives run on synthetic schemas in
  the core tests, not on the resolved Northwind tables (`ORDER_DETAILS ⋈ PRODUCTS`, `EMPLOYEE_ID Equal 100000`).

## Risks

- **React Flow's two stylesheets.** The Cube canvas (xyflow 12) imports its CSS with the lazy Cube page, so Query's
  other pages don't load it (checked in the M1.8b dry run). Once the Cube page has been opened, it stays loaded for
  the session, and the query builder's lineage viewer (reactflow 11) shares its `.react-flow__*` class names. The
  lineage viewer wasn't seen after a visit to `/cube` (it needs a depot query). Check it when one is reachable. Studio
  loads both from the start: React Flow 11's fixed control and minimap colours won over xyflow 12's on the Cube canvas
  (white icons on white), which now takes them back (`legend-cube-builder/style/index.scss`, 2026-10-14).
- **Canvas fitting is checked by hand only.** jsdom measures nothing, so React Flow never reports the nodes measured
  and the refit after a layout or size change (`CubeCanvas.tsx`) has no jsdom test. Part B checks it by hand. The M1.9
  rehearsal found that a height-only change (the splitter above the grid) didn't refit, and that is fixed.
- **Only Chrome is checked.** The dry run, the demo and the M1.9 acceptance use Chrome, and `03e095655` fixed a
  Chrome-only behaviour of the date input. Firefox and Safari are untested, value entry (Part B step 5) and the spec
  file import (step 8) above all (user, 2026-10-08).
- **A page laid out at zero width stays empty.** Found in M2.4's browser check: when the Cube page first renders in a
  container with no width (the app's browser pane opening), react-reflex warns "Found ReflexContainer with width=0" and
  the graph and grid region keeps zero width, even after the window grows, until the page reloads. Not seen in a
  normal browser tab. Suggested check: whether `CubeEditor`'s resizable layout should re-measure on a resize, or
  render only once its container has a size.
- **Long column names on Postgres (💭, not probed).** A Rename (M2.9) accepts new names of up to 128 code points
  (PLAN §11.4), but Postgres cuts identifiers at 63 bytes, so two long names could collide or be cut once a Postgres
  runtime is in use. Suggested check: plan a rename to a name of 64 bytes or more on Postgres, and lower the cap per
  database if needed. Group's auto-names, `<column> <function>`, reach the limit with no name typed (M4.15, plans
  only): from a 53-byte column, Distinct Count and Distinct Value share their first 63 bytes; from 61 bytes, Min and
  Max do too; from 62 bytes, every aggregation of the column. Cube accepts both names and the engine passes them on,
  so Postgres would reject a Sort on either after the Group ("ORDER BY … is ambiguous") and a Concat's outer select
  ("column reference is ambiguous"); a Filter, Limit or select after the Group is unaffected. A per-database cap on
  Rename's names wouldn't cover this: a guard has to check every derived output name, a Group's included, and only
  when the runtime's database is Postgres (the names run on H2).