[Bug] upgrading from 2.1.2->2.1.7 causes query result error

[nandy33up]

Search before asking

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

Version

• 2.1.7 (upgrading from 2.1.2)

What's Wrong?

• 2.1.2 SUBSTRING_INDEX

mysql> select * from
(
	select 
				 SUBSTRING_INDEX(name, 'A', 1) t1,
				 LEFT(name, LOCATE('A', name)-1) t2,
				 name,
				 age,
				 code
	from
			(
				SELECT 
							 name,
							 age,
							 code
				FROM (
							SELECT '哈哈哈AAA' name, 20 age, '1' code
							UNION
							SELECT '哈哈哈AA+' name, 21 age, '2' code
							UNION
							SELECT '哈哈哈AA' name, 22 age, '3' code
							UNION
							SELECT '哈哈哈AA(2)' name, 23 age, '4' code
							UNION
							SELECT '哈哈哈AA-' name, 24 age, '5' code
						) a
			) b
) c
where t1 = '哈哈哈';
+--------+--------+-------------+-----+------+
| t1     | t2     | name        | age | code |
+--------+--------+-------------+-----+------+
| 哈哈哈 | 哈哈哈 | 哈哈哈AA-   |  24 | 5    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AAA   |  20 | 1    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA+   |  21 | 2    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA    |  22 | 3    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA(2) |  23 | 4    |
+--------+--------+-------------+-----+------+
5 rows in set (0.17 sec)

• 2.1.7 SUBSTRING_INDEX

mysql> select * from
(
	select 
				 SUBSTRING_INDEX(name, 'A', 1) t1,
				 LEFT(name, LOCATE('A', name)-1) t2,
				 name,
				 age,
				 code
	from
			(
				SELECT 
							 name,
							 age,
							 code
				FROM (
							SELECT '哈哈哈AAA' name, 20 age, '1' code
							UNION
							SELECT '哈哈哈AA+' name, 21 age, '2' code
							UNION
							SELECT '哈哈哈AA' name, 22 age, '3' code
							UNION
							SELECT '哈哈哈AA(2)' name, 23 age, '4' code
							UNION
							SELECT '哈哈哈AA-' name, 24 age, '5' code
						) a
			) b
) c
where t1 = '哈哈哈';
+--------+--------+-------------+-----+------+
| t1     | t2     | name        | age | code |
+--------+--------+-------------+-----+------+
| 哈哈哈 | 哈哈哈 | 哈哈哈AA-   |  24 | 5    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA(2) |  23 | 4    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA+   |  21 | 2    |
+--------+--------+-------------+-----+------+
3 rows in set (0.10 sec)

we use SUBSTRING_INDEX in the production, now we have to use LEFT+LOCATE for replacement.

What You Expected?

fix the query result bug

How to Reproduce?

• comment step

Anything Else?

No response

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

[nandy33up]

mysql> show global variables;
+------------------------------------------------------------------+-------------------------------------------+-------------------------------------------+---------+
| Variable_name                                                    | Value                                     | Default_Value                             | Changed |
+------------------------------------------------------------------+-------------------------------------------+-------------------------------------------+---------+
| character_set_client                                             | utf8                                      | utf8mb4                                   | 1       |
| character_set_connection                                         | utf8                                      | utf8mb4                                   | 1       |
| character_set_results                                            | utf8                                      | utf8mb4                                   | 1       |
| character_set_server                                             | utf8                                      | utf8mb4                                   | 1       |
| collation_connection                                             | utf8_general_ci                           | utf8mb4_0900_bin                          | 1       |
| collation_database                                               | utf8_general_ci                           | utf8mb4_0900_bin                          | 1       |
| collation_server                                                 | utf8_general_ci                           | utf8mb4_0900_bin                          | 1       |
| interactive_timeout                                              | 300                                       | 3600                                      | 1       |
| wait_timeout                                                     | 1800                                      | 28800                                     | 1       |
| adaptive_pipeline_task_serial_read_on_limit                      | 10000                                     | 10000                                     | 0       |
| allow_modify_materialized_view_data                              | false                                     | false                                     | 0       |
| allow_partition_column_nullable                                  | true                                      | true                                      | 0       |
...

[nandy33up]

we deployed a new single node fe+be cluster for test, it proved v2.1.7's query bug:

• without filter

mysql> select * from
(
	select 
				 SUBSTRING_INDEX(name, 'A', 1) t1,
				 LEFT(name, LOCATE('A', name)-1) t2,
				 name,
				 age,
				 code
	from
			(
				SELECT 
							 name,
							 age,
							 code
				FROM (
							SELECT '哈哈哈AAA' name, 20 age, '1' code
							UNION
							SELECT '哈哈哈AA+' name, 21 age, '2' code
							UNION
							SELECT '哈哈哈AA' name, 22 age, '3' code
							UNION
							SELECT '哈哈哈AA(2)' name, 23 age, '4' code
							UNION
							SELECT '哈哈哈AA-' name, 24 age, '5' code
						) a
			) b
) c;
+--------+--------+-------------+-----+------+
| t1     | t2     | name        | age | code |
+--------+--------+-------------+-----+------+
| 哈哈哈 | 哈哈哈 | 哈哈哈AAA   |  20 | 1    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA+   |  21 | 2    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA    |  22 | 3    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA(2) |  23 | 4    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA-   |  24 | 5    |
+--------+--------+-------------+-----+------+
5 rows in set (0.14 sec)

• with filter

mysql> select * from
(
	select 
				 SUBSTRING_INDEX(name, 'A', 1) t1,
				 LEFT(name, LOCATE('A', name)-1) t2,
				 name,
				 age,
				 code
	from
			(
				SELECT 
							 name,
							 age,
							 code
				FROM (
							SELECT '哈哈哈AAA' name, 20 age, '1' code
							UNION
							SELECT '哈哈哈AA+' name, 21 age, '2' code
							UNION
							SELECT '哈哈哈AA' name, 22 age, '3' code
							UNION
							SELECT '哈哈哈AA(2)' name, 23 age, '4' code
							UNION
							SELECT '哈哈哈AA-' name, 24 age, '5' code
						) a
			) b
) c
where t1 = '哈哈哈';
+--------+--------+-------------+-----+------+
| t1     | t2     | name        | age | code |
+--------+--------+-------------+-----+------+
| 哈哈哈 | 哈哈哈 | 哈哈哈AA-   |  24 | 5    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA+   |  21 | 2    |
| 哈哈哈 | 哈哈哈 | 哈哈哈AA(2) |  23 | 4    |
+--------+--------+-------------+-----+------+
3 rows in set (0.17 sec)

I compared the explain output of two versions(2.1.2, 2.1.7), 2.1.7 appears too simple

+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String(Nereids Planner)                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                                                        |
|   OUTPUT EXPRS:                                                                                                                                        |
|     t1[#21]                                                                                                                                            |
|     t2[#22]                                                                                                                                            |
|     name[#23]                                                                                                                                          |
|     age[#24]                                                                                                                                           |
|     code[#25]                                                                                                                                          |
|   PARTITION: UNPARTITIONED                                                                                                                             |
|                                                                                                                                                        |
|   HAS_COLO_PLAN_NODE: false                                                                                                                            |
|                                                                                                                                                        |
|   VRESULT SINK                                                                                                                                         |
|      MYSQL_PROTOCAL                                                                                                                                    |
|                                                                                                                                                        |
|   10:VEXCHANGE                                                                                                                                         |
|      offset: 0                                                                                                                                         |
|      distribute expr lists: name[#23], age[#24], code[#25]                                                                                             |
|                                                                                                                                                        |
| PLAN FRAGMENT 1                                                                                                                                        |
|                                                                                                                                                        |
|   PARTITION: HASH_PARTITIONED: name[#15], age[#16], code[#17]                                                                                          |
|                                                                                                                                                        |
|   HAS_COLO_PLAN_NODE: true                                                                                                                             |
|                                                                                                                                                        |
|   STREAM DATA SINK                                                                                                                                     |
|     EXCHANGE ID: 10                                                                                                                                    |
|     UNPARTITIONED                                                                                                                                      |
|                                                                                                                                                        |
|   9:VAGGREGATE (merge finalize)(981)                                                                                                                   |
|   |  group by: name[#15], age[#16], code[#17]                                                                                                          |
|   |  cardinality=3                                                                                                                                     |
|   |  final projections: substring_index(name[#18], 'A', 1), left(name[#18], CAST((locate('A', name[#18]) - 1) AS int)), name[#18], age[#19], code[#20] |
|   |  final project output tuple id: 9                                                                                                                  |
|   |  distribute expr lists: name[#15], age[#16], code[#17]                                                                                             |
|   |                                                                                                                                                    |
|   8:VEXCHANGE                                                                                                                                          |
|      offset: 0                                                                                                                                         |
|      distribute expr lists:                                                                                                                            |
|                                                                                                                                                        |
| PLAN FRAGMENT 2                                                                                                                                        |
|                                                                                                                                                        |
|   PARTITION: RANDOM                                                                                                                                    |
|                                                                                                                                                        |
|   HAS_COLO_PLAN_NODE: false                                                                                                                            |
|                                                                                                                                                        |
|   STREAM DATA SINK                                                                                                                                     |
|     EXCHANGE ID: 08                                                                                                                                    |
|     HASH_PARTITIONED: name[#15], age[#16], code[#17]                                                                                                   |
|                                                                                                                                                        |
|   7:VAGGREGATE (update serialize)(971)                                                                                                                 |
|   |  STREAMING                                                                                                                                         |
|   |  group by: name[#12], age[#13], code[#14]                                                                                                          |
|   |  cardinality=3                                                                                                                                     |
|   |  distribute expr lists:                                                                                                                            |
|   |                                                                                                                                                    |
|   6:VUNION(966)                                                                                                                                        |
|   |                                                                                                                                                    |
|   |----3:VEXCHANGE                                                                                                                                     |
|   |       offset: 0                                                                                                                                    |
|   |       distribute expr lists:                                                                                                                       |
|   |                                                                                                                                                    |
|   |----5:VEXCHANGE                                                                                                                                     |
|   |       offset: 0                                                                                                                                    |
|   |       distribute expr lists:                                                                                                                       |
|   |                                                                                                                                                    |
|   1:VEXCHANGE                                                                                                                                          |
|      offset: 0                                                                                                                                         |
|      distribute expr lists:                                                                                                                            |
|                                                                                                                                                        |
| PLAN FRAGMENT 3                                                                                                                                        |
|                                                                                                                                                        |
|   PARTITION: UNPARTITIONED                                                                                                                             |
|                                                                                                                                                        |
|   HAS_COLO_PLAN_NODE: false                                                                                                                            |
|                                                                                                                                                        |
|   STREAM DATA SINK                                                                                                                                     |
|     EXCHANGE ID: 05                                                                                                                                    |
|     RANDOM                                                                                                                                             |
|                                                                                                                                                        |
|   4:VUNION(951)                                                                                                                                        |
|      constant exprs:                                                                                                                                   |
|          0                                                                                                                                             |
|      final projections: '哈哈哈AA-', 24, '5'                                                                                                           |
|      final project output tuple id: 5                                                                                                                  |
|                                                                                                                                                        |
| PLAN FRAGMENT 4                                                                                                                                        |
|                                                                                                                                                        |
|   PARTITION: UNPARTITIONED                                                                                                                             |
|                                                                                                                                                        |
|   HAS_COLO_PLAN_NODE: false                                                                                                                            |
|                                                                                                                                                        |
|   STREAM DATA SINK                                                                                                                                     |
|     EXCHANGE ID: 03                                                                                                                                    |
|     RANDOM                                                                                                                                             |
|                                                                                                                                                        |
|   2:VUNION(940)                                                                                                                                        |
|      constant exprs:                                                                                                                                   |
|          0                                                                                                                                             |
|      final projections: '哈哈哈AA(2)', 23, '4'                                                                                                         |
|      final project output tuple id: 3                                                                                                                  |
|                                                                                                                                                        |
| PLAN FRAGMENT 5                                                                                                                                        |
|                                                                                                                                                        |
|   PARTITION: UNPARTITIONED                                                                                                                             |
|                                                                                                                                                        |
|   HAS_COLO_PLAN_NODE: false                                                                                                                            |
|                                                                                                                                                        |
|   STREAM DATA SINK                                                                                                                                     |
|     EXCHANGE ID: 01                                                                                                                                    |
|     RANDOM                                                                                                                                             |
|                                                                                                                                                        |
|   0:VUNION(929)                                                                                                                                        |
|      constant exprs:                                                                                                                                   |
|          0                                                                                                                                             |
|      final projections: CAST('哈哈哈AA+' AS varchar(32)), 21, '2'                                                                                      |
|      final project output tuple id: 1                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
116 rows in set (0.28 sec)

[nandy33up]

Can someone help us? thanks a lot. SOS!

[jacktengg]

It's supposed to be problem of plan. 2.1.6 is OK. @starocean999 is working on it.

[nandy33up]

It's supposed to be problem of plan. 2.1.6 is OK. @starocean999 is working on it.

Is there any way to downgrade to 2.1.6?

[starocean999]

It's supposed to be problem of plan. 2.1.6 is OK. @starocean999 is working on it.

Is there any way to downgrade to 2.1.6?

I see a UDF_SUBSTRING_INDEX function. You may check the UDF and take the pr as reference. And try use this UDF as workaround