Re: INSERT ... VALUES... with ORDER BY / LIMIT

2010/10/2 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Fri, 2010-10-01 at 18:52 +0900, Hitoshi Harada wrote:
>> 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
>
> That looks like a bug to me. According to the documentation:
>
>  "Within larger commands, VALUES is syntactically allowed anywhere
>   that SELECT is. Because it is treated like a SELECT by the grammar,
>   it is possible to use the ORDER BY, LIMIT..."
>
>  -- http://www.postgresql.org/docs/9.0/static/sql-values.html
>
> The doc for INSERT is a little less clear:
> http://www.postgresql.org/docs/9.0/static/sql-insert.html
>
> It explicitly makes room in the INSERT grammar for VALUES, but in that
> branch of the grammar it doesn't make room for the ORDER BY, etc.

From my reading the source around transformInsertStmt(), VALUES in
INSERT is a bit apart from the one in SELECT. I see VALUES in INSERT
has to process DEFAULT and it doesn't accept NEW/OLD reference when it
is inside rule. But it doesn't seem like enough reason to explain why
the two are so different, at least to me.

Doesn't anyone know the reason?

Regards,

--
Hitoshi Harada