Description

When adding more than 499 unique entities within a single transaction, SQLite throws an error related to the compound SELECT limit (UNION depth limit).

Therefore its e.g. currently not possible to initially persist a larger file, if the plugin that manages its diffs, finds more than 500 entities

This appears to be caused by the way refresh-working-change-set dynamically builds a UNION ALL query for each unique entity.

Error

error.txt

Reproduction

Ive attached example code, using the @lix-js/plugin-json plugin to trigger the bug

index.js

I used
"@lix-js/plugin-json": "^1.0.1",
"@lix-js/sdk": "^0.5.1"

plugin-json was a arbitrary choice, i ran into the issue when creating my own plugin

Suspected Root Cause

I believe this issue stems from the way the refresh-working-change set creates the SQL query

	if (uniqueTargets.length > 0) {
		const literalTable = sql<{
			entity_id: string;
			schema_key: string;
			file_id: string;
		}>`
			(${sql.join(
				uniqueTargets.map(
					(target) =>
						sql`SELECT ${target.entity_id} AS entity_id, ${target.schema_key} AS schema_key, ${target.file_id} AS file_id`
				),
				sql` UNION ALL `
			)})
			target_entities
		`;