[YSQL][randgen] Query with join on inequality condition incorrectly returns rows with NULL in the key column

[mtakahar]

Jira Link: DB-9640

Description

• Reproducible on the latest master (7d7a636).

Test case

create table t1 (c1 int, c2 int);
insert into t1 values (null, 3);
create table t2 (pk int, c2 int, primary key (pk asc));
insert into t2 values (1, 3);

select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;

This query should not return any row but returned one:

yugabyte=# select version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2-YB-2.21.0.0-b0 on x86_64-apple-darwin22.6.0, compiled by Apple clang version 15.0.0 (clang-1500.1.0.2.5), 64-bit
(1 row)

yugabyte=# select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;
 c1 | pk 
----+----
    |  1
(1 row)

yugabyte=# explain select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Nested Loop  (cost=0.00..224.00 rows=333333 width=8)
   ->  Seq Scan on t1  (cost=0.00..100.00 rows=1000 width=4)
   ->  Index Scan using t2_pkey on t2  (cost=0.00..0.11 rows=1 width=4)
         Index Cond: (pk > t1.c1)
(4 rows)

PG15 results:

PG15 test# select version();
select version();
                                                      version                                                       
--------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 on x86_64-apple-darwin22.3.0, compiled by Apple clang version 14.0.0 (clang-1400.0.29.202), 64-bit
(1 row)

PG15 test# select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;
select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;
 c1 | pk 
----+----
(0 rows)

PG15 test# explain select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;
explain select t1.c1, t2.pk from t1 join t2 on t2.pk > t1.c1;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Nested Loop  (cost=0.15..47229.85 rows=1702533 width=8)
   ->  Seq Scan on t1  (cost=0.00..32.60 rows=2260 width=4)
   ->  Index Only Scan using t2_pkey on t2  (cost=0.15..13.35 rows=753 width=4)
         Index Cond: (pk > t1.c1)
(4 rows)

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.