SQLite: "too many terms in compound SELECT" when inserting lots of records at once

[dos1]

SQLite has a hard limit of 500 terms in a compound SELECT (see: https://stackoverflow.com/a/9527898). The limit can be configured by the user to be lower, but not greater.

Laravel doesn't take that into account, resulting in an "too many terms in compound SELECT" error when trying to insert more than 500 entries at once.

framework/src/Illuminate/Database/Query/Grammars/SQLiteGrammar.php

Lines 146 to 187 in fe68aa9

	/**
	* Compile an insert statement into SQL.
	*
	* @param \Illuminate\Database\Query\Builder $query
	* @param array $values
	* @return string
	*/
	public function compileInsert(Builder $query, array $values)
	{
	// Essentially we will force every insert to be treated as a batch insert which
	// simply makes creating the SQL easier for us since we can utilize the same
	// basic routine regardless of an amount of records given to us to insert.
	$table = $this->wrapTable($query->from);
	if (! is_array(reset($values))) {
	$values = [$values];
	}
	// If there is only one record being inserted, we will just use the usual query
	// grammar insert builder because no special syntax is needed for the single
	// row inserts in SQLite. However, if there are multiples, we'll continue.
	if (count($values) === 1) {
	return empty(reset($values))
	? "insert into $table default values"
	: parent::compileInsert($query, reset($values));
	}
	$names = $this->columnize(array_keys(reset($values)));
	$columns = [];
	// SQLite requires us to build the multi-row insert as a listing of select with
	// unions joining them together. So we'll build out this list of columns and
	// then join them all together with select unions to complete the queries.
	foreach (array_keys(reset($values)) as $column) {
	$columns[] = '? as '.$this->wrap($column);
	}
	$columns = array_fill(0, count($values), implode(', ', $columns));
	return "insert into $table ($names) select ".implode(' union all select ', $columns);
	}

A possible solution would be to split up big datasets into multiple queries (at most SQLITE_MAX_COMPOUND_SELECT) in SQLiteGrammar's compileInsert function. Another one would be to simply wrap many inserts into a single transaction regardless of the dataset size, which should perform similarly performance-wise.

The current limit on the database can be checked with sqlite3_limit(db, SQLITE_MAX_COMPOUND_SELECT, -1) function, as described in https://www.sqlite.org/c3ref/limit.html

[staudenmeir]

What query are you executing?

[dos1]

Just a regular batch insert of ~700 entries. The exact context can be seen here: dos1/movim@306de37 - where Roster::insert comes from Illuminate\Database\Eloquent\Model. The linked commit successfully workarounds this issue - splitting it to SQLITE_MAX_COMPOUND_SELECT entries per batch would work too.

Keep in mind that the only way to do batch inserts in SQLite is to do it via compound SELECT, so the error message makes sense. There is an alternative syntax available, but it's just a syntax sugar.

(btw. it's not my code, I know about it [or Laravel] only as much as I debugged, which led me to filling this bug)

[staudenmeir]

We could fix it by using the standard syntax: INSERT INTO "table" ("column") VALUES (?), (?)
SQLite has been supporting it since 3.7.11 (6.5 years ago).

Ideally, we would detect the SQLite version and use the legacy syntax if required. But the problem is that we don't have access to the database version inside the grammar.

Replacing the old syntax completly would be a breaking change. And you can bet that some hosting services still use ancient SQLite versions...

This is another use case for version-specific grammars (laravel/ideas#870).

[dos1]

@staudenmeir That syntax you mention is implemented in SQLite as just a syntax sugar and falls under the same limit as the old one.

[staudenmeir]

The syntax works for me. Do you get an error?

[dos1]

Oh, looks like it was fixed in 3.8.8. So that would be the version you would want to check against.

The number of rows in a VALUES clause is no longer limited by SQLITE_LIMIT_COMPOUND_SELECT.

[dos1]

@laurencei Any info on why has it been closed? I just looked at the SQLiteGrammar.php file on master branch and I don't see any relevant update there.

[laurencei]

You said it was fixed in 3.8.8? So they just need to update SQLite?

[dos1]

@laurencei Oh, no, it was a reply to the previous comment referring to the alternative syntax to the one used now by Laravel (before 3.8.8 it was just a syntax sugar, so changing it in Laravel won't fix the issue for the older ones). So, if Laravel is fine with requiring 3.8.8+, the syntax can simply be changed, otherwise it should do a version check and construct multiple INSERT queries on older ones (or just ignore that and leave this bug for the users of older SQLite versions).

To sum up, this is a Laravel bug that happens on the newest SQLite. Any specific SQLite versions were only mentioned when discussing a way to fix it.

[dos1]

(if you're fine with requiring SQLite 3.8.8 [or 3.7.11 if you don't mind this bug still happening for some people], I should even manage to make a PR with the fix :P)

[laurencei]

...it kind of feels like a real edge case, that if someone runs into it - the easiest/obvious solution is to update to SQLite 3.8.8?

Changing the SQL statement just to handle this single case might have a performance impact for other users not affected by it?

[dos1]

Uhm, no, please read the whole discussion again. This happens for every SQLite version right now, it's just a fix proposed in #25262 (comment) that will require SQLite 3.8.8 to actually fix anything.

[laurencei]

Ok... now I see.

Well - seems like the best option is submit a PR for this and see what Taylor thinks.

[dos1]

Submitted :)

[staudenmeir]

Please close the issue.