heterodb / pg-strom

PG-Strom - Master development repository
http://heterodb.github.io/pg-strom/
Other
1.3k stars 162 forks source link

[GPU Logic Bug] SELECT * FROM <table> FULL OUTER JOIN <table> ON FALSE WHERE <column> ISNULL Brings GPU Logic Bug #827

Closed qwebug closed 1 week ago

qwebug commented 1 month ago

Describe:

SELECT * FROM \

FULL OUTER JOIN \
ON FALSE WHERE \ ISNULL brings different results, when using CPU-only configurations and GPU-used configurations.

SQL with CPU-only Config:

CREATE TABLE t0(c0 TEXT);
CREATE TABLE t1(LIKE t0);
INSERT INTO t0(c0) VALUES('x');
INSERT INTO t1(c0) VALUES('1');
CREATE SCHEMA extensions;
CREATE EXTENSION pg_strom WITH SCHEMA extensions;
SET pg_strom.enabled=off;
SELECT * FROM t1 FULL OUTER JOIN t0 ON FALSE WHERE (t1.c0) ISNULL;

Result:

 c0 | c0 
----+----
    | x
(1 row)

SQL with GPU-used Config:

SET pg_strom.enabled=on;
SELECT * FROM t1 FULL OUTER JOIN t0 ON FALSE WHERE (t1.c0) ISNULL;

Result:

 c0 | c0 
----+----
(0 rows)

Environment:

Pg-strom Version: commit 9765660eba4fab7e4e9378c667e6bd565da83ffd

PostgreSQL Version: 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

CUDA Version: 12.2

NVIDIA Driver Version: 535.171.04

kaigai commented 1 month ago

cb2c4e7a1a6a39e6d81e0bc8dceef9c8e54f0a35 fixed this problem. Thanks for your report.

postgres=# EXPLAIN SELECT t1.c0 t1_c0, t0.c0 t0_c0 FROM t1 FULL OUTER JOIN t0 ON FALSE WHERE (t1.c0) ISNULL;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Custom Scan (GpuJoin) on t1  (cost=150.80..160.87 rows=7 width=64)
   GPU Projection: t1.c0, t0.c0
   GPU Full Outer Join Quals [1]: false, [(t1.c0 IS NULL)] ... [nrows: 1360 -> 7]
   GPU-Direct SQL: enabled (N=2,GPU0,1)
   ->  Seq Scan on t0  (cost=0.00..23.60 rows=1360 width=32)
(5 rows)

postgres=# SELECT t1.c0 t1_c0, t0.c0 t0_c0 FROM t1 FULL OUTER JOIN t0 ON FALSE WHERE (t1.c0) ISNULL;
 t1_c0 | t0_c0
-------+-------
       | x
(1 row)