[Postgres-xl-developers] cte bug

senhu(胡森) senhu at tencent.com
Wed Jun 21 00:10:12 PDT 2017



Hello, all:
 When I run the with case of regression test, I get some errors about ‘with recursive’, here is the sql:
with recursive q as (
      select * from department
    union all
      (with recursive x as (
           select * from department
         union all
           (select * from q union all select * from x)
        )
       select * from x)
    )
select * from q order by 1, 2, 3 limit 32;

Explanation:
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=313646.00..313646.08 rows=32 width=40)
   CTE q
     ->  Remote Subquery Scan on all (datanode_1)  (cost=0.00..192986.00 rows=2413200 width=40)
           ->  Recursive Union  (cost=0.00..192986.00 rows=2413200 width=40)
                 ->  Seq Scan on department department_1  (cost=0.00..22.00 rows=1200 width=40)
                 ->  CTE Scan on x x_1  (cost=9646.00..14470.00 rows=241200 width=40)
                       CTE x
                         ->  Remote Subquery Scan on all (datanode_1)  (cost=0.00..9646.00 rows=241200 width=40)
                               ->  Recursive Union  (cost=0.00..9646.00 rows=241200 width=40)
                                     ->  Seq Scan on department  (cost=0.00..22.00 rows=1200 width=40)
                                     ->  Append  (cost=0.00..720.00 rows=24000 width=40)
                                           ->  WorkTable Scan on q q_1  (cost=0.00..240.00 rows=12000 width=40)
                                           ->  WorkTable Scan on x  (cost=0.00..240.00 rows=12000 width=40)
   ->  Sort  (cost=120660.00..126693.00 rows=2413200 width=40)
         Sort Key: q.id, q.parent_department, q.name
         ->  CTE Scan on q  (cost=0.00..48264.00 rows=2413200 width=40)
Error message:
 unrecognized node type: 120

With the remote_subquery part(marked on red), there are two worktablecans below the Recursive Union, one is on cte x, the other is on cte q. when executing the query on remote, worktablescan on x is ok, but worktablescan on q can not be executed, because there is no worktable for that scan, and the query will fail.

To fix this problem, I think the correct plan shoule be like this(Recursive Union part):
                               ->  Recursive Union  (cost=0.00..9646.00 rows=241200 width=40)
                                  ->  Remote Subquery Scan on all (datanode_1)  (cost=0.00..9646.00 rows=241200 width=40)
                                     ->  Seq Scan on department  (cost=0.00..22.00 rows=1200 width=40)
                                  ->  Append  (cost=0.00..720.00 rows=24000 width=40)
                                     ->  WorkTable Scan on q q_1  (cost=0.00..240.00 rows=12000 width=40)
                                     ->  WorkTable Scan on x  (cost=0.00..240.00 rows=12000 width=40)

If Recursive Union has worktablscan, we have to make sure the Recursive Union has all worktables which the worktablescans need. In this case, we can add remote_subquery to Recursive Union, or just send the subquery to remote.



regards

Senhu(senhu at tencent.com<mailto:senhu at tencent.com>)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20170621/ebe654e2/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cte.patch
Type: application/octet-stream
Size: 12412 bytes
Desc: cte.patch
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20170621/ebe654e2/attachment-0001.obj>


More information about the Postgres-xl-developers mailing list