emerkfu opened a new issue, #9545: URL: https://github.com/apache/incubator-doris/issues/9545
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Version 0.15.3 ### What's Wrong? -- The following is the DDL to create the test table. DROP TABLE test.`rownumber_test_00`; CREATE TABLE test.`rownumber_test_00` ( `code` varchar(200) NULL, `name` char(255) NULL ) ENGINE=OLAP UNIQUE KEY(`code`) COMMENT "rownumber_test_00" DISTRIBUTED BY HASH(`code`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ); DROP TABLE test.`rownumber_test_01`; CREATE TABLE test.`rownumber_test_01` ( `id` varchar(128) NOT NULL, `outer_id` varchar(128) NOT NULL ) ENGINE=OLAP UNIQUE KEY(`id`, `outer_id`) COMMENT "rownumber_test_01" DISTRIBUTED BY HASH(`id`, `outer_id`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ); DROP TABLE test.`rownumber_test_02`; CREATE TABLE test.`rownumber_test_02` ( `id` varchar(128) NOT NULL, `refresh_token_expiration` datetime NOT NULL , `client_actor_id` varchar(128) NULL, `service_actor_id` varchar(128) NULL, `client_application` varchar(128) NOT NULL, `service_application` varchar(128) NOT NULL, `is_deleted_flg` int(11) NULL DEFAULT "0" ) ENGINE=OLAP UNIQUE KEY(`id`) COMMENT "rownumber_test_02" DISTRIBUTED BY HASH(`id`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ); DROP TABLE test.`rownumber_test_03`; CREATE TABLE test.`rownumber_test_03` ( `id` varchar(1020) NOT NULL, `token_id` varchar(1020) NULL, `name` varchar(1020) NULL, `is_deleted_flg` int(11) NULL DEFAULT "0" ) ENGINE=OLAP UNIQUE KEY(`id`) COMMENT "rownumber_test_03" DISTRIBUTED BY HASH(`id`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ); --------------------------------------------------------- -- The following is the SQL that will report an error when executing insert select. -- If only the select part is executed, no error will be reported. -- However, when the complete insert select SQL is executed, an error will occur. -- The above scenario can be reproduced even if there is no data in the test table. insert into test.`rownumber_test_00` select t0.id, t1.name from test.`rownumber_test_01` t0 left join (select t.client_actor_id, t.name from (select t1.client_actor_id, t2.name, row_number()OVER(PARTITION BY t1.client_actor_id,t1.service_actor_id,t1.service_application,t2.name ORDER BY t1.refresh_token_expiration desc) AS rn from test.`rownumber_test_02` t1 left join test.`rownumber_test_03` t2 on t1.id = t2.token_id and t2.is_deleted_flg=0 where t1.is_deleted_flg=0) t where t.rn = 1 ) t1 on t0.id= t1.client_actor_id ; --------------------------------------------------------- The following content is the error content. org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1064] [42000]: errCode = 2, detailMessage = can't support at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:509) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:440) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:168) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:427) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:812) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3181) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:168) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:119) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4514) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLSyntaxErrorException: errCode = 2, detailMessage = can't support at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:768) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:653) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:327) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130) ... 12 more The following is Explain String . Explain String | ---------------------------------------------------------------------------------------------------------------------------------------| PLAN FRAGMENT 0 | OUTPUT EXPRS:`t0`.`id` | `t2`.`name` | 0 | PARTITION: HASH_PARTITIONED: `default_cluster:test`.`rownumber_test_01`.`id`, `default_cluster:test`.`rownumber_test_01`.`outer_id` | | OLAP TABLE SINK | TUPLE ID: 0 | RANDOM | | 7:HASH JOIN | | join op: LEFT OUTER JOIN (BROADCAST) | | hash predicates: | | colocate: false, reason: The src data has been redistributed | | equal join conjunct: `t0`.`id` = <slot 25> <slot 6> | | cardinality=0 | | tuple ids: 1 9N 8N | | | |----10:EXCHANGE | | tuple ids: 9 8 | | | 0:OlapScanNode | TABLE: rownumber_test_01 | PREAGGREGATION: OFF. Reason: No AggregateInfo | PREDICATES: `t0`.`__DORIS_DELETE_SIGN__` = 0 | partitions=0/1 | rollup: null | tabletRatio=0/0 | tabletList= | cardinality=0 | avgRowSize=17.0 | numNodes=1 | tuple ids: 1 | | PLAN FRAGMENT 1 | OUTPUT EXPRS: | PARTITION: HASH_PARTITIONED: `t1`.`client_actor_id`, `t1`.`service_actor_id`, `t1`.`service_application`, `t2`.`name` | | STREAM DATA SINK | EXCHANGE ID: 10 | UNPARTITIONED | | 6:SELECT | | predicates: <slot 23> = 1, <slot 23> = 1 | | tuple ids: 9 8 | | | 5:ANALYTIC | | functions: [, row_number(), ] | | partition by: `t1`.`client_actor_id`, `t1`.`service_actor_id`, `t1`.`service_application`, `t2`.`name` | | order by: <slot 28> <slot 10> DESC NULLS LAST | | window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | | tuple ids: 9 8 | | | 4:SORT | | order by: <slot 25> <slot 6> ASC, <slot 26> <slot 8> ASC, <slot 27> <slot 9> ASC, <slot 33> <slot 7> ASC, <slot 28> <slot 10> DESC| | offset: 0 | | tuple ids: 9 | | | 9:EXCHANGE | tuple ids: 2 3N | | PLAN FRAGMENT 2 | OUTPUT EXPRS: | PARTITION: HASH_PARTITIONED: `default_cluster:test`.`rownumber_test_02`.`id` | | STREAM DATA SINK | EXCHANGE ID: 09 | HASH_PARTITIONED: `t1`.`client_actor_id`, `t1`.`service_actor_id`, `t1`.`service_application`, `t2`.`name` | | 3:HASH JOIN | | join op: LEFT OUTER JOIN (BROADCAST) | | hash predicates: | | colocate: false, reason: Tables are not in the same group | | equal join conjunct: `t1`.`id` = `t2`.`token_id` | | cardinality=0 | | tuple ids: 2 3N | | | |----8:EXCHANGE | | tuple ids: 3 | | | 1:OlapScanNode | TABLE: rownumber_test_02 | PREAGGREGATION: OFF. Reason: No AggregateInfo | PREDICATES: `t1`.`is_deleted_flg` = 0, `t1`.`__DORIS_DELETE_SIGN__` = 0 | partitions=0/1 | rollup: null | tabletRatio=0/0 | tabletList= | cardinality=0 | avgRowSize=85.0 | numNodes=1 | tuple ids: 2 | | PLAN FRAGMENT 3 | OUTPUT EXPRS: | PARTITION: HASH_PARTITIONED: `default_cluster:test`.`rownumber_test_03`.`id` | | STREAM DATA SINK | EXCHANGE ID: 08 | UNPARTITIONED | | 2:OlapScanNode | TABLE: rownumber_test_03 | PREAGGREGATION: OFF. Reason: null | PREDICATES: `t2`.`is_deleted_flg` = 0, `t2`.`__DORIS_DELETE_SIGN__` = 0 | partitions=0/1 | rollup: null | tabletRatio=0/0 | tabletList= | cardinality=0 | avgRowSize=37.0 | numNodes=1 | tuple ids: 3 | | Tuples: | TupleDescriptor{id=0, tbl=null, byteSize=48, materialized=true} | SlotDescriptor{id=0, col=code, type=VARCHAR(*)} | parent=0 | materialized=true | byteSize=16 | byteOffset=16 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=1 | | SlotDescriptor{id=1, col=name, type=CHAR(*)} | parent=0 | materialized=true | byteSize=16 | byteOffset=32 | nullIndicatorByte=0 | nullIndicatorBit=1 | slotIdx=2 | | SlotDescriptor{id=2, col=__DORIS_DELETE_SIGN__, type=TINYINT} | parent=0 | materialized=true | byteSize=1 | byteOffset=1 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=0 | | | TupleDescriptor{id=1, tbl=rownumber_test_01, byteSize=32, materialized=true} | SlotDescriptor{id=19, col=id, type=VARCHAR(*)} | parent=1 | materialized=true | byteSize=16 | byteOffset=16 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=1 | | SlotDescriptor{id=20, col=__DORIS_DELETE_SIGN__, type=TINYINT} | parent=1 | materialized=true | byteSize=1 | byteOffset=0 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=0 | | | TupleDescriptor{id=2, tbl=rownumber_test_02, byteSize=96, materialized=true} | SlotDescriptor{id=3, col=id, type=VARCHAR(*)} | parent=2 | materialized=true | byteSize=16 | byteOffset=16 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=2 | | SlotDescriptor{id=6, col=client_actor_id, type=VARCHAR(*)} | parent=2 | materialized=true | byteSize=16 | byteOffset=32 | nullIndicatorByte=0 | nullIndicatorBit=1 | slotIdx=3 | | SlotDescriptor{id=8, col=service_actor_id, type=VARCHAR(*)} | parent=2 | materialized=true | byteSize=16 | byteOffset=48 | nullIndicatorByte=0 | nullIndicatorBit=2 | slotIdx=4 | | SlotDescriptor{id=9, col=service_application, type=VARCHAR(*)} | parent=2 | materialized=true | byteSize=16 | byteOffset=64 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=5 | | SlotDescriptor{id=10, col=refresh_token_expiration, type=DATETIME} | parent=2 | materialized=true | byteSize=16 | byteOffset=80 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=6 | | SlotDescriptor{id=11, col=is_deleted_flg, type=INT} | parent=2 | materialized=true | byteSize=4 | byteOffset=4 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=1 | | SlotDescriptor{id=21, col=__DORIS_DELETE_SIGN__, type=TINYINT} | parent=2 | materialized=true | byteSize=1 | byteOffset=1 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=0 | | | TupleDescriptor{id=3, tbl=rownumber_test_03, byteSize=48, materialized=true} | SlotDescriptor{id=4, col=token_id, type=VARCHAR(*)} | parent=3 | materialized=true | byteSize=16 | byteOffset=16 | nullIndicatorByte=0 | nullIndicatorBit=1 | slotIdx=2 | | SlotDescriptor{id=5, col=is_deleted_flg, type=INT} | parent=3 | materialized=true | byteSize=4 | byteOffset=4 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=1 | | SlotDescriptor{id=7, col=name, type=VARCHAR(*)} | parent=3 | materialized=true | byteSize=16 | byteOffset=32 | nullIndicatorByte=0 | nullIndicatorBit=2 | slotIdx=3 | | SlotDescriptor{id=22, col=__DORIS_DELETE_SIGN__, type=TINYINT} | parent=3 | materialized=true | byteSize=1 | byteOffset=1 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=0 | | | TupleDescriptor{id=4, tbl=null, byteSize=8, materialized=false} | SlotDescriptor{id=12, col=null, type=BIGINT} | parent=4 | materialized=true | byteSize=8 | byteOffset=0 | nullIndicatorByte=0 | nullIndicatorBit=-1 | slotIdx=0 | | | TupleDescriptor{id=5, tbl=null, byteSize=16, materialized=false} | SlotDescriptor{id=13, col=null, type=BIGINT} | parent=5 | materialized=true | byteSize=8 | byteOffset=8 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | | TupleDescriptor{id=6, tbl=t, byteSize=0, materialized=false} | SlotDescriptor{id=14, col=client_actor_id, type=VARCHAR(*)} | parent=6 | materialized=false | byteSize=0 | byteOffset=-1 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | SlotDescriptor{id=15, col=name, type=VARCHAR(*)} | parent=6 | materialized=false | byteSize=0 | byteOffset=-1 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | SlotDescriptor{id=16, col=rn, type=BIGINT} | parent=6 | materialized=false | byteSize=0 | byteOffset=-1 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | | TupleDescriptor{id=7, tbl=t1, byteSize=0, materialized=false} | SlotDescriptor{id=17, col=client_actor_id, type=VARCHAR(*)} | parent=7 | materialized=false | byteSize=0 | byteOffset=-1 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | SlotDescriptor{id=18, col=name, type=VARCHAR(*)} | parent=7 | materialized=false | byteSize=0 | byteOffset=-1 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | | TupleDescriptor{id=8, tbl=null, byteSize=16, materialized=true} | SlotDescriptor{id=23, col=null, type=BIGINT} | parent=8 | materialized=true | byteSize=8 | byteOffset=8 | nullIndicatorByte=0 | nullIndicatorBit=0 | slotIdx=0 | | | TupleDescriptor{id=9, tbl=null, byteSize=208, materialized=true} | SlotDescriptor{id=24, col=id, type=VARCHAR(*)} | parent=9 | materialized=true | byteSize=16 | byteOffset=16 | nullIndicatorByte=0 | nullIndicatorBit=4 | slotIdx=4 | | SlotDescriptor{id=25, col=client_actor_id, type=VARCHAR(*)} | parent=9 | materialized=true | byteSize=16 | byteOffset=32 | nullIndicatorByte=0 | nullIndicatorBit=5 | slotIdx=5 | | SlotDescriptor{id=26, col=service_actor_id, type=VARCHAR(*)} | parent=9 | materialized=true | byteSize=16 | byteOffset=48 | nullIndicatorByte=0 | nullIndicatorBit=6 | slotIdx=6 | | SlotDescriptor{id=27, col=service_application, type=VARCHAR(*)} | parent=9 | materialized=true | byteSize=16 | byteOffset=64 | nullIndicatorByte=0 | nullIndicatorBit=7 | slotIdx=7 | | SlotDescriptor{id=28, col=refresh_token_expiration, type=DATETIME} | parent=9 | materialized=true | byteSize=16 | byteOffset=80 | nullIndicatorByte=1 | nullIndicatorBit=0 | slotIdx=8 | | SlotDescriptor{id=29, col=is_deleted_flg, type=INT} | parent=9 | materialized=true | byteSize=4 | byteOffset=4 | nullIndicatorByte=0 | nullIndicatorBit=2 | slotIdx=2 | ### What You Expected? Can a subquery containing row_number be joined? ### How to Reproduce? _No response_ ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
