public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tatsuo Ishii <[email protected]>
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Sat, 20 Jun 2026 12:17:19 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxHEu+zYWdpi4gP7FbL+fjMhk8VOF7LJDU4CG=ZMYEPb-w@mail.gmail.com>
References: <CACJufxH_Z5aaYEir3=GHoZu-0pKzQdScu85LgCm1C8n=oQo=4Q@mail.gmail.com>
	<CAAAe_zAe8CRN-ggNhcP7b-ALiRrHxKTXBWbNVMhdW_QoO=1c4Q@mail.gmail.com>
	<CACJufxHEu+zYWdpi4gP7FbL+fjMhk8VOF7LJDU4CG=ZMYEPb-w@mail.gmail.com>

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

view thread (141+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Row pattern recognition
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox