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

Unexpected behavior in string manipulation functions.

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4.2, 10.5, 10.6, 10.11, 11.2(EOL), 11.4
    • 10.5, 10.6, 10.11, 11.4
    • None
    • None
    • ubuntu 20.04

    Description

      drop table if exists t6;
      create table t6 (c22 int);
      insert into t6 values (16);
      insert into t6 values (80);
       
      select HEX(t6.c22) >REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'))  as c1 from t6;
       
       
      +------+
      | c1   |
      +------+
      |    1 |
      |    0 |
      +------+
      2 rows in set (0.00 sec)
       
       
      select * from t6
      where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));
      +------+
      | c22  |
      +------+
      |   16 |
      |   80 |
      +------+
      2 rows in set (0.00 sec)
       
      
      

      The result of the first SELECT SQL is the content of the WHERE clause in the second SELECT SQL, and the results returned are 1 and 0. Therefore, the query result of the second statement should only have one row. There seems to be some kind of bug.

       
      drop table if exists t6;
      create table t6 (c22 int);
      -- insert into t6 values (16);
      insert into t6 values (80);
       
      select HEX(t6.c22) >REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'))  as c1 from t6;
      +------+
      | c1   |
      +------+
      |    0 |
      +------+
      1 row in set (0.00 sec)
       
       
      select * from t6
      where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));
      Empty set (0.00 sec)
       
      
      

      Additionally, I tried deleting the inserted value 16, and the query results of these two statements became normal.This is strange.

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            Thank you for the report! I repeated on 10.5-11.4, it is dependent on the first value that was used to calculate 'where 1' :

            MariaDB [test]> create table t6 (c22 int);
            Query OK, 0 rows affected (0,057 sec)
             
            MariaDB [test]> insert into t6 values (16),(80),(90);
            Query OK, 3 rows affected (0,014 sec)
            Records: 3  Duplicates: 0  Warnings: 0
             
            MariaDB [test]> select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));
            +------+
            | c22  |
            +------+
            |   16 |
            |   80 |
            |   90 |
            +------+
            3 rows in set (0,002 sec)
             
            MariaDB [test]> truncate table t6;
            Query OK, 0 rows affected (0,096 sec)
             
            MariaDB [test]> insert into t6 values (80),(90),(16);
            Query OK, 3 rows affected (0,020 sec)
            Records: 3  Duplicates: 0  Warnings: 0
             
            MariaDB [test]> select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));
            +------+
            | c22  |
            +------+
            |   16 |
            +------+
            1 row in set (0,002 sec)
             
            MariaDB [test]> truncate table t6;
            Query OK, 0 rows affected (0,051 sec)
             
            MariaDB [test]> insert into t6 values (80),(16),(90);
            Query OK, 3 rows affected (0,014 sec)
            Records: 3  Duplicates: 0  Warnings: 0
             
            MariaDB [test]> select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));
            +------+
            | c22  |
            +------+
            |   16 |
            |   90 |
            +------+
            2 rows in set (0,002 sec)
             
            MariaDB [test]> explain extended select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
            |    1 | SIMPLE      | t6    | ALL  | NULL          | NULL | NULL    | NULL | 3    |   100.00 | Using where |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
            1 row in set, 1 warning (0,001 sec)
             
            Note (Code 1003): select `test`.`t6`.`c22` AS `c22` from `test`.`t6` where hex(`test`.`t6`.`c22`) > reverse(rpad(<cache>(case when 1 then '+YeL' else '+YeL' end),`test`.`t6`.`c22`,'7+02o'))
            
            

            alice Alice Sherepa added a comment - - edited Thank you for the report! I repeated on 10.5-11.4, it is dependent on the first value that was used to calculate 'where 1' : MariaDB [test]> create table t6 (c22 int); Query OK, 0 rows affected (0,057 sec)   MariaDB [test]> insert into t6 values (16),(80),(90); Query OK, 3 rows affected (0,014 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [test]> select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o')); +------+ | c22 | +------+ | 16 | | 80 | | 90 | +------+ 3 rows in set (0,002 sec)   MariaDB [test]> truncate table t6; Query OK, 0 rows affected (0,096 sec)   MariaDB [test]> insert into t6 values (80),(90),(16); Query OK, 3 rows affected (0,020 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [test]> select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o')); +------+ | c22 | +------+ | 16 | +------+ 1 row in set (0,002 sec)   MariaDB [test]> truncate table t6; Query OK, 0 rows affected (0,051 sec)   MariaDB [test]> insert into t6 values (80),(16),(90); Query OK, 3 rows affected (0,014 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [test]> select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o')); +------+ | c22 | +------+ | 16 | | 90 | +------+ 2 rows in set (0,002 sec)   MariaDB [test]> explain extended select * from t6 where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o')); +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t6 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,001 sec)   Note (Code 1003): select `test`.`t6`.`c22` AS `c22` from `test`.`t6` where hex(`test`.`t6`.`c22`) > reverse(rpad(<cache>(case when 1 then '+YeL' else '+YeL' end),`test`.`t6`.`c22`,'7+02o'))

            People

              psergei Sergei Petrunia
              Wangdada HeShan
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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