sql/opt: incorrect query results with virtual table lookup semi join

[rafiss]

Describe the problem

There's a problem that seems to happen when a virtual table lookup semi join is planned. See below.

                              info
-----------------------------------------------------------------
  distribution: local
  vectorized: true

  • group (scalar)
  │
  └── • virtual table lookup join
      │ table: pg_class@pg_class_oid_idx
      │ equality: (attrelid) = (oid)
      │ pred: relname = 'b'
      │
      └── • virtual table lookup join (semi)
          │ table: pg_type@pg_type_oid_idx
          │ equality: (atttypid) = (oid)
          │ pred: typname = 'int8'
          │
          └── • filter
              │ filter: (NOT attnotnull) AND (attname = 'a_id')
              │
              └── • virtual table
                    table: pg_attribute@primary

To Reproduce

Start a fresh cluster, then:

-- setup
CREATE TABLE b (id INT, a_id INT);

root@localhost:26257/defaultdb> 
SELECT
  count(*)
FROM
  pg_class AS t INNER JOIN pg_attribute AS a ON t.oid = a.attrelid
WHERE
  a.attnotnull = 'f'
  AND a.attname = 'a_id'
  AND t.relname = 'b'
  AND a.atttypid
    IN (
        SELECT
          oid
        FROM
          pg_type
        WHERE
          typname = ANY (ARRAY['int8'])
      );
  count
---------
      0
(1 row)

If you force a hash join, then a count of 1 is returned.

root@localhost:26257/defaultdb> 
SELECT
  count(*)
FROM
  pg_class AS t
  INNER HASH JOIN pg_attribute AS a ON t.oid = a.attrelid
WHERE
  a.attnotnull = 'f'
  AND a.attname = 'a_id'
  AND t.relname = 'b'
  AND a.atttypid
    IN (
        SELECT
          oid
        FROM
          pg_type
        WHERE
          typname = ANY (ARRAY['int8'])
      );
  count
---------
      1
(1 row)

Expected behavior
The query should return a count of 1.

Environment:
Reproduced on v22.2.0-beta.2 and v22.1.8.

Jira issue: CRDB-21073
Epic: CRDB-23454

[rafiss]

I tried to step through a bit with the debugger. I found that the inner join on 'b' is probably not getting planned correctly. In (*joinPredicate).eval we can see the following onCond.

But when you step in and look at how the condition is evaluated, it appears that the left-hand side is wrong. It's trying to compare 'b' to 'int8'.

[knz]

Is this related to #88096?

[mgartner]

Let's aim to fix this by ~Feb 2023 to unblock some CLI improvements for 23.1.

[rafiss]

This is blocking this PR: #90649. The PR requires an index on pg_namespace(oid), but adding that index triggers this bug and causes this test to fail:

cockroach/pkg/sql/logictest/testdata/logic_test/orms

Lines 127 to 139 in 5632043

	# ActiveRecord 4.2.x query for checking if an index exists
	# Relies on OID IN tuple support
	query I
	SELECT count(*)
	FROM pg_class t
	INNER JOIN pg_index d ON t.oid = d.indrelid
	INNER JOIN pg_class i ON d.indexrelid = i.oid
	WHERE i.relkind = 'i'
	AND i.relname = 'b_idx'
	AND t.relname = 'b'
	AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)))
	----
	1