[Bug] substring_index with non constant parameter error

[justfortaste]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.1

What's Wrong?

mysql> set enable_fallback_to_original_planner=false;
Query OK, 0 rows affected (0.00 sec)

mysql> set experimental_enable_nereids_planner=true;
Query OK, 0 rows affected (0.00 sec)

mysql> select substring_index(a.char_value, " ", a.k) from t1 a join t1 b;
ERROR 1105 (HY000): errCode = 2, detailMessage = substring_index function except for the first argument, other parameter must be a constant.
mysql>
mysql> set experimental_enable_nereids_planner=false;
Query OK, 0 rows affected (0.00 sec)

mysql> select substring_index(a.char_value, " ", a.k) from t1 a join t1 b;
ERROR 1052 (23000): errCode = 2, detailMessage = Column 'date_value' in field list is ambiguous

What You Expected?

mysql> select substring_index(a.char_value, " ", a.k) from t1 a join t1 b;
+-------------------------------------+
| substring_index(char_value, ' ', k) |
+-------------------------------------+
| c |
| c |
| c |
| c |
| a |
| a |
| a |
| a |
| c |
| c |
| c |
| c |
| b |
| b |
| b |
| b |
+-------------------------------------+
16 rows in set (0.05 sec)

How to Reproduce?

drop table if exists t1;
CREATE TABLE IF NOT EXISTS t1 (
k int,
int_value int,
char_value char(10),
date_value date not null
)
ENGINE=OLAP
duplicate KEY(k)
auto partition by RANGE (date_trunc(date_value, 'month'))
(PARTITION p20201101000000 VALUES [('2024-11-01'), ('2024-12-01')),
PARTITION p20241201000000 VALUES [('2024-12-01'), ('2025-01-01')))
DISTRIBUTED BY HASH(k) BUCKETS 2 properties("replication_num" = "1");
insert into t1 values(1,1,'a', '2020-12-01');
insert into t1 values(2,2,'b', '2021-12-01');
insert into t1 values(3,2,'c', '2022-12-01');
insert into t1 values(4,4,'c', '2023-12-01');

select * from t1;

set enable_fallback_to_original_planner=false;
set experimental_enable_nereids_planner=true;
select substring_index(a.char_value, " ", a.k) from t1 a join t1 b;

set experimental_enable_nereids_planner=false;
select substring_index(a.char_value, " ", a.k) from t1 a join t1 b;

Anything Else?

ref:
https://github.com/apache/doris/pull/27891/files#diff-a1925ab8626cce0f685b2c94b582ff5630791fc84e90265aef83f7cc2764bf94

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct