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_sleevesWHEREid` 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.