Document that operations that delete in bulk can hit limits for the number of arguments

Added documentation to the following functions:

• node_delete_multiple()
• node_load_multiple()
• DrupalDefaultEntityController::buildQuery()

The same docs change, rolled for 8.x

I imagine that the limit is dependent on the database driver and/or configuration. In my testing configuration (PHP 5.5.0 with memory_limit set to 256M, PDO/sqlite3 database), 2000 is clearly too high.

I didn't try to find exactly what number breaks the query in my configuration, let alone under a broad range of databases and memory limit configurations.

Hm. Can we provide any guidance at all then?

Not without extensive testing that I frankly am not interested in doing. For all I know, it's a fundamental hard-coded limit of PDO and not dependent on database/driver/config at all. If so, then our function should be rewritten to enforce the limits of the language we are using.

You've identified PDO as the bottleneck for the number of values inside IN()? Good!

No I have not identified the bottleneck. Nor am I interested in doing so. Please re-read #7 and do not mis-quote me.

Will salvis please file his own bug report against the Drupal core database system instead of trying to sidetrack this documentation issue?

You mentioned "out of memory errors", but do you know of this being a possibility?

Since all of the node objects are being loaded into memory before being deleted, I imagine that in certain edge cases (very complex nodes in combination with a very small PHP memory_limit) there could be an out-of-memory issue. In my experience, this manifests as weird syntax errors in code that is actually syntactically correct.

The only error I see in that other issue is a PDO error due to too many placeholders.

I'm sure you are more knowledgable than I, but what experience I have in database optimization leads me to believe that extremely long and/or complex queries are detrimental to performance, quite aside from any limits built in to the PDO code.

The part you added to the doc doesn't even mention placeholders or explain how having "all the items in one query" would cause problems.

I try to write documentation from a user standpoint, not from a systems engineering standpoint. All the user needs to know is that putting too many items in that array can lead to errors.

Really, the problem is not that all the results come in one query, it's that the IN() clause has too many items in it, or that there are too may placeholders in the query.

The problem is that the current code and documentation assumes that it is possible and practical to produce unlimited-length SQL queries which load unlimited amounts of data into RAM.

You decided to document this in 3 functions, but there are many others that could be affected, right? So why did you decide on this particular list of 3 functions? It seems a bit arbitrary.

We scratch our own itch. But you're right; I should have submitted three patches, not one.

Marking this a duplicate in favor of:

• #1212146: Document limited size of the $nids array argument to node_delete_multiple() function.
• #1212148: Document limited size of the $nids array argument to node_load_multiple() function.
• #1212150: Document limited size of the $ids and $conditions array arguments to DrupalDefaultEntityController::buildQuery() function.

Why open those three issues

Because then you can debate those three issues separately rather than arguing about whether I've managed to find every single function whose documentation exhibits this bug.

when the real problem is entity_load and the default controller?

Actually, the lowest-level function in the call chain is DatabaseCondition::compile().

I got a fatal error while using a very popular Drupal module. I submitted a patch to fix the fatal error, and was told that the module is working as designed. I submitted a documentation patch to help future programmers avoid the fatal error, and am being told that I have wrongly documented the wrong functions. Therefore, I have posted comments to the relevant API pages as a stopgap measure.

At this point, I don't care anymore.

From http://www.sqlite.org/limits.html:

Maximum Number Of Host Parameters In A Single SQL Statement

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like 1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, size) interface.

I recompiled sqlite with SQLITE_MAX_VARIABLE_NUMBER set to 65536, then recompiled PHP, and performed the same test with the same results.

--- sqlite3-3.7.4/src/sqliteLimit.h     2010-11-29 11:24:18.000000000 -0500
+++ sqlite3-3.7.4-patched/src/sqliteLimit.h     2011-07-08 12:54:25.391881511 -0400
@@ -130,7 +130,7 @@
 ** The maximum value of a ?nnn wildcard that the parser will accept.
 */
 #ifndef SQLITE_MAX_VARIABLE_NUMBER
-# define SQLITE_MAX_VARIABLE_NUMBER 999
+# define SQLITE_MAX_VARIABLE_NUMBER 65536
 #endif
 
 /* Maximum page size.  The upper bound on this value is 65536.  This a limit