sql: planColumns() is missing TableID information during a merge join

[yuzefovich]

If we add the following diff to row_description PG test

diff --git a/pkg/sql/pgwire/testdata/pgtest/row_description b/pkg/sql/pgwire/testdata/pgtest/row_description
index f18071d10c..8d40c5de7e 100644
--- a/pkg/sql/pgwire/testdata/pgtest/row_description
+++ b/pkg/sql/pgwire/testdata/pgtest/row_description
@@ -135,6 +135,35 @@ ReadyForQuery
 {"Type":"CommandComplete","CommandTag":"SELECT 1"}
 {"Type":"ReadyForQuery","TxStatus":"I"}
 
+send
+Query {"String": "SELECT a, tab1_a FROM tab2 INNER MERGE JOIN tab1 ON a = tab1_a WHERE a = 1"}
+----
+
+# With postgres we don't control the table OID.
+until ignore_table_oids noncrdb_only
+RowDescription
+----
+{"Type":"RowDescription","Fields":[{"Name":"a","TableOID":0,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"tab1_a","TableOID":0,"TableAttributeNumber":2,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]}
+
+until crdb_only
+RowDescription
+----
+{"Type":"RowDescription","Fields":[{"Name":"a","TableOID":52,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"tab1_a","TableOID":53,"TableAttributeNumber":2,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]}
+
+until ignore_table_oids
+ReadyForQuery
+----
+{"Type":"DataRow","Values":[{"text":"1"},{"text":"1"}]}
+{"Type":"CommandComplete","CommandTag":"SELECT 1"}
+{"Type":"ReadyForQuery","TxStatus":"I"}
+
+until ignore_table_oids
+ReadyForQuery
+----
+{"Type":"DataRow","Values":[{"text":"1"},{"text":"1"}]}
+{"Type":"CommandComplete","CommandTag":"SELECT 1"}
+{"Type":"ReadyForQuery","TxStatus":"I"}
+
 send
 Query {"String": "CREATE TABLE tab3 (a INT8 PRIMARY KEY, b CHAR(8))"}
 ----

it'll fail with

    --- FAIL: TestPGTest/row_description (0.23s)
        datadriven.go:75: 
            testdata/pgtest/row_description:170: RowDescription
            expected:
            {"Type":"RowDescription","Fields":[{"Name":"a","TableOID":52,"TableAttributeNumber":1,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"tab1_a","TableOID":53,"TableAttributeNumber":2,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]}
            
            found:
            {"Type":"RowDescription","Fields":[{"Name":"a","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0},{"Name":"tab1_a","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]}

For some reason we're losing some of the information about the columns returned from the query.

[otan]

This bug involves something around here:

cockroach/pkg/sql/conn_executor_exec.go

Lines 1011 to 1013 in 87f79da

	cols = planner.curPlan.main.planColumns()
	}
	if err := ex.initStatementResult(ctx, res, stmt.AST, cols); err != nil {

initStatementResult relies on planColumns() containing the correct table ID information, which then populates this result.

If you insert:

diff --git a/pkg/sql/conn_executor_exec.go b/pkg/sql/conn_executor_exec.go
index cc8146d786..7659323a2d 100644
--- a/pkg/sql/conn_executor_exec.go
+++ b/pkg/sql/conn_executor_exec.go
@@ -1010,6 +1010,9 @@ func (ex *connExecutor) dispatchToExecutionEngine(
        if stmt.AST.StatementReturnType() == tree.Rows {
                cols = planner.curPlan.main.planColumns()
        }
+       for _, col := range cols {
+               fmt.Printf("columns: %s %s\n", col.Name, col.TableID)
+       }
        if err := ex.initStatementResult(ctx, res, stmt.AST, cols); err != nil {
                res.SetError(err)
                return nil

you will see that the columns have no tableid attached:

columns: a %!s(descpb.ID=0)
columns: tab1_a %!s(descpb.ID=0)
  a | tab1_a
----+---------
(0 rows)

i think the queries team / optimiser should dig further here (as the information comes from the plan)