SQL query length default changed in 3.36

[kanishkachat]

Hi,
In SQLite 3.36, the default max SQL length has regressed from 1,000,000,000 bytes --> 1073741824 bytes

This was introduced in 57d597e

I believe we may have picked up this limit from SQLite documentation which states it as so: https://www.sqlite.org/rescode.html#toobig , however the real limit is actually larger and documented here: https://www.sqlite.org/limits.html#max_sql_length

I was able to validate the same in SQLite code as well: https://sqlite.org/src/file?name=src/sqliteLimit.h&ci=trunk

We can get around this by setting the limit via the SQLiteConfig, so not blocking in any manner. This was an FYI for this behavior.

Getting around it:

 SQLITE_CONFIG.setPragma(SQLiteConfig.Pragma.LIMIT_SQL_LENGTH, "1000000000");

Regards,
Kanishka

[gotson]

You explain quite well what happened and what's in sqlite, but you don't really explain what symptom or issue your experienced because of that. Would you be able to expand on that?

[kanishkachat]

The issue was with us bumping from version 3.34 --> 3.36, and few of our existing queries started failing due to

Caused by: org.sqlite.SQLiteException: [SQLITE_TOOBIG] String or BLOB exceeds size limit (statement too long)
at org.sqlite.core.DB.newSQLException(DB.java:1030)
at org.sqlite.core.DB.newSQLException(DB.java:1042)
at org.sqlite.core.DB.throwex(DB.java:1007)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:137)
at org.sqlite.core.DB.prepare(DB.java:257)
at org.sqlite.jdbc3.JDBC3Statement.executeQuery(JDBC3Statement.java:66)

We have few large queries which were breaching the threshold.

On looking at the commit diff, we would the above change which had inadvertently lowered the default limit.

[gotson]

So what do you expect from this issue ticket then? Some kind of fix, or was it raised to make other people aware?

[kanishkachat]

My expectation would be to fix the default value back to what it was to maintain backward compatibility. Awareness and search-ability of this was the secondary reason.

[gotson]

I don't think reverting the limit to no value is a good solution. Having sane defaults is good.

The release mentions the change, but not the fact that it could break existing long queries. Maybe that's something that could be added in the release notes.

[kanishkachat]

I don't think reverting the limit to no value is a good solution. Having sane defaults is good.

I don't particularly agree with this. Unless there are strong reason, we live with our limits. The end users may not be directly consuming this library, and may be consuming some service using this package. They won't realize why a scenario suddenly fails.

Also i don't see any reason why the limits of this library should defer from what SQLite provides. I would argue that the library should be as transparent as possible, and allow clients to configure appropriate limits based on their usecase.

[gotson]

The defaults in the driver are the same as the one in SQLite.

[mrckzgl]

I've come across the same problem and was glad to find this issue towards getting the cause.
What I wonder is the following. @gotson you write:

The defaults in the driver are the same as the one in SQLite.

But I am unable to find any mention of the DEFAULT_MAX_SQL_LENGTH = 1000000 1M bytes default max sql statement length in sqlite. The docs you refer to only mention this:

Maximum Length Of An SQL Statement

The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.

If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host [parameters](https://www.sqlite.org/lang_expr.html#varparam) for your data. Prepare short SQL statements like this:

    INSERT INTO tab1 VALUES(?,?,?); 

Then use the [sqlite3_bind_XXXX()](https://www.sqlite.org/c3ref/bind_blob.html) functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It also runs faster since the large string does not need to be parsed or copied as much.

The maximum length of an SQL statement can be lowered at run-time using the [sqlite3_limit](https://www.sqlite.org/c3ref/limit.html)(db,[SQLITE_LIMIT_SQL_LENGTH](https://www.sqlite.org/c3ref/c_limit_attached.html#sqlitelimitsqllength),size) interface.

This mentions a default of 1G. So I wonder where the 1M default actually comes from? Is there any documentation of this in sqlite? If not, this may also be an issue for the sqlite docs. Well, reading a bit deeper an odd mention is this sentence

If an SQL statement is limited to be a million bytes in length, [...]

but this also does not state that there is a default limit of 1M bytes...