in tdengine 3.3.5.0 UNION ALL does not work properly with different length strings
Open
Nobody has claimed this yet.
bug
- Dominant language
- C
- Stars
- 25.1k
- Forks
- 5k
- Avg merge
- 4d 59m
- Merged PRs (30d)
- 7
Description
Bug Description
3.3.5.0版本执行以下SQL语句异常:
(select 1741708800000 startTime,
'37KAP1_DL' deviceId,
1215373850266001408 companyId,
last(collect_date_time) endTime,
last(temp.collect_value) - first(temp.collect_value) usage from (
select
collect_date_time, collect_value
from aiot_dasmeasure.measure_data_t1743084587277402113
where project_id = 1192410229407772672
and device_id = '37KAP1_DL'
and collect_date_time >= 1741708800000
and collect_date_time <= 1745510400000
and is_delete is null
order by collect_date_time
) temp)
UNION ALL
(select 1741708800000 startTime,
'37KAP2_DL' deviceId,
1215373850266001408 companyId,
last(collect_date_time) endTime,
last(temp.collect_value) - first(temp.collect_value) usage from (
select
collect_date_time, collect_value
from aiot_dasmeasure.measure_data_t1743084587277402113
where project_id = 1192410229407772672
and device_id = '37KAP2_DL'
and collect_date_time >= 1741708800000
and collect_date_time <= 1745510400000
and is_delete is null
order by collect_date_time
) temp)
UNION ALL
(select 1741708800000 startTime,
'8FPDX_WYGL_3_DL' deviceId,
1215373861133443072 companyId,
last(collect_date_time) endTime,
last(temp.collect_value) - first(temp.collect_value) usage from (
select
collect_date_time, collect_value
from aiot_dasmeasure.measure_data_t1743084587277402113
where project_id = 1192410229407772672
and device_id = '8FPDX_WYGL_3_DL'
and collect_date_time >= 1741708800000
and collect_date_time <= 1745510400000
and is_delete is null
order by collect_date_time
) temp)
当把第三段中的8FPDX_WYGL_3_DL长度减少到9位的时候正常,以下SQL能够正常执行:
(select 1741708800000 startTime,
'37KAP1_DL' deviceId,
1215373850266001408 companyId,
last(collect_date_time) endTime,
last(temp.collect_value) - first(temp.collect_value) usage from (
select
collect_date_time, collect_value
from aiot_dasmeasure.measure_data_t1743084587277402113
where project_id = 1192410229407772672
and device_id = '37KAP1_DL'
and collect_date_time >= 1741708800000
and collect_date_time <= 1745510400000
and is_delete is null
order by collect_date_time
) temp)
UNION ALL
(select 1741708800000 startTime,
'37KAP2_DL' deviceId,
1215373850266001408 companyId,
last(collect_date_time) endTime,
last(temp.collect_value) - first(temp.collect_value) usage from (
select
collect_date_time, collect_value
from aiot_dasmeasure.measure_data_t1743084587277402113
where project_id = 1192410229407772672
and device_id = '37KAP2_DL'
and collect_date_time >= 1741708800000
and collect_date_time <= 1745510400000
and is_delete is null
order by collect_date_time
) temp)
UNION ALL
(select 1741708800000 startTime,
'WYGL_3_DL' deviceId,
1215373861133443072 companyId,
last(collect_date_time) endTime,
last(temp.collect_value) - first(temp.collect_value) usage from (
select
collect_date_time, collect_value
from aiot_dasmeasure.measure_data_t1743084587277402113
where project_id = 1192410229407772672
and device_id = '8FPDX_WYGL_3_DL'
and collect_date_time >= 1741708800000
and collect_date_time <= 1745510400000
and is_delete is null
order by collect_date_time
) temp)
Expected Behavior
在UNION ALL中长度不一样也能够正常执行
Environment (please complete the following information):
- TDengine Version [3.3.5.0]
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the supplied UNION ALL statements on TDengine 3.3.5.0, comparing the failing query with the version whose third deviceId is shorter. Trace the UNION ALL handling and string-type resolution involved in those queries; done means UNION ALL executes correctly when the string lengths differ, with a regression test covering the reported case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100