Run sql failed when using replace function #9522
Description
Bug Report
When using the replace function in complex SQL, it may be affected by the where condition, causing the replace function to fail when pushed down to Tiflash for execution.
For example,
SELECT /*+ read_from_storage(tiflash[my_table]) */ REPLACE(my_table.col14, my_table.col_11, my_table.col_14) FROM my_table where my_table.col14 in 'TEST SQL';This statement will replace the replace function's parameter with the filter parameter when it is pushed down to improve SQL execution efficiency. Unfortunately, Tiflash only supports the replace function first parameter to be of type ColumnString.
1. Minimal reproduce step (Required)
mysql> CREATE TABLE my_table (
`col_11` VARCHAR(255) NOT NULL,
`col_12` int(10) unsigned NOT NULL,
`col_13` VARCHAR(255) NOT NULL,
`col_14` VARCHAR(255) NOT NULL
);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into my_table values ('World',1203945,'Hello, World!','FOR TEST'), ('ttttt',1122,'bbbbb','cccc');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT /*+ read_from_storage(tiflash[my_table]) */ REPLACE(my_table.col_13, my_table.col_11, my_table.col_13) FROM my_table;
+------------------------------------------------------------+
| REPLACE(my_table.col_13, my_table.col_11, my_table.col_13) |
+------------------------------------------------------------+
| Hello, Hello, World!! |
+------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from my_table;
+--------+---------+---------------+----------+
| col_11 | col_12 | col_13 | col_14 |
+--------+---------+---------------+----------+
| World | 1203945 | Hello, World! | FOR TEST |
| ttttt | 1122 | bbbbb | cccc |
+--------+---------+---------------+----------+
2 rows in set (0.00 sec)
mysql> select replace('Hello World',my_table.col_11, my_table.col_14) from my_table;
+---------------------------------------------------------+
| replace('Hello World',my_table.col_11, my_table.col_14) |
+---------------------------------------------------------+
| Hello FOR TEST |
| Hello World |
+---------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> explain select replace('Hello World',my_table.col_11, my_table.col_14) from my_table;
+-------------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
| Projection_3 | 2.00 | root | | replace(Hello World, test.my_table.col_11, test.my_table.col_14)->Column#6 |
| └─TableReader_6 | 2.00 | root | | data:TableFullScan_5 |
| └─TableFullScan_5 | 2.00 | cop[tikv] | table:my_table | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE my_table SET TIFLASH REPLICA 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT /*+ read_from_storage(tiflash[my_table]) */ REPLACE('Hello World', my_table.col_11, my_table.col_14) FROM my_table;
ERROR 1105 (HY000): other error for mpp stream: Code: 44, e.displayText() = DB::Exception: Illegal column Const(String) of first argument of function replaceAll, e.what() = DB::Exception,
mysql> explain SELECT /*+ read_from_storage(tiflash[my_table]) */ REPLACE('Hello World', my_table.col_11, my_table.col_14) FROM my_table;
+---------------------------+---------+--------------+----------------+----------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+--------------+----------------+----------------------------------------------------------------------------+
| TableReader_10 | 2.00 | root | | MppVersion: 2, data:ExchangeSender_9 |
| └─ExchangeSender_9 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_4 | 2.00 | mpp[tiflash] | | replace(Hello World, test.my_table.col_11, test.my_table.col_14)->Column#6 |
| └─TableFullScan_8 | 2.00 | mpp[tiflash] | table:my_table | keep order:false, stats:pseudo |
+---------------------------+---------+--------------+----------------+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)2. What did you expect to see? (Required)
Query OK.
3. What did you see instead (Required)
ERROR 1105 (HY000): other error for mpp stream: Code: 44, e.displayText() = DB::Exception: Illegal column Const(String) of first argument of function replaceAll, e.what() = DB::Exception,
4. What is your TiFlash version? (Required)
master