search

heterodb / pg-strom

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

[GPU Logic Bug] SELECT <agg_func> FROM <tables> JOIN <tables> JOIN <tables> Brings GPU Logic Bug #784

Closed qwebug closed 1 week ago

qwebug commented 3 weeks ago

Describe:

SELECT \ FROM \

JOIN \
JOIN \
brings different results, when using CPU-only configurations and GPU-used configurations.

SQL with CPU-only Config:

CREATE TABLE t0(c0 money);
CREATE TABLE t1(LIKE t0);
CREATE TABLE t2(LIKE t1);
CREATE TABLE t3(LIKE t0);
CREATE TABLE t4(c0 boolean);
INSERT INTO t0(c0) VALUES(CAST(0.1 AS MONEY));
INSERT INTO t1(c0) VALUES(CAST(0.0 AS MONEY));
INSERT INTO t4(c0) VALUES(false);
SET cpu_tuple_cost=0.1;
CREATE SCHEMA extensions;
CREATE EXTENSION pg_strom WITH SCHEMA extensions;
SET pg_strom.enabled=off;
SELECT BIT_OR(-1) FROM t0, t4 LEFT OUTER JOIN t1 ON t4.c0 LEFT OUTER JOIN t2 ON t4.c0;

Result:

 bit_or 
--------
     -1
(1 row)

SQL with GPU-used Config:

BEGIN;
SET LOCAL pg_strom.enabled=on;
SET LOCAL pg_strom.enable_gpuscan=on; 
SET LOCAL pg_strom.enable_gpuhashjoin=on; 
SET LOCAL pg_strom.enable_gpujoin=on; 
SET LOCAL pg_strom.enable_gpupreagg=on;
SELECT BIT_OR(-1) FROM t0, t4 LEFT OUTER JOIN t1 ON t4.c0 LEFT OUTER JOIN t2 ON t4.c0;
COMMIT;

Result:

 bit_or 
--------

(1 row)

Environment:

Pg-strom Version: commit b1f04e4042a8990ee9b21263f08365982b5495b5

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 2 weeks ago

That was a logic bug of CPU-Fallback JOIN. It does not handle LEFT OUTER JOIN case when input row has no matched tuple in the inner relation. Fixed at c8c8dd3b161301445fb712e93cb99a047abda65d.

hoge=# set pg_strom.enabled = off;
SET
hoge=# SELECT * FROM t0, t4 LEFT OUTER JOIN t1 ON t4.c0 LEFT OUTER JOIN t2 ON t4.c0;
  c0   | c0 | c0 | c0
-------+----+----+----
 $0.10 | f  |    |
(1 row)

hoge=# set pg_strom.enabled = on;
SET
hoge=# SELECT * FROM t0, t4 LEFT OUTER JOIN t1 ON t4.c0 LEFT OUTER JOIN t2 ON t4.c0;
  c0   | c0 | c0 | c0
-------+----+----+----
 $0.10 | f  |    |
(1 row)
qwebug commented 2 weeks ago

This problem is fixed, after my verification. Thanks for your work.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.