search

StarRocks / starrocks

StarRocks, a Linux Foundation project, is a next-generation sub-second MPP OLAP database for full analytics scenarios, including multi-dimensional analytics, real-time analytics, and ad-hoc queries.
https://starrocks.io
Apache License 2.0
8.67k stars 1.75k forks source link

【outer join】right join/full join query unknown error #10003

Closed lvchenyang-maker closed 2 years ago

lvchenyang-maker commented 2 years ago

Steps to reproduce the behavior (Required)

  1. CREATE TABLE '...' 
    CREATE TABLE `join_basic_t1` ( `t1_c1`  date NOT NULL, `t1_c2`  datetime NOT NULL, `t1_c3`  char(20) NOT NULL, `t1_c4`  varchar(20) NOT NULL, `t1_c5`  boolean NOT NULL, `t1_c6`  tinyint NOT NULL, `t1_c7`  smallint NOT NULL, `t1_c8`  int NOT NULL, `t1_c9`  bigint NOT NULL, `t1_c10` largeint NOT NULL, `t1_c11` float NOT NULL, `t1_c12` double NOT NULL, `t1_c13` decimalv2(27,9) NOT NULL ) ENGINE=OLAP DUPLICATE KEY(`t1_c1`, `t1_c2`, `t1_c3`, `t1_c4`, `t1_c5`) COMMENT "OLAP" DISTRIBUTED BY HASH(`t1_c1`, `t1_c2`, `t1_c3`, `t1_c4`, `t1_c5`) BUCKETS 3 PROPERTIES ( "replication_num" = "3", "storage_format" = "v2" );
CREATE TABLE `join_basic_t2` ( `t2_c1`  date NOT NULL, `t2_c2`  datetime NOT NULL, `t2_c3`  char(20) NOT NULL, `t2_c4`  varchar(20) NOT NULL, `t2_c5`  boolean NOT NULL, `t2_c6`  tinyint NOT NULL, `t2_c7`  smallint NOT NULL, `t2_c8`  int NOT NULL, `t2_c9`  bigint NOT NULL, `t2_c10` largeint NOT NULL, `t2_c11` float NOT NULL, `t2_c12` double NOT NULL, `t2_c13` decimalv2(27,9) NOT NULL ) ENGINE=OLAP DUPLICATE KEY(`t2_c1`, `t2_c2`, `t2_c3`, `t2_c4`, `t2_c5`) COMMENT "OLAP" DISTRIBUTED BY HASH(`t2_c1`, `t2_c2`, `t2_c3`, `t2_c4`, `t2_c5`) BUCKETS 3 PROPERTIES ( "replication_num" = "3", "storage_format" = "v2" );
  2. INSERT INTO '....' 
    insert into join_basic_t1 values ('2020-06-20', '2020-06-20 00:00:00', 'beijing0', 'haidian0', '0', -120, -32760, -2147483640, -9223372036854775800, -18446744073709551610, -3.0, -3.1, '-3.140'), ('2020-06-21', '2020-06-21 00:00:01', 'beijing1', 'haidian1', '1', -121, -32761, -2147483641, -9223372036854775801, -18446744073709551611, -3.1, -3.11, '-3.141'), ('2020-06-22', '2020-06-22 00:00:02', 'beijing2', 'haidian2', '1', -122, -32762, -2147483642, -9223372036854775802, -18446744073709551612, -3.2, -3.12, '-3.142'), ('2020-06-23', '2020-06-23 00:00:03', 'beijing3', 'haidian3', '0', -123, -32763, -2147483643, -9223372036854775803, -18446744073709551613, -3.3, -3.13, '-3.143'), ('2020-06-24', '2020-06-24 00:00:04', 'beijing4', 'haidian4', '0', -124, -32764, -2147483644, -9223372036854775804, -18446744073709551614, -3.4, -3.14, '-3.144');
insert into join_basic_t2 values ('2020-06-21', '2020-06-21 00:00:01', 'beijing1', 'haidian1', '1', -121, -32761, -2147483641, -9223372036854775801, -18446744073709551611, -3.1, -3.11, '-3.141'), ('2020-06-22', '2020-06-22 00:00:02', 'beijing2', 'haidian2', '1', -122, -32762, -2147483642, -9223372036854775802, -18446744073709551612, -3.2, -3.12, '-3.142'), ('2020-06-25', '2020-06-25 00:00:05', 'beijing5', 'haidian5', '0', -125, -32765, -2147483645, -9223372036854775805, -18446744073709551615, -3.5, -3.15, '-3.145'), ('2020-06-26', '2020-06-26 00:00:06', 'beijing6', 'haidian6', '0', -126, -32766, -2147483646, -9223372036854775806, -18446744073709551616, -3.6, -3.16, '-3.146'), ('2020-06-27', '2020-06-27 00:00:07', 'beijing7', 'haidian7', '0', -127, -32767, -2147483647, -9223372036854775807, -18446744073709551617, -3.7, -3.17, '-3.147');
  3. SELECT '....' 
    select * from (select * from join_basic_t1 as t1 right join join_basic_t2 as t2 on t1.t1_c1<t2.t2_c1)as t3 order by t1_c1 asc, t2_c1 asc;
    2022-08-15 18:33:53,969 WARN (starrocks-mysql-nio-pool-1|365) [StmtExecutor.execute():491] execute Exception, sql select * from (select * from join_basic_t1 as t1 right join join_basic_t2 as t2 on t1.t1_c1<t2.t2_c1)as t3 order by t1_c1 asc, t2_c1 asc
java.lang.IllegalStateException: null
    at com.google.common.base.Preconditions.checkState(Preconditions.java:494) ~[spark-dpp-1.0.0.jar:?]
    at com.starrocks.sql.optimizer.base.HashDistributionDesc.<init>(HashDistributionDesc.java:36) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.PropertyDeriverBase.createShuffleJoinRequiredProperties(PropertyDeriverBase.java:95) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.PropertyDeriverBase.computeShuffleJoinRequiredProperties(PropertyDeriverBase.java:31) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.RequiredPropertyDeriver.visitPhysicalHashJoin(RequiredPropertyDeriver.java:85) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.RequiredPropertyDeriver.visitPhysicalHashJoin(RequiredPropertyDeriver.java:38) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.operator.physical.PhysicalHashJoinOperator.accept(PhysicalHashJoinOperator.java:28) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.RequiredPropertyDeriver.getRequiredProps(RequiredPropertyDeriver.java:50) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.task.EnforceAndCostTask.initRequiredProperties(EnforceAndCostTask.java:203) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.task.EnforceAndCostTask.execute(EnforceAndCostTask.java:103) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.task.SeriallyTaskScheduler.executeTasks(SeriallyTaskScheduler.java:42) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.Optimizer.memoOptimize(Optimizer.java:166) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.optimizer.Optimizer.optimize(Optimizer.java:105) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.StatementPlanner.createQueryPlan(StatementPlanner.java:89) ~[starrocks-fe.jar:?]
    at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:54) ~[starrocks-fe.jar:?]
    at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:348) ~[starrocks-fe.jar:?]
    at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:316) ~[starrocks-fe.jar:?]
    at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:429) ~[starrocks-fe.jar:?]
    at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:667) ~[starrocks-fe.jar:?]
    at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_252]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_252]
    at java.lang.Thread.run(Thread.java:748) [?:1.8.0_252]

    Expected behavior (Required)

    return the correct result

    Real behavior (Required)

    ERROR 1064 (HY000): Unknown error image

StarRocks version (Required)

murphyatwork commented 2 years ago

fixed.