Re:rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true - Mailing list pgsql-hackers

> I would look at the code which adds subqueries corresponding to the
> views when rewriting queries. Do these subquery RTEs have their
> inFromCl set to true? A few instances I examined, set inFromCl = false
> and rightly so since they are not part of the original from clause. I
> think setting it for subqueries derived for GRAPH_TABLE.
> 
> I also think that the proposed fix isn't traversing the UNION query
> tree. Have you tried a GRAPH_TABLE clause which resuts into UNION of
> JOINs.
> 
> For the sake of the extension, (which looks useful), inFromCl can be
> set to true for the desired RTEs after fetching rewritten query and
> copying it. You would need query tree mutator for the same.

Hi Ashutosh,

I understand your point and thank you very much for suggesting the second solution. 
However, I'm thinking that if the kernel could accept this modification, it would be much more convenient for plugins
like`pg_pgq2sql` and `pg_duckdb`, 
 
especially `pg_duckdb`. Its working principle is to obtain the rewritten statement through `pg_get_querydef` and then
handit over to `duckdb` for processing. 
 
This means it might help `PostgreSQL` handle very complex graph queries, which is worth considering. 
Additionally, I have tested the `UNION` operation in various scenarios, and it works correctly. Here is a simple
example:

```
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    user_type VARCHAR(20)
);
CREATE TABLE follows (
    id INT PRIMARY KEY,
    follower INT REFERENCES users(id),
    following INT REFERENCES users(id)
);
CREATE TABLE premium_follows (
    id INT PRIMARY KEY,
    follower INT REFERENCES users(id),
    following INT REFERENCES users(id)
);
INSERT INTO users VALUES
    (1, 'Alice', 'regular'),
    (2, 'Bob', 'regular'),
    (3, 'Charlie', 'premium'),
    (4, 'David', 'premium');
INSERT INTO follows VALUES
    (1, 1, 2),
    (2, 1, 3);
INSERT INTO premium_follows VALUES
    (1, 3, 4),
    (2, 2, 3);
CREATE PROPERTY GRAPH social_graph_multi
    VERTEX TABLES (users)
    EDGE TABLES (
        follows
            SOURCE KEY (follower) REFERENCES users(id)
            DESTINATION KEY (following) REFERENCES users(id)
            LABEL connections,
        premium_follows
            SOURCE KEY (follower) REFERENCES users(id)
            DESTINATION KEY (following) REFERENCES users(id)
            LABEL connections
    );
SELECT * FROM pg_pgq2sql($$
    SELECT common_name
    FROM GRAPH_TABLE (
        social_graph_multi
        MATCH (a IS users)-[IS connections]->(x IS users)
        WHERE a.name = 'Alice'
        COLUMNS (x.name AS common_name)
    )
$$);
                                                                     pg_pgq2sql
                           
 

----------------------------------------------------------------------------------------------------------------------------------------------------
  SELECT common_name
                          +
 
    FROM LATERAL ( SELECT users_1.name AS common_name
                          +
 
            FROM users,
                          +
 
             follows,
                          +
 
             users users_1
                          +
 
           WHERE users.id = follows.follower AND users_1.id = follows.following AND users.name::text = 'Alice'::text
                          +
 
         UNION ALL
                          +
 
          SELECT users_1.name AS common_name
                          +
 
            FROM users,
                          +
 
             premium_follows,
                          +
 
             users users_1
                          +
 
           WHERE users.id = premium_follows.follower AND users_1.id = premium_follows.following AND users.name::text =
'Alice'::text)"graph_table"
 
(1 row)

SELECT * FROM pg_pgq2sql_info($$
    SELECT common_name
    FROM GRAPH_TABLE (
        social_graph_multi
        MATCH (a IS users)-[IS connections]->(x IS users)
        WHERE a.name = 'Alice'
        COLUMNS (x.name AS common_name)
    )
$$);
INFO:  
 SELECT common_name
   FROM LATERAL ( SELECT users_1.name AS common_name
           FROM users,
            follows,
            users users_1
          WHERE users.id = follows.follower AND users_1.id = follows.following AND users.name::text = 'Alice'::text
        UNION ALL
         SELECT users_1.name AS common_name
           FROM users,
            premium_follows,
            users users_1
          WHERE users.id = premium_follows.follower AND users_1.id = premium_follows.following AND users.name::text =
'Alice'::text)"graph_table";
 

 pg_pgq2sql_info 
-----------------
 
(1 row)

SELECT common_name
FROM LATERAL ( SELECT users_1.name AS common_name
        FROM users,
        follows,
        users users_1
        WHERE users.id = follows.follower AND users_1.id = follows.following AND users.name::text = 'Alice'::text
    UNION ALL
        SELECT users_1.name AS common_name
        FROM users,
        premium_follows,
        users users_1
        WHERE users.id = premium_follows.follower AND users_1.id = premium_follows.following AND users.name::text =
'Alice'::text)"graph_table";
 
 common_name 
-------------
 Bob
 Charlie
(2 rows)
```


--
regards,
Man Zeng