[MCOL-5468] A query containing a big list of values in the IN-clause immediately crashes columnstore engine (add limit setting) Created: 2023-04-05  Updated: 2023-12-28  Resolved: 2023-09-07

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 23.02.1, 23.02.3
Fix Version/s: 23.10.0

Type: Bug Priority: Critical
Reporter: andreas eschbacher Assignee: Andrey Piskunov (Inactive)
Resolution: Fixed Votes: 1
Labels: rm_stability
Environment:

MariaDB 10.5.10, Columnstore 5.5.2; MariaDB 10.5.18, Columnstore 5.6.8; RHEL 8.2 and RHEL 8.5

Attachments: PNG File Bug_reproduction.png     File crashing_select_on_reduced_table_columnstore.sql     File create_tables_without_select_from_original.sql     File ebi_es_op_or_ebi_1021.csv     Text File mysql_command_for_importing_tables.txt     File working_select_on_reduced_table_innodb.sql    
Issue Links:
Duplicate
is duplicated by MCOL-5321 MCS plugin crashes MDB runtime for IN... Closed
Sprint: 2023-8, 2023-10
Assigned for Review: Leonid Fedorov Leonid Fedorov
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

The attached query ("crashing_select...sql") on the attached Columnstore table (see "create_tables_...sql") using the exported data ("ebi_es_op_or...csv") immediately crashes Columnstore engine when executed.
Result: ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
When the table is InnoDB (suffix "_innodb") it works flawlessly ("working_select...sql")

Side note: query is generated by a third-party BI tool, where we have no possibility to change the way it generates the SQL queries to avoid such big IN-clauses.
TBD: solution :

.....

The currently proposed solution: Introduction of a new runtime variable defined as
MariaDB [test]> show variables like 'columnstore_max_in%';
--------------------------------------------+

Variable_name Value

--------------------------------------------+

columnstore_max_in_limit_query_length 1000

--------------------------------------------+
1 row in set (0.001 sec)

which is a limit for the maximum length of an IN query measure in number of values .
If the query is longer, an error is signaled:
ERROR 1178 (42000): The storage engine for the table doesn't support long in clauses. Query exceeds max_in_limit_query_length threshold: consider changing the value via SET @var_name := value;

If there are multiple IN clauses in query, the limit is checked against the longest.
The default value is a subject to be determined, right now it is 6000 items.
Right now this has a possible side effect of possibly limiting some of the OR clauses as well, but I have not managed to find one.

 Comments   
Comment by Andrey Piskunov (Inactive) [ 2023-07-25 ]

The currently proposed solution: Introduction of a new config variable InLimit, defined as

	<InLimit>
 
	    <InQueryLimit>3</InQueryLimit>
 
	</InLimit>

which is a limit for the maximum length of an IN query.
If the query is longer, an error is signalled:
ERROR 1178 (42000): The storage engine for the table doesn't support long in clauses. Query exceeds InLimit threshold: check InLimit in config file
If there are multiple IN clauses in query, the limit is checked against the longest.
The default value is a subject to be determined, right now it is 1000.
Right now this has a possible side effect of possibly limiting some of the OR clauses as well, but I have not managed to find one.

toddstoffel you might have some input on this.

Comment by Andrey Piskunov (Inactive) [ 2023-07-26 ]

The unit is number of entities.

Comment by Andrey Piskunov (Inactive) [ 2023-07-28 ]

toddstoffel , alexey.vorovich
I have updated the description (and also the branch of course) to make the limit a runtime value.

Comment by alexey vorovich (Inactive) [ 2023-07-28 ]

allen.herrera the current default is 1000. see above.

Can that session variable be in config file ?

Comment by Allen Herrera [ 2023-07-28 ]

yeah thats fine, ideal would be dynamic to avoid stop/starting the database, architecture allowing.

Comment by Daniel Lee (Inactive) [ 2023-09-07 ]

Build verified: develop branch, build 8586

engine: e0d9b8270584bcc9a92f9eff3a84e949cd2f052e
server: 8178f1d088c14ff30c274081f18cbdf7b2e37d9b
buildNo: 8586

MariaDB [mytest]> set columnstore_max_allowed_in_values=3;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [mytest]> select * from orders where o_orderkey in (5210112,5210087,5210086,5210085);
 
ERROR 1178 (42000): The storage engine for the table doesn't support  in clauses of this length. Number of values in the IN clause exceeded columnstore_max_allowed_in_values threshold.
 
MariaDB [mytest]> select * from orders where o_orderkey in (5210112,5210087,5210086);
 
+------------+-----------+---------------+--------------+-------------+-----------------+-----------------+----------------+----------------------------------------------------------------+
 
| o_orderkey | o_custkey | o_orderstatus | o_totalprice | o_orderdate | o_orderpriority | o_clerk         | o_shippriority | o_comment                                                      |
 
+------------+-----------+---------------+--------------+-------------+-----------------+-----------------+----------------+----------------------------------------------------------------+
 
|    5210086 |     97676 | F             |    288412.41 | 1992-08-01  | 5-LOW           | Clerk#000000448 |              0 | leep furiously around the blithely pending requests. req       |
 
|    5210087 |     30775 | O             |    191026.67 | 1997-05-07  | 4-NOT SPECIFIED | Clerk#000000010 |              0 | ns are regular requests. furiously regular packages nag idly f |
 
|    5210112 |     26012 | F             |     57132.55 | 1992-08-15  | 2-HIGH          | Clerk#000000820 |              0 | . blithely pending requests haggle slyly. instru               |
 
+------------+-----------+---------------+--------------+-------------+-----------------+-----------------+----------------+----------------------------------------------------------------+
 
3 rows in set (0.037 sec)

Generated at Thu Feb 08 02:58:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.