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 <column> FROM <table> JOIN <table> Brings GPU Logic Bug #792

Closed qwebug closed 1 week ago

qwebug commented 2 weeks ago

Describe:

(1)CREATE INDEX \ ON \

USING BTREE(\ DESC) (2)SELECT \ FROM \
JOIN \
brings different results, when using CPU-only configurations and GPU-used configurations.

SQL with CPU-only Config:

CREATE TABLE t1(c0 money, c1 inet);
CREATE TABLE t5(LIKE t1);
INSERT INTO t1(c0) VALUES((4.2E8)::MONEY), ((0.01)::MONEY), ((0.3)::MONEY);
INSERT INTO t1(c1, c0) VALUES('210.81.4.34', CAST(0.3 AS MONEY));
CREATE INDEX i0 ON t1 USING BTREE(c1 DESC);
INSERT INTO t1(c0) VALUES((0.6)::MONEY), ((4.1E8)::MONEY), ((0.5)::MONEY);
INSERT INTO t5(c0) VALUES((0.4)::MONEY), (CAST(0.8 AS MONEY)), (CAST(0.9 AS MONEY));
CREATE SCHEMA extensions;
CREATE EXTENSION pg_strom WITH SCHEMA extensions;
SET pg_strom.enabled=off;
SELECT t1.c1 FROM  t1 LEFT OUTER JOIN t5 ON (t1.c1) BETWEEN SYMMETRIC (t1.c1) AND ('22.212.70.183');

Result:

     c1      
-------------

 210.81.4.34
 210.81.4.34
 210.81.4.34

(9 rows)

SQL with GPU-used Config:

BEGIN;
SET LOCAL pg_strom.enabled=on;
SET LOCAL pg_strom.enable_gpuscan=on; 
SET LOCAL pg_strom.enable_gpujoin=on; 
SET LOCAL pg_strom.enable_gpupreagg=on; 
SELECT t1.c1 FROM t1 LEFT OUTER JOIN t5 ON (t1.c1) BETWEEN SYMMETRIC (t1.c1) AND ('22.212.70.183');
COMMIT;

Result:

     c1      
-------------
 210.81.4.34
 210.81.4.34
 210.81.4.34

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

I could not reproduce the problem after 45a97a1be969b490d8354b21bad43eb92168af47. It might be same problem of #791.

By 3d3ef37b34a50e9ab409ef27c233a5a83747242c

hoge=# SELECT t1.c1 FROM  t1 LEFT OUTER JOIN t5 ON (t1.c1) BETWEEN SYMMETRIC (t1.c1) AND ('22.212.70.183');
     c1
-------------
 210.81.4.34
 210.81.4.34
 210.81.4.34

(6 rows)
By 45a97a1be969b490d8354b21bad43eb92168af47 (latest)

hoge=# SELECT t1.c1 FROM  t1 LEFT OUTER JOIN t5 ON (t1.c1) BETWEEN SYMMETRIC (t1.c1) AND ('22.212.70.183');
     c1
-------------
 210.81.4.34
 210.81.4.34
 210.81.4.34

(9 rows)
qwebug commented 2 weeks ago

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

qwebug commented 1 week ago

I could not reproduce the problem after 45a97a1be969b490d8354b21bad43eb92168af47. It might be same problem of #791.

By 3d3ef37b34a50e9ab409ef27c233a5a83747242c

hoge=# SELECT t1.c1 FROM  t1 LEFT OUTER JOIN t5 ON (t1.c1) BETWEEN SYMMETRIC (t1.c1) AND ('22.212.70.183');
     c1
-------------
 210.81.4.34
 210.81.4.34
 210.81.4.34

(6 rows)
By 45a97a1be969b490d8354b21bad43eb92168af47 (latest)

hoge=# SELECT t1.c1 FROM  t1 LEFT OUTER JOIN t5 ON (t1.c1) BETWEEN SYMMETRIC (t1.c1) AND ('22.212.70.183');
     c1
-------------
 210.81.4.34
 210.81.4.34
 210.81.4.34

(9 rows)

I think this GPU logic bug is different from the one in #791 because there is more "CREATE INDEX \ ON \

USING BTREE(\ DESC)". And after removing "CREATE INDEX \ ON \
USING BTREE(\ DESC)", the GPU logic bug cannot be reproduced.

kaigai commented 6 days ago

Even if btree-index is built on the tables, GpuJoin does not use the index. So, order of the output is not guaranteed.

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