taosdata / taosdata/TDengine

in tdengine 3.3.5.0 UNION ALL does not work properly with different length strings

Open
#30,632 3 comments 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.