Re: Row pattern recognition

Hi Jian,

Only comments to error messages.

> CREATE TABLE stock (company TEXT, tdate DATE, price INTEGER);
> CREATE TEMP TABLE stock (company TEXT, tdate DATE, price INTEGER);
> SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS
> pg_temp.stock.price > 0 );
> SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.stock.price
>> 0 );
> SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.price > 0 );
> 
> The error messages for the above 3 SELECT queries are different.
> (pg_temp.stock.price, public.stock.price, stock.price) mean the same
> thing: column reference,
> Should we try to make the error messages consistent?

I have tested above queries to see how error messages actually look
like. These errors raised by different reasons and becomes different
looks natural. I see no consistency problem here.

SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS
pg_temp.stock.price > 0 );
(1)
psql:rangevar.sql:6: ERROR:  42601: qualified expression "pg_temp.stock.price" is not allowed in DEFINE clause
LINE 3: pg_temp.stock.price > 0 );
        ^
LOCATION:  transformColumnRef, parse_expr.c:966

"stock" table in the FROM clause is actually pg_temp.stock. The
expression "pg_temp.stock.price > 0" is valid in general but in a DEFINE
clause schema qualified column reference is not allowed by the
standard. So the error messages look reasonable to me.

SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.stock.price
> 0 );
(2)
psql:rangevar.sql:9: ERROR:  42P01: invalid reference to FROM-clause entry for table "stock"
LINE 2: ...W AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.sto...
                                                             ^
DETAIL:  There is an entry for table "stock", but it cannot be referenced from this part of the query.
LOCATION:  errorMissingRTE, parse_relation.c:3864

"stock" table in the FROM clause is actually pg_temp.stock. The
expression "public.stock.price > 0" is not valid because public.stock
is not in the FROM clause. The error messages look reasonable to me.

SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.price > 0 );
(3)
psql:rangevar.sql:11: ERROR:  42601: range variable qualified expression "stock.price" is not allowed in DEFINE clause
LINE 2: ...W AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.pric...
                                                             ^
LOCATION:  transformColumnRef, parse_expr.c:674

The error message precisely points out that the range variable "stock"
qualifies "stock.price", which is not allowed by the standard. I see
no problem here.

> ERROR:  range variable qualified expression "rpr_composite.items" is
> not allowed in DEFINE clause
> 
> "Range variable qualified expression" is non-standard that may confuse users.

Which part of it do you think "non-standard"?  The standard uses both
terms "Range variable" and "qualified".

> To improve clarity and consistency, let's align this with the
> established error pattern:
> 
> ERROR: invalid reference to FROM-clause entry for table "the_table"

-1. As I explained above, these 3 errors raised by the different
 reasons. "invalid reference to FROM-clause entry for table
 "the_table" is only applied to (2). So unified (1) and (3) will make
 more confusion.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp