Change SQLite backend to generate INSERT statements using VALUES instead of UNION.

[Keryn Knight]

This is a ticket for tracking this ​pre-emptive PR.

Django's minimum supported version of SQLite is currently documented as 3.9.

Using INSERT INTO ... VALUES (...) instead of INSERT INTO ... SELECT UNION ALL ... for multiple rows (i.e. bulk_create) was added to SQLite in ​3.7.11 released in 2012.

​SQLite 3.8.8, released in 2015, further changed it so that VALUES(...) was not subject to the SQLITE_LIMIT_COMPOUND_SELECT value, which should subsequently allow for improvements in batch sizes (e.g. investigating changes to django.db.backends.sqlite3.operations.DatabaseOperations.bulk_batch_size and django.db.backends.sqlite3.features.DatabaseFeatures.max_query_params, the latter of which supports 32766 values instead of 999 as of ​3.32.0)

The documentation for current SQLite (3.37.2) says, of using VALUES(...):

The phrase "VALUES(expr-list)" means the same thing as "SELECT expr-list". The phrase "VALUES(expr-list-1),...,(expr-list-N)" means the same thing as "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N". Both forms are the same, except that the number of SELECT statements in a compound is limited by SQLITE_LIMIT_COMPOUND_SELECT whereas the number of rows in a VALUES clause has no arbitrary limit.
There are some restrictions on the use of a VALUES clause that are not shown on the syntax diagrams:
A VALUES clause cannot be followed by ORDER BY.
A VALUES clause cannot be followed by LIMIT.

I have run the test suite against 3.37.2 and whatever the CI versions are (I've not checked) ... so far so good.

[Mariusz Felisiak]

Thanks!

[Mariusz Felisiak <felisiak.mariusz@…>]

In c27932ec:

Fixed #33460 -- Used VALUES clause for insert in bulk on SQLite.

SQLite 3.7.11 introduced the ability to use multiple values directly.
SQLite 3.8.8 made multiple values not subject to the
SQLITE_LIMIT_COMPOUND_SELECT (500).