Lost hierarchy chain in query

[mkgrgis]

Firebird:

CREATE TABLE "!IE" (
    "c₀" INTEGER NOT NULL,
    "c₁" INTEGER NOT NULL
);

Sample data for Firebird Hierarchy.txt Some first lines is here.

SQL> SELECT * FROM "!IE";

          c₀           c₁ 
============ ============ 
         394          300 
         395          300 
         396          394 
         397          395 
         398          300 
         399          398 

Query for Firebird

WITH RECURSIVE r AS (
SELECT p."c₀",
       0 AS level,       
       CAST (p."c₀" AS VARCHAR(256)) AS ADR 
 FROM "!IE" p
WHERE p."c₁" = 12
UNION ALL 
SELECT p."c₀",
      r.level+1 AS level,  
      r.ADR || ' ' || p."c₀" AS ADR
 FROM "!IE" p 
 JOIN r
   ON p."c₁" = r."c₀"
)
SELECT * FROM r;

No problems. First lines of result

          c₀        LEVEL ADR                                                                                                                                                                                                                                                              
======= ======= ============================ 
         241            0 241                                                                                                                                                                                                                                                              
         481            1 241 481                                                                                                                                                                                                                                                          
         482            2 241 481 482                                                                                                                                                                                                                                                      
         483            3 241 481 482 483                                                                                                                                                                                                                                                  
         484            3 241 481 482 484                                                                                                                                                                                                                                                  
         485            3 241 481 482 485                                                                                                                                                                                                                                                  
         486            3 241 481 482 486                                                                                                                                                                                                                                                  
         487            3 241 481 482 487                                                                                                                                                                                                                                                  
         488            3 241 481 482 488                                                                                                                                                                                                                                                  
         526            1 241 526                                                                                                                                                                                                                                                          
       14288            2 241 526 14288                                                                                                                                                                                                                                                    
       14286            3 241 526 14288 14286                                                                                                                                                                                                                                              
       14287            3 241 526 14288 14287                                                                                                                                                                                                                                              
       14338            1 241 14338                                                                                                                                                                                                                                                        
       14566            1 241 14566             

and what we have in PostgreSQL 13?

CREATE FOREIGN TABLE hier(
c₀      INTEGER OPTIONS (column_name 'c₀'),
c₁  INTEGER OPTIONS (column_name 'c₁')
)
SERVER "fb_Test"
OPTIONS ( table_name '!IE' );

The query select * from hier; is similar to query for Firebird. But for query

WITH RECURSIVE r AS (
SELECT p.c₀,
       0 AS level,       
       p.c₀::text AS ADR 
 FROM hier p
WHERE p.c₁ = 12
UNION ALL 
SELECT p.c₀,
      r.level+1 AS level,  
      r.ADR || ' ' || p.c₀ AS ADR
 FROM hier p 
 JOIN r
   ON p.c₁ = r.c₀
)
SELECT * FROM r;

we get only levels 0 and 1 - this is the problem.

  c₀   | level |    adr    
-------+-------+-----------
   241 |     0 | 241
   481 |     1 | 241 481
 14566 |     1 | 241 14566
   242 |     1 | 241 242
 14338 |     1 | 241 14338
   245 |     1 | 241 245
   323 |     1 | 241 323
   526 |     1 | 241 526
   244 |     1 | 241 244
   243 |     1 | 241 243
   246 |     1 | 241 246

Final test for pg query. If we execute create table hier2 as select * from hier; and rewrite query to hier2, there is no problems with all levels.

  c₀   | level |                           adr                           
-------+-------+---------------------------------------------------------
   241 |     0 | 241
   481 |     1 | 241 481
   526 |     1 | 241 526
 14338 |     1 | 241 14338
 14566 |     1 | 241 14566
   242 |     1 | 241 242
   243 |     1 | 241 243
   244 |     1 | 241 244
   245 |     1 | 241 245
   246 |     1 | 241 246
   323 |     1 | 241 323
   482 |     2 | 241 481 482
   582 |     2 | 241 244 582
 14288 |     2 | 241 526 14288
 14515 |     2 | 241 245 14515
 14568 |     2 | 241 14566 14568
   247 |     2 | 241 242 247
   248 |     2 | 241 242 248
   268 |     2 | 241 243 268
   273 |     2 | 241 244 273
   483 |     3 | 241 481 482 483
   484 |     3 | 241 481 482 484

[ibarwick]

Thanks for the report, I will take a look but it may be a few weeks before I get to this.

[mkgrgis]

No problems, @ibarwick ! I'll try to prepare the smallest dataset for this issue. Surprisingly, this problem has nothing common with https://github.com/ibarwick/firebird_fdw/issues/13.

[mkgrgis]

It may be interesting for comparation, https://github.com/pgspider/sqlite_fdw haven't issue for the same situation here discussed.

[ibarwick]

No problems, @ibarwick ! I'll try to prepare the smallest dataset for this issue. Surprisingly, this problem has nothing common with #13.

@mkgrgis Any chance of a dataset to reproduce this with? I hope to be able to take a look at this in the next few weeks.

[mkgrgis]

Now there is only big dataset with 1345 rows from my first comment to this issue. I have some variants of mini datasets, but there is no errors. I'll make additional research to locate a problem.

[ibarwick]

Now there is only big dataset with 1345 rows from my first comment to this issue. I have some variants of mini datasets, but there is no errors. I'll make additional research to locate a problem.

Ah OK, I missed that attachment; that should be fine for now.

[mkgrgis]

I have a version, error depends on a combination of cardinalities of objects in a plan of execution between DB's. All of my mini datasets (10..50 rows) haven't the error.

[ibarwick]

OK, I've finally had the time to work out what was causing this. The issue does not occur if the foreign table is analyzed; in this case a hash join is used. However, without any table statistics, the planner uses a merge join, which results in a rescan, which was not happening properly, leading to PostgreSQL only receiving a subset of the expected data. Fix is applied; I'll put out a new release in the next few days. Thanks once again for the report!

[mkgrgis]

Thanks a lot, @ibarwick ! I have seen https://github.com/ibarwick/firebird_fdw/commit/de5a02bf8fdf994fd32a9a50acd6462ee0243f4d My version of problem's source was wrong. Not only cardinality, but rescan in complex with merge join. I'll try to use Your new code next week and then can to write if it's correct for release.

[mkgrgis]

Verified. Works fine, @ibarwick! My congratulations for You! No problems to make a new release if You want.

Now libfq doesn't support only generic BLOB and arrays. In my experience there was no BLOB's in Firebird. For C-language Firebird interface there is some common algorithms for BLOB and arrays. I saw that arrays in Firebird C-code likes to special implicit BLOBs, but i haven't use arrays. If You want, i will help with arrays.