Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27937

Assertion failure when executing prepared statement with ? in IN list

Details

    Description

      With one of the last updates, a bug in mariadb server got introduced which returns unexpected behavior. The last tested version without this bug is 10.5.13/10.6.5.

      When executing a "where in" query with more than 1000 values in a prepared statement, the query will always return an empty result set. This only occurs in prepared statements, not if queried directly. We have attached a test case below.

      Steps to reproduce:
      1. Create a test table with a "val" column.
      2. Add two or more rows:

      INSERT INTO `test` (`id`, `val`) VALUES (1, 1), (2, 2), (3, 3);

      3. Create a prepared statement:

      PREPARE stmt1 FROM 'select * from test where val in (?,[more than 999 entries]...)';

      4. Execute the prepared statement with 999+ values. Exclude one or more rows to expect a non-empty result set:

      EXECUTE stmt1 USING 2, 3, ...;

      Expected results:
      A proper result with one or more entries, depending on the query. Or, alternatively, a proper error message stating why this is no longer possible.

      Actual results:
      No error message is returned, just an empty result set.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27869 Conversion of Big IN Predicates Into Subqueries returns wrong result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28557 No results-set returned when results exists.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23182 Server crashes in Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28041 Invalid resulset received for prepared statement

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28064 Prepared Statements WHERE IN limited to 999 parameters

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28316 Prepared statement with more than 1000 parameters leads to incorrect result

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            igor Igor Babaev (Inactive) added a comment -

            Here are simple test cases demonstrating this problem

            set in_predicate_conversion_threshold=2;
            		 
             
            		 
            create table t1 (id int, a int, b int);
            		 
            insert into t1 values (1,3,30), (2,7,70), (3,1,10);
            		 
             
            		 
            prepare stmt from "
            		 
            select * from t1 where a in (7, ?, 5, 1);
            		 
            ";
            		 
            execute stmt using 3;
            		 
            deallocate prepare stmt;
            		 
             
            		 
            prepare stmt from "
            		 
            select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
            		 
            ";
            		 
            execute stmt using 30;
            		 
            deallocate prepare stmt;
            		 
             
            		 
            drop table t1;
            		 
             
            		 
            set in_predicate_conversion_threshold=default;

            igor Igor Babaev (Inactive) added a comment - Here are simple test cases demonstrating this problem set in_predicate_conversion_threshold=2;   create table t1 (id int , a int , b int ); insert into t1 values (1,3,30), (2,7,70), (3,1,10);   prepare stmt from " select * from t1 where a in (7, ?, 5, 1); " ; execute stmt using 3; deallocate prepare stmt;   prepare stmt from " select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); " ; execute stmt using 30; deallocate prepare stmt;   drop table t1;   set in_predicate_conversion_threshold= default ;
            sanja Oleksandr Byelkin added a comment -

            1902fc43b74c9dabedbde0da6b567fa184eaf6d1 ok to push

            sanja Oleksandr Byelkin added a comment - 1902fc43b74c9dabedbde0da6b567fa184eaf6d1 ok to push
            igor Igor Babaev (Inactive) added a comment -

            A fix of this patch was pushed into 10.3

            igor Igor Babaev (Inactive) added a comment - A fix of this patch was pushed into 10.3
            greenman Ian Gilfillan added a comment -

            igor the fix versions look a bit janky - no mention of 10.6, two versions of 10.4?

            greenman Ian Gilfillan added a comment - igor the fix versions look a bit janky - no mention of 10.6, two versions of 10.4?
            Roel Roel Van de Paar added a comment -

            For clarity re: the last comment; this was fixed.

            Roel Roel Van de Paar added a comment - For clarity re: the last comment; this was fixed.

            People

              igor Igor Babaev (Inactive)
              kgeigerGD Karim Geiger
              Votes:
              0 Vote for this issue
              Watchers:
              13 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.