search

heterodb / pg-strom

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

[VTJ-JP]Subquery does not return correct results. #820

Closed sakaik closed 2 weeks ago

sakaik commented 3 months ago

Summary

　サブクエリを含む（やや複雑な）クエリが、PG-Strom有効時に正しい結果を返さない（結果件数が少なくなる）

Data (作成クエリは末尾に)

nikkei=# SELECT * FROM t1;
  keycode  |        area        |      area_clip       |       area_rate        
-----------+--------------------+----------------------+------------------------
 523750891 | 263203.78272429225 |   0.1169552621686655 |  4.443525125593538e-07
 523837491 |  263875.8959045195 | 0.029132181955639557 | 1.1040107265492984e-07
 523801962 | 264638.23138546856 |    120360.5266555015 |     0.4548115592572335
(3 rows)

nikkei=# SELECT * FROM t2;
  keycode  |        wariai        |      target_jinko      
-----------+----------------------+------------------------
 523801962 |   0.4547999759967326 |       72.3131961834805
 523661381 | 6.40000052975156e-08 | 2.8224002336204377e-05
(2 rows)

Problems: 問題のクエリと結果

OUTER JOIN と FULL JOINのいずれも、PG-Strom有効時に結果件数が少なくなる。

OUTER JOIN

db=# SET pg_strom.enabled=true;

db=# SELECT * FROM (
db(#   SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db(#          CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db(#     FROM t1 py LEFT OUTER JOIN t2 pg ON (py.keycode=pg.keycode)
db(# )
db-# WHERE diff_gt_0_00001 = 'DIFFERENT';
  keycode  |      rate_py       |      rate_pg       |          diff          | diff_gt_0_00001 
-----------+--------------------+--------------------+------------------------+-----------------
 523801962 | 0.4548115592572335 | 0.4547999759967326 | 1.1583260500880321e-05 | DIFFERENT
(1 row)
db=# SET pg_strom.enabled=false;

db=# SELECT * FROM (
db(#   SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db(#          CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db(#     FROM t1 py LEFT OUTER JOIN t2 pg ON (py.keycode=pg.keycode)
db(# )
db-# WHERE diff_gt_0_00001 = 'DIFFERENT';
  keycode  |        rate_py         |      rate_pg       |          diff          | diff_gt_0_00001 
-----------+------------------------+--------------------+------------------------+-----------------
 523750891 |  4.443525125593538e-07 |                    |                        | DIFFERENT
 523801962 |     0.4548115592572335 | 0.4547999759967326 | 1.1583260500880321e-05 | DIFFERENT
 523837491 | 1.1040107265492984e-07 |                    |                        | DIFFERENT
(3 rows)

FULL JOIN

db=# SET pg_strom.enabled=true;

db=# SELECT * FROM (
db(#   SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db(#          CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db(#     FROM t1 py FULL JOIN t2 pg ON (py.keycode=pg.keycode)
db(# )
db-# WHERE diff_gt_0_00001 = 'DIFFERENT';
  keycode  |      rate_py       |       rate_pg        |          diff          | diff_gt_0_00001 
-----------+--------------------+----------------------+------------------------+-----------------
 523801962 | 0.4548115592572335 |   0.4547999759967326 | 1.1583260500880321e-05 | DIFFERENT
 523661381 |                    | 6.40000052975156e-08 |                        | DIFFERENT
(2 rows)
db=# SET pg_strom.enabled=false;

db=# SELECT * FROM (
db(#   SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db(#          CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db(#     FROM t1 py FULL JOIN t2 pg ON (py.keycode=pg.keycode)
db(# )
db-# WHERE diff_gt_0_00001 = 'DIFFERENT';
  keycode  |        rate_py         |       rate_pg        |          diff          | diff_gt_0_00001 
-----------+------------------------+----------------------+------------------------+-----------------
 523661381 |                        | 6.40000052975156e-08 |                        | DIFFERENT
 523750891 |  4.443525125593538e-07 |                      |                        | DIFFERENT
 523801962 |     0.4548115592572335 |   0.4547999759967326 | 1.1583260500880321e-05 | DIFFERENT
 523837491 | 1.1040107265492984e-07 |                      |                        | DIFFERENT
(4 rows)

サブクエリの内側のクエリは問題ない

サブクエリの内側のクエリだけを取り出して実行した場合、PG-Strom有効でも正しい結果を得られる。

db=# SET pg_strom.enabled=true;

db=# SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db-#        CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db-#   FROM t1 py FULL JOIN t2 pg ON (py.keycode=pg.keycode);
  keycode  |        rate_py         |       rate_pg        |          diff          | diff_gt_0_00001 
-----------+------------------------+----------------------+------------------------+-----------------
 523801962 |     0.4548115592572335 |   0.4547999759967326 | 1.1583260500880321e-05 | DIFFERENT
 523661381 |                        | 6.40000052975156e-08 |                        | DIFFERENT
 523750891 |  4.443525125593538e-07 |                      |                        | DIFFERENT
 523837491 | 1.1040107265492984e-07 |                      |                        | DIFFERENT
(4 rows)
db=# SET pg_strom.enabled=false;

db=# SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db-#        CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db-#   FROM t1 py FULL JOIN t2 pg ON (py.keycode=pg.keycode);
  keycode  |        rate_py         |       rate_pg        |          diff          | diff_gt_0_00001 
-----------+------------------------+----------------------+------------------------+-----------------
 523661381 |                        | 6.40000052975156e-08 |                        | DIFFERENT
 523750891 |  4.443525125593538e-07 |                      |                        | DIFFERENT
 523801962 |     0.4548115592572335 |   0.4547999759967326 | 1.1583260500880321e-05 | DIFFERENT
 523837491 | 1.1040107265492984e-07 |                      |                        | DIFFERENT
(4 rows)

EXPLAIN: 実行計画

db=# SET pg_strom.enabled=true;

db=# EXPLAIN SELECT * FROM (
db(#   SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db(#          CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db(#     FROM t1 py LEFT OUTER JOIN t2 pg ON (py.keycode=pg.keycode)
db(# )
db-# WHERE diff_gt_0_00001 = 'DIFFERENT';
                                                                                                           QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (GpuJoin) on t2 pg  (cost=129.80..140.52 rows=21 width=96)
   GPU Projection: COALESCE(py.keycode, pg.keycode), py.area_rate, pg.wariai, (py.area_rate - pg.wariai), CASE WHEN (abs((py.area_rate - pg.wariai)) < '1e-05'::double precision) THEN ''::text ELSE 'DIFFERENT'::text END
   GPU Right Outer Join Quals [1]: (py.keycode = pg.keycode), [((CASE WHEN (abs((py.area_rate - pg.wariai)) < '1e-05'::double precision) THEN ''::text ELSE 'DIFFERENT'::text END) = 'DIFFERENT'::text)] ... [nrows: 970 -> 21]
   GPU Outer Hash [1]: pg.keycode
   GPU Inner Hash [1]: py.keycode
   GPU-Direct SQL: enabled (GPU-0)
   ->  Seq Scan on t1 py  (cost=0.00..18.80 rows=880 width=48)
(7 rows)
db=# SET pg_strom.enabled=false;

db=# EXPLAIN SELECT * FROM (
db(#   SELECT COALESCE(py.keycode, pg.keycode) keycode,py.area_rate rate_py, pg.wariai rate_pg, py.area_rate - pg.wariai diff,
db(#          CASE WHEN abs(py.area_rate - pg.wariai)<0.00001 THEN '' ELSE 'DIFFERENT' END diff_gt_0_00001
db(#     FROM t1 py LEFT OUTER JOIN t2 pg ON (py.keycode=pg.keycode)
db(# )
db-# WHERE diff_gt_0_00001 = 'DIFFERENT';
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=129.66..240.97 rows=21 width=96)
   Merge Cond: (py.keycode = pg.keycode)
   Filter: (CASE WHEN (abs((py.area_rate - pg.wariai)) < '1e-05'::double precision) THEN ''::text ELSE 'DIFFERENT'::text END = 'DIFFERENT'::text)
   ->  Sort  (cost=61.84..64.04 rows=880 width=48)
         Sort Key: py.keycode
         ->  Seq Scan on t1 py  (cost=0.00..18.80 rows=880 width=48)
   ->  Sort  (cost=67.82..70.25 rows=970 width=48)
         Sort Key: pg.keycode
         ->  Seq Scan on t2 pg  (cost=0.00..19.70 rows=970 width=48)
(9 rows)

Version

db=# SELECT pgstrom.githash();
                 githash                  
------------------------------------------
 fd4c07d7c36dc190d9ab99b3d56d5d3b2bcd9405
(1 row)

動作確認用データ生成クエリ

CREATE TABLE t1 (keycode char(9), area double precision, area_clip double precision, area_rate double precision);
CREATE TABLE t2 (keycode char(9), wariai double precision, target_jinko double precision);

INSERT INTO t1 VALUES 
('523750891',263203.78272429225 , 0.1169552621686655, 4.443525125593538e-07),
('523837491', 263875.8959045195 , 0.029132181955639557,1.1040107265492984e-07),
('523801962', 264638.23138546856, 120360.5266555015,0.4548115592572335);

INSERT INTO t2 VALUES 
('523801962', 0.4547999759967326, 72.3131961834805),
('523661381', 6.40000052975156e-08,2.8224002336204377e-05);
kaigai commented 2 weeks ago

1807801c5415da5e61be068686138def8044412a にて修正しました。

こちら、割と根の深い問題で、RIGHT OUTER JOINを実行する際のCPU-fallbackコードで、 JOINの条件句が式表現を含んでいたものの、それがProjectionで使われる式表現を含んでいるために、 setrefs.cでの置換ロジックで意味のない列参照（中間値を計算するためだけに領域を割り当てたスロットで、 CPU-fallbackではNULL値を置いておくだけ）に置き換えられており、それが原因でNULL値と評価され、 結果セットから消えていたというものでした。

sakaik commented 2 weeks ago

81792828 にて、PG-Stromをオンにした場合でもオフ時と同じ件数が帰ってくること、結果の値が一致することを確認しました。

db=# SELECT pgstrom.githash();
                 githash                  
------------------------------------------
 81792828fff930b838b3e4b7f33650fd97c4a3cc

参考：git log

81792828 (HEAD -> master, origin/master, origin/HEAD) documentation fix related to PostgreSQL v12
6ce56756 forget to commit v5.2 release note source document
0a76b592 documentation fix
1a5606e3 bugfix: wrong scan logic recently enbugged.
51c0b0de add delay to apply pg_strom.max_async_tasks
1807801c bugfix: CPU-fallback ExprState was not initialized correctly ←★本件修正バージョン★
dc101ba6 nvcc working directory is moved to $PGDATA/.pgstrom_fatbin_build_XXXXXX