ECSQL with VALUES(...),(...) has a limit

[JonasDov]

The following ECSQL statement:

SELECT * FROM (VALUES(1), (2), (3))

Gets converted to:

SELECT [K0] FROM (SELECT 1 [K0] UNION ALL SELECT 2 UNION ALL SELECT 3)

This works fine when there are 500 or fewer statements. But with more than 500 statements, the following error is returned: SQLite statement failed to prepare: BE_SQLITE_ERROR too many terms in compound SELECT (BE_SQLITE_ERROR). For example passing the following ECSQL:

SELECT * FROM (VALUES(1), (2), (3), ..., (499), (500), (501))

Results in the error above.

This is because SELECT ... UNION ALL ... has a limit of 500 set, see: https://www.sqlite.org/limits.html#:~:text=Maximum%20Number%20Of%20Terms%20In%20A%20Compound%20SELECT%20Statement And VALUES(...),(...) should not have a limit, see: https://www.sqlite.org/lang_select.html#values:~:text=6.-,The%20VALUES%20clause,-The%20phrase%20%22VALUES