bug: Simple Group By fails to return

[rad-pat]

Search before asking

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

Version

v1.2.659-nightly

What's Wrong?

This is a strange one. I have a table with 54,596 records. The simple group-by shown below fails to return. If I create a new table t2 with same columns and insert data into it via INSERT INTO t2 SELECT * from t1, the similar group by query for t2 also fails. However, if I insert the data via, INSERT INTO t2 TOP 55000 * FROM t1 then the similar group by query works. I cannot even get an EXPLAIN on the group by query if I use INSERT without TOP

Lots of this reported in the logs and have to restart query pod.

c6a7b482-99f8-4ee1-a9f7-104983153946 2024-11-16T12:20:42.596745Z  WARN databend_query::pipelines::executor::processor_async_task: processor_async_task.rs:146 Very slow processor async task, query_id:"c6a7b482-99f8-4ee1-a9f7-104983153946", processor id: NodeIndex(11), name: "ExchangeSourceReader", elapsed: 10.00217565s, active sync workers: 0
c6a7b482-99f8-4ee1-a9f7-104983153946 2024-11-16T12:20:42.598818Z  WARN databend_query::pipelines::executor::processor_async_task: processor_async_task.rs:146 Very slow processor async task, query_id:"c6a7b482-99f8-4ee1-a9f7-104983153946", processor id: NodeIndex(50), name: "ExchangeSourceReader", elapsed: 10.004329116s, active sync workers: 0
c6a7b482-99f8-4ee1-a9f7-104983153946 2024-11-16T12:20:42.598801Z  WARN databend_query::pipelines::executor::processor_async_task: processor_async_task.rs:146 Very slow processor async task, query_id:"c6a7b482-99f8-4ee1-a9f7-104983153946", processor id: NodeIndex(11), name: "ExchangeSourceReader", elapsed: 10.004214976s, active sync workers: 0
c6a7b482-99f8-4ee1-a9f7-104983153946 2024-11-16T12:20:47.598095Z  WARN databend_query::pipelines::executor::processor_async_task: processor_async_task.rs:146 Very slow processor async task, query_id:"c6a7b482-99f8-4ee1-a9f7-104983153946", processor id: NodeIndex(11), name: "ExchangeSourceReader", elapsed: 15.003525376s, active sync workers: 0

-- definition
CREATE TABLE t1 (
  "C1" VARCHAR NULL,
  "C2" VARCHAR NULL,
  "C3" VARCHAR NULL,
  "C4" VARCHAR NULL,
  "C5" VARCHAR NULL,
  "V1" DECIMAL(38, 10) NULL
);
select count(*) from t1; -- 54,596

-- failing query
SELECT t1."C1"
    ,t1."C2"
    ,t1."C3"
    ,t1."C4"
    ,t1."C5"
    ,sum(t1."V1")
FROM t1
GROUP BY t1."C1"
    ,t1."C2"
    ,t1."C3"
    ,t1."C4"
    ,t1."C5"

CREATE TABLE t2 (
  "C1" VARCHAR NULL,
  "C2" VARCHAR NULL,
  "C3" VARCHAR NULL,
  "C4" VARCHAR NULL,
  "C5" VARCHAR NULL,
  "V1" DECIMAL(38, 10) NULL
);

--INSERT INTO t2 select * from t1;  -- insert this way, group by will fail
--INSERT INTO t2 select top 55000 * from t1; -- insert this way, group by will work

select t2."C1"
    ,t2."C2"
    ,t2."C3"
    ,t2."C4"
    ,t2."C5"
    ,sum(t2."V1")
FROM t2
GROUP BY t2."C1"
    ,t2."C2"
    ,t2."C3"
    ,t2."C4"
    ,t2."C5"

The plan for the group by after insert via TOP * records looks like this:

explain                                                                                                    |
-----------------------------------------------------------------------------------------------------------+
Exchange                                                                                                   |
├── output columns: [sum(t2."V1") (#6), t2.C1 (#0), t2.C2 (#1), t2.C3 (#2), t2.C4 (#3), t2.C5 (#4)]        |
├── exchange type: Merge                                                                                   |
└── AggregateFinal                                                                                         |
    ├── output columns: [sum(t2."V1") (#6), t2.C1 (#0), t2.C2 (#1), t2.C3 (#2), t2.C4 (#3), t2.C5 (#4)]    |
    ├── group by: [C1, C2, C3, C4, C5]                                                                     |
    ├── aggregate functions: [sum(V1)]                                                                     |
    ├── estimated rows: 54596.00                                                                           |
    ├── cpu time: 233.770708ms                                                                             |
    ├── output rows: 27.28 thousand                                                                        |
    ├── output bytes: 2.74 MiB                                                                             |
    └── Exchange                                                                                           |
        ├── output columns: [sum(t2."V1") (#6), t2.C1 (#0), t2.C2 (#1), t2.C3 (#2), t2.C4 (#3), t2.C5 (#4)]|
        ├── exchange type: Hash(0, 1, 2, 3, 4)                                                             |
        └── AggregatePartial                                                                               |
            ├── group by: [C1, C2, C3, C4, C5]                                                             |
            ├── aggregate functions: [sum(V1)]                                                             |
            ├── estimated rows: 54596.00                                                                   |
            ├── cpu time: 16.261109ms                                                                      |
            └── TableScan                                                                                  |
                ├── table: default.default.t2                                                              |
                ├── output columns: [C1 (#0), C2 (#1), C3 (#2), C4 (#3), C5 (#4), V1 (#5)]                 |
                ├── read rows: 54596                                                                       |
                ├── read size: 577.68 KiB                                                                  |
                ├── partitions total: 1                                                                    |
                ├── partitions scanned: 1                                                                  |
                ├── pruning stats: [segments: <range pruning: 1 to 1>, blocks: <range pruning: 1 to 1>]    |
                ├── push downs: [filters: [], limit: NONE]                                                 |
                ├── estimated rows: 54596.00                                                               |
                ├── cpu time: 14.839977ms                                                                  |
                ├── wait time: 73.096679ms                                                                 |
                ├── output rows: 54.6 thousand                                                             |
                ├── output bytes: 7.61 MiB                                                                 |
                └── bytes scanned: 7.61 MiB                                                                |

How to Reproduce?

I need to attempt to reproduce this from scratch, loading the data from a CSV or something. Then I can upload a procedure to reproduce.

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

[sundy-li]

Can you check v1.2.659-nightly? There are some bugs in [1.2.654 , 1.2.658], so the releases are deleted.

[rad-pat]

Yes, will try

[rad-pat]

Can you check v1.2.659-nightly? There are some bugs in [1.2.654 , 1.2.658], so the releases are deleted.

Seems to still exist, slightly different circumstances perhaps. I need to figure out how to replicate exactly - will update when I know how.

[rad-pat]

Now if I insert with SELECT * FROM t1 or SELECT TOP 55000 * from t1 it works, but if I insert like below, I still get query not returning

INSERT INTO t2
("C1", "C2", "C3", "C4", "C5", "V1")
    SELECT CAST(t1."C1" AS TEXT)
            ,CAST(t1."C2" AS TEXT)
            ,CAST(t1."C3" AS TEXT)
            ,CAST(t1."C4" AS TEXT)
            ,CAST(t1."C5" AS TEXT)
            ,CAST(t1."V1" AS DECIMAL(38, 10))