Error when using sub-query with multi instances of tabl in GridDB

Question

I have a scenario as below:

-- In GridDB
set_tableInfo(store, "INT8_TBL", &INT8_TBL,
                  3,
                  "id", GS_TYPE_INTEGER, GS_TYPE_OPTION_NOT_NULL,
                  "q1", GS_TYPE_LONG, GS_TYPE_OPTION_NULLABLE,
                  "q2", GS_TYPE_LONG, GS_TYPE_OPTION_NULLABLE);

-- In GridDB FDW
CREATE FOREIGN TABLE int8_tbl(id int4 OPTIONS (rowkey 'true'), q1 int8, q2 int8) SERVER griddb_svr;

INSERT ...

select * from int8_tbl;
 id |        q1        |        q2         
----+------------------+-------------------
  1 |              123 |               456
  2 |              123 |  4567890123456789
  3 | 4567890123456789 |               123
  4 | 4567890123456789 |  4567890123456789
  5 | 4567890123456789 | -4567890123456789
(5 rows)

Scenario 1

-- In GridDB FDW
explain (verbose, costs off)
select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;           <-- a, b are 2 instances of int8_tbl 
                     QUERY PLAN                     
----------------------------------------------------
 Merge Right Join
   Output: a.id, a.q1, a.q2, b.id, b.q1, b.q2, a.q2
   Merge Cond: (b.q1 = a.q2)
   ->  Sort
         Output: b.id, b.q1, b.q2, a.q2
         Sort Key: b.q1
         ->  Foreign Scan on public.int8_tbl b
               Output: b.id, b.q1, b.q2, a.q2                          <-- a.q2 is not defined
               Remote SQL: SELECT  *  FROM int8_tbl
   ->  Sort
         Output: a.id, a.q1, a.q2
         Sort Key: a.q2
         ->  Foreign Scan on public.int8_tbl a
               Output: a.id, a.q1, a.q2
               Remote SQL: SELECT  *  FROM int8_tbl
(15 rows)

select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;

                                                                  Error:   b.q2     and         a.q2    have same values
                                                                            |                    |
                                                                            V                    V

 id |        q1        |        q2         | id |        q1        |        q2         |         x         
----+------------------+-------------------+----+------------------+-------------------+-------------------
  5 | 4567890123456789 | -4567890123456789 |    |                  |                   |                  
  3 | 4567890123456789 |               123 |  1 |              123 |               456 |               456
  3 | 4567890123456789 |               123 |  2 |              123 |  4567890123456789 |  4567890123456789
  1 |              123 |               456 |    |                  |                   |                  
  2 |              123 |  4567890123456789 |  3 | 4567890123456789 |               123 |               123
  4 | 4567890123456789 |  4567890123456789 |  3 | 4567890123456789 |               123 |               123
  2 |              123 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  2 |              123 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 | -4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 | -4567890123456789
(10 rows)

Expected result:

select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;

 id |        q1        |        q2         | id |        q1        |        q2         |         x         
----+------------------+-------------------+----+------------------+-------------------+-------------------
  5 | 4567890123456789 | -4567890123456789 |    |                  |                   |                  
  3 | 4567890123456789 |               123 |  1 |              123 |               456 |               123
  3 | 4567890123456789 |               123 |  2 |              123 |  4567890123456789 |               123
  1 |              123 |               456 |    |                  |                   |                  
  2 |              123 |  4567890123456789 |  3 | 4567890123456789 |               123 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  3 | 4567890123456789 |               123 |  4567890123456789
  2 |              123 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  2 |              123 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 |  4567890123456789
(10 rows)

Scenario 2

-- In GridDB FDW
-- lateral reference in a PlaceHolderVar evaluated at join level
explain (verbose, costs off)
select * from
  int8_tbl a left join lateral
  (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from               <-- a.q1 not found
   int8_tbl b cross join int8_tbl c) ss
  on a.q2 = ss.bq1;
ERROR:  variable not found in subplan target lists
select * from
  int8_tbl a left join lateral
  (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
   int8_tbl b cross join int8_tbl c) ss
  on a.q2 = ss.bq1;
ERROR:  variable not found in subplan target lists

If I remove a.q1 from this query, the command can run successfully. I think that there is something wrong with query plan.

In Scenario 1, when a.q2 is not found, a.q2 is converted to b.q2 (a and b are 2 instances of int8_tbl).
In Scenario 2, when a.q1 is not found, the error is returned.

I think we can modify query plan to get data from int8_tbl only once time for all instances (a, b and c). Could you please help me to resolve this issue?

Error when using sub-query with multi instances of tabl in GridDB

0 Answers0