While tackling the top-level CTEs patch, I found that INSERT ... VALUES isn't aware of ORDER BY / LIMIT.
regression=# CREATE TABLE t1(x int); CREATE TABLE regression=# INSERT INTO t1 VALUES (1),(2),(3) LIMIT 1; INSERT 0 3 regression=# TABLE t1; x --- 1 2 3 (3 rows) regression=# TRUNCATE t1; TRUNCATE TABLE regression=# INSERT INTO t1 VALUES (1),(2),(3) ORDER BY 2; INSERT 0 3 regression=# TABLE t1; x --- 1 2 3 (3 rows) Is it intentional, or a bug? This behavior gives me a thought that current INSERT ... VALUES should be refactored. Query(INSERT)->rtable has a RangeTblEntry(RTE_VALUES) as the representation of VALUES, but to be consistent with the syntax, rtable should have RangeTblEntry(RTE_SUBSELECT), whose rtable member holds RangeTblEntry(RTE_VALUES) as the normal SELECT does. I see there's some reason here why INSERT case doesn't handle it as the normal SELECT does, and I don't see how big this change affect to around rewriter and planner. On the other hand, if INSERT ... VALUES doesn't care about ORDER BY / LIMIT, it should ignore WITH clause as well. Ignoring WITH breaks the current behavior, but the use case is quite narrow and I bet no one relies on it, so we might be able to break it. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
