More than 1000 expressions in a list are not allowed on Oracle

[Markoise]

Steps to reproduce

I have displayed a table that has over 2000 rows.

Expected behavior

No error message

Actual behavior

An error message appears in the Nextcloud log, which is probably caused by the table app.

Tables app version

0.7.1

Browser

Brave 124

Client operating system

Windows 11, Android

Operating system

Debian

Web server

Nginx

PHP engine version

PHP 8.2

Database

MariaDB

Additional info

{"reqId":"*******************","level":3,"time":"2024-05-05T12:17:56+02:00","remoteAddr":"xxx.xxx.xxx.xxx","user":"username","app":"core","method":"GET","url":"/apps/tables/row/table/2","message":"More than 1000 expressions in a list are not allowed on Oracle.","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36","version":"29.0.0.19","exception":{"Exception":"Doctrine\DBAL\Query\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code":0,"Trace":[{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":293,"function":"execute","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/QBMapper.php","line":335,"function":"executeQuery","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Db/RowSleeveMapper.php","line":46,"function":"findEntities","class":"OCP\AppFramework\Db\QBMapper","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Db/Row2Mapper.php","line":222,"function":"findMultiple","class":"OCA\Tables\Db\RowSleeveMapper","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Db/Row2Mapper.php","line":181,"function":"getRows","class":"OCA\Tables\Db\Row2Mapper","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Service/RowService.php","line":79,"function":"findAll","class":"OCA\Tables\Db\Row2Mapper","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/RowController.php","line":39,"function":"findAllByTable","class":"OCA\Tables\Service\RowService","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/Errors.php","line":16,"function":"OCA\Tables\Controller\{closure}","class":"OCA\Tables\Controller\RowController","type":"->","args":[" sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/RowController.php","line":38,"function":"handleError","class":"OCA\Tables\Controller\RowController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":232,"function":"index","class":"OCA\Tables\Controller\RowController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":138,"function":"executeController","class":"OC\AppFramework\Http\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":184,"function":"dispatch","class":"OC\AppFramework\Http\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":338,"function":"main","class":"OC\AppFramework\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1050,"function":"match","class":"OC\Route\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":49,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","Line":263,"message":"More than 1000 expressions in a list are not allowed on Oracle.","query":"SELECT * FROM *PREFIX*tables_row_sleeves WHERE id IN (:dcValue1)","exception":[],"CustomMessage":"More than 1000 expressions in a list are not allowed on Oracle."},"id":"66375da8364c9"}

[Sylvain303]

I'm curious, would it be resolved by using real DB backend for the table? I started a discussion about it here: #1014

I mean does Oracle impose limitation on the number of column?

How did you create such table? Imported form CSV?

[Markoise]

It's not about columns, but about rows. The problem has only occurred since the update to Tables 0.7.1 and Nextcloud 29.0.0 I did not have such messages before.

I originally created this table empty and usually maintain it via the web frontend. Sometimes I import data records via a CSV file.

[enjeck]

@blizzz Any suggestions what could be going on here? :thinking: Is it same problem you're solving at https://github.com/nextcloud/tables/pull/1049 ?

[Sylvain303]

oh 😯, I see... 🤔🤔

Something like on this post:

https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is

select field1, field2, field3 
from table1 
where name in 
(
'value1',
'value2',
...
'value10000+'
);

which I may guess from the error message you've posted:

SELECT * FROM *PREFIX*tables_row_sleeves WHERE id IN (:dcValue1)
--                                               ^^^^^^^^^^^^

how many row do you have?

[blizzz]

@blizzz Any suggestions what could be going on here? 🤔 Is it same problem you're solving at #1049 ?

Different thing, but also a backend DB handling thing.

[Markoise]

how many row do you have?

There are currently 3214 rows in this table. This is also my largest table.

[vwbusguy]

I'm seeing this now as well, and also running MariaDB 10.11.10 (not Oracle MySQL). I see this on Nextcloud 30.0.2 with a large table from facerecognition (facerecog_faces). I don't remember seeing this before now.

REf. https://github.com/matiasdelellis/facerecognition/issues/786

[jacques74]

I'm French, and I installed my first server one month ago so I have difficulties to follow this conversation...

I have this error in my YunoHost installation with Nextcloud 30.

I understand that it's possible to solve this problem, but: I do not understand if this error is really a problem since Nextcloud is using MariaDB and not Oracle. Does it affect the results? The duration of the process?

I understand it's possible to solve it by splitting the request into smaller ones, but I don't get which part of which file I have to modify and I didn't find a clear path to do it.

Can someone guide me? 😇