Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.2, 10.5, 10.6, 10.11, 11.2(EOL), 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
- is duplicated by
-
MDEV-35129 Unexpected behavior in REPEAT functions.
-
- Closed
-
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'))