mysqldba-nandy commented on issue #44666: URL: https://github.com/apache/doris/issues/44666#issuecomment-2505450130
we deployed a new single node fe+be 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)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
