Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5468

A query containing a big list of values in the IN-clause immediately crashes columnstore engine (add limit setting)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 23.02.1, 23.02.3
    • 23.10.0
    • MariaDB Server
    • MariaDB 10.5.10, Columnstore 5.5.2; MariaDB 10.5.18, Columnstore 5.6.8; RHEL 8.2 and RHEL 8.5
    • 2023-8, 2023-10

    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.

      Attachments

        1. Bug_reproduction.png
          108 kB
          andreas eschbacher
        2. crashing_select_on_reduced_table_columnstore.sql
          3.46 MB
          andreas eschbacher
        3. create_tables_without_select_from_original.sql
          0.3 kB
          andreas eschbacher
        4. ebi_es_op_or_ebi_1021.csv
          4.41 MB
          andreas eschbacher
        5. mysql_command_for_importing_tables.txt
          0.3 kB
          andreas eschbacher
        6. working_select_on_reduced_table_innodb.sql
          3.46 MB
          andreas eschbacher

        Issue Links

          Activity

            People

              Andrey Andrey Piskunov (Inactive)
              aeae81 andreas eschbacher
              Leonid Fedorov Leonid Fedorov
              Daniel Lee Daniel Lee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.