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

IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison

    XMLWordPrintable

Details

    Description

      Here is a test case demonstrating the issue

      MariaDB [test]> create table t1(a int);
      Query OK, 0 rows affected (0.047 sec)
       
      MariaDB [test]> insert into t1 select seq from seq_1_to_100;
      Query OK, 100 rows affected (0.025 sec)
       
      MariaDB [test]> set in_predicate_conversion_threshold=9;
      Query OK, 0 rows affected (0.001 sec)
      

      Query 1:

      SELECT * FROM t1 WHERE a IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
      the column a has the same datatype as the elements in the IN list.

      MariaDB [test]> explain select * from t1 where a in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
      +------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------------+
      | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra          |
      +------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------------+
      |    1 | PRIMARY      | t1          | ALL    | NULL          | NULL         | NULL    | NULL |  100 |                |
      |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |                |
      |    2 | MATERIALIZED | <derived3>  | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                |
      |    3 | DERIVED      | NULL        | NULL   | NULL          | NULL         | NULL    | NULL | NULL | No tables used |
      +------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------------+
      4 rows in set (0.005 sec)
      

      Query 2:

      select * from t1 where a in ('0','1','2','3','4','5','6','7','8','9')
      the column a has different datatype as the elements in the IN list.

      MariaDB [test]> explain select * from t1 where a in ('0','1','2','3','4','5','6','7','8','9');
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |  100 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      1 row in set (0.002 sec)
      

      For query 2 it would be good if we could convert the the elements of the IN list to DOUBLE and do the comparison in DOUBLE. By doing this we could do materialization of the IN subquery, when the IN predicate gets converted.

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              7 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.