Cockroach: sql: incorrect column types after apply join on 19.1

[intech]

Describe the problem
Crash node on parse incorrect alias name in sub-query

To Reproduce
test table and data

create table table1
(
    column_1 text not null
        constraint table1_pk
            primary key,
    column_2 text not null
);
INSERT INTO public.table1 (column_1, column_2) VALUES ('a1', 'b1');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a10', 'b2');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a2', 'b1');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a3', 'b1');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a4', 'b2');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a5', 'b3');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a6', 'b2');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a7', 'b1');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a8', 'b3');
INSERT INTO public.table1 (column_1, column_2) VALUES ('a9', 'b3');

crash query:

SELECT t1.column_2,
       (SELECT COUNT(t1.column_1) FROM table1 t2 WHERE t1.column_2 = t2.column_2 GROUP BY t2.column_2) AS t2
FROM table1 t1
GROUP BY t1.column_2;

in sub-query COUNT(t1.column_1), right t2

valid query:

SELECT t1.column_2,
       (SELECT COUNT(t2.column_1) FROM table1 t2 WHERE t1.column_2 = t2.column_2 GROUP BY t2.column_2) AS t2
FROM table1 t1
GROUP BY t1.column_2;

Expected behavior
Reply with error sql syntax

Additional data / screenshots

* ERROR: [n1,client=x.x.x.x:3600,user=root] a SQL panic has occurred while executing "SELECT t1.column_2, (SELECT count(t1.column_1) FROM table1 AS t2 WHERE t1.column_2 = t2.column_2 GROUP BY t2.column_2) AS t2 FROM table1 AS t1 GROUP BY t1.column_2": invalid datum type given: int, expected unknown
*
*
* ERROR: [n1,client=x.x.x.x:3600,user=root] a panic has occurred!
*
panic while executing 1 statements: SELECT _._, (SELECT count(_._) FROM _ AS _ WHERE _._ = _._ GROUP BY _._) AS _ FROM _ AS _ GROUP BY _._

goroutine 8149 [running]:
runtime/debug.Stack(0x3a4a5c0, 0xc005493980, 0xc000000003)
    /usr/local/go/src/runtime/debug/stack.go:24 +0xa7
github.com/cockroachdb/cockroach/pkg/util/log.ReportPanic(0x3a4a5c0, 0xc005493980, 0xc00047d300, 0x313d740, 0xc007587710, 0x1)
    /go/src/github.com/cockroachdb/cockroach/pkg/util/log/crash_reporting.go:226 +0xa6
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).closeWrapper(0xc0085a0000, 0x3a4a5c0, 0xc005493980, 0x2dcdf80, 0xc00520bf70)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:715 +0x2dd
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1(0xc0085a0000, 0x3a4a5c0, 0xc005493980)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:434 +0x61
panic(0x2dcdf80, 0xc00520bf70)
    /usr/local/go/src/runtime/panic.go:513 +0x1b9
github.com/cockroachdb/cockroach/pkg/sql/sqlbase.DatumToEncDatum(0xd, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/sqlbase/encoded_datum.go:171 +0x25d
github.com/cockroachdb/cockroach/pkg/sql.(*planNodeToRowSource).Next(0xc00ac5f400, 0xc0085a0350, 0x3a4b740, 0xc0064b7000, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:186 +0x54e
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun.Run(0x3a4a680, 0xc0082b2ea0, 0x3a56180, 0xc00ac5f400, 0x3a3b9c0, 0xc0074e1880)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/base.go:174 +0x35
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun.(*ProcessorBase).Run(0xc00ac5f400, 0x3a4a680, 0xc0082b2ea0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/processors.go:801 +0x92
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun.(*Flow).Run(0xc00091e3c0, 0x3a4a680, 0xc0082b2ea0, 0x34363c8, 0x0, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/flow.go:626 +0x1e9
github.com/cockroachdb/cockroach/pkg/sql.(*DistSQLPlanner).Run(0xc0004492c0, 0xc00738aa20, 0xc008f661b0, 0xc005322838, 0xc0056b7080, 0xc0085a0410, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:252 +0x8a0
github.com/cockroachdb/cockroach/pkg/sql.(*DistSQLPlanner).PlanAndRun(0xc0004492c0, 0x3a4a680, 0xc0082b24b0, 0xc0085a0410, 0xc00738aa20, 0xc008f661b0, 0x3a4c300, 0xc008f70360, 0xc0056b7080)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:839 +0x227
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execWithDistSQLEngine(0xc0085a0000, 0x3a4a680, 0xc0082b24b0, 0xc0085a0350, 0x3, 0x7f6d65e956b0, 0xc00911cf20, 0x0, 0x0, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1125 +0x283
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine(0xc0085a0000, 0x3a4a680, 0xc0082b24b0, 0xc0085a0350, 0x7f6d65e956b0, 0xc00911cf20, 0x0, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:961 +0x658
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState(0xc0085a0000, 0x3a4a680, 0xc0082b24b0, 0x3a50300, 0xc00a92cf00, 0xc00641a68f, 0x9d, 0x0, 0xc006343a90, 0x2, ...)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:456 +0xdd0
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt(0xc0085a0000, 0x3a4a680, 0xc0082b24b0, 0x3a50300, 0xc00a92cf00, 0xc00641a68f, 0x9d, 0x0, 0xc006343a90, 0x2, ...)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:102 +0x610
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run(0xc0085a0000, 0x3a4a5c0, 0xc005493980, 0xc0008930b8, 0x5400, 0x15000, 0xc000893150, 0xc005c64e90, 0x0, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1246 +0x141c
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn(0xc000c882c0, 0x3a4a5c0, 0xc005493980, 0xc0085a0000, 0x5400, 0x15000, 0xc000893150, 0xc005c64e90, 0x0, 0x0)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:436 +0xce
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1(0xc008652981, 0xc0075abaa0, 0x3a4a5c0, 0xc005493980, 0xc005c64e90, 0xc000c882c0, 0xc007a36400, 0x3a4e300, 0xc0075aba80, 0xc007521ce0, ...)
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:580 +0x21f
created by github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync
    /go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:515 +0x17b

*
* ERROR: [n1,client=x.x.x.x:3600,user=root] Reported as error 697a515b68ea4e1db3d84020e41dd837
*

Environment:

• CockroachDB version: CCL v19.1.4 @ 2019/08/06 15:34:13 (go1.11.6)
• Server OS: Debian 4.9.168-1+deb9u2 (2019-05-13) x86_64 GNU/Linux
• Docker version 19.03.1, build 74b1e89
• Client app JDBC Postgresql (DataGrip)

[intech]

related #40806 and #40804

[yuzefovich]

@intech Thanks for the report!

I first tried the repro on the current master, and the query succeeded. Then I ran it on v19.1.4 and hit the panic. Then I ran it on the current release-19.1 branch and also hit the panic.

So it appears that there is some feature development that fixed the issue on 19.2 branch, but those changes were not backported onto 19.1. We'll need to dig in deeper.

[yuzefovich]

It appears that applyJoinNode.columns has {string, int, unknown} types, but {string, int, int} are expected. The last column corresponds to the count subquery result, so I wonder whether there has been some work on the optimizer that could have resolved this problem? cc @rytaft

[rytaft]

I used git bisect to find the PR from May 20th: https://github.com/cockroachdb/cockroach/pull/37597

@jordanlewis can you please backport this PR to 19.1? Thanks!

[jordanlewis]

Thanks @rytaft for the bisect!

[jordanlewis]

Closing this - #40829 is the backport.