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

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4, 10.5
    • Fix Version/s: 10.3, 10.4, 10.5
    • Component/s: Optimizer
    • Labels:
      None

      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

              Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: