MIME-Version: 1.0
References: <20260302.141823.129040280886273031.ishii@postgresql.org>
 <CAN4CZFOEpz6vXitAkcCGixzAzp4uRRahyScWixKQZU=K4tngtQ@mail.gmail.com>
 <CAAAe_zAFv0E_EhFxZueJ1n9pZpPaTVChMhDmndzugzovgz0o2Q@mail.gmail.com>
 <20260312.092125.2016894123875469735.ishii@postgresql.org>
In-Reply-To: <20260312.092125.2016894123875469735.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Thu, 12 Mar 2026 09:44:29 +0900
Message-ID: 
 <CAAAe_zBswm=JGsBi3Hn-8kVbH3LgAcOvWJ3nnoAeWbiwR7NOig@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: zsolt.parragi@percona.com, vik@postgresfriends.org, er@xs4all.nl,
	jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
	peter@eisentraut.org, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c7e06a064cc90ecb"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zBswm%3DJGsBi3Hn-8kVbH3LgAcOvWJ3nnoAeWbiwR7NOig%40mail.gmail.com>
Precedence: bulk

--000000000000c7e06a064cc90ecb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Tatsuo,

Sorry about the empty reply =E2=80=94 sent it by accident.

Yeah, current patch needs to be fixed. Question is, the output of the
> expression of DEFINE clause must be a strict boolean or, it is allowed
> to accept an expression coercive to boolean?
>
> If we prefer the former, we should use exprType() instead.
>

Good question. Both the SQL standard and PostgreSQL's existing
behavior point toward allowing coercion.

In the SQL standard (ISO/IEC 19075-5), DEFINE specifies a "Boolean
condition" for each pattern variable. The standard does not suggest
a stricter type requirement on DEFINE than on other boolean contexts
like WHERE or HAVING.

PostgreSQL's WHERE clause already accepts implicit casts to boolean
via coerce_to_boolean(). You can verify this with Zsolt's setup:

    CREATE TYPE truthyint AS (v int);
    CREATE FUNCTION truthyint_to_bool(truthyint) RETURNS boolean AS $$
      SELECT ($1).v <> 0;
    $$ LANGUAGE SQL IMMUTABLE STRICT;
    CREATE CAST (truthyint AS boolean)
      WITH FUNCTION truthyint_to_bool(truthyint)
      AS ASSIGNMENT;

    CREATE TABLE test_coerce (id serial, val truthyint);
    INSERT INTO test_coerce VALUES
      (1, ROW(1)), (2, ROW(0)), (3, ROW(5)), (4, ROW(0));

    SELECT id, val FROM test_coerce WHERE val ORDER BY id;
    -- returns rows 1 and 3 (where val casts to true)

As Zsolt noted, the same query works correctly with an actual
boolean column. The issue is specifically that the implicit cast
is not being applied.

All other boolean contexts in the parser (WHERE, HAVING, JOIN/ON,
WHEN, etc.) use coerce_to_boolean() and assign the result back.
I think DEFINE should be consistent with these =E2=80=94 it would be
surprising if `DEFINE A AS val` rejected a type that `WHERE val`
accepts.

Regards,
Henson

--000000000000c7e06a064cc90ecb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi Tatsuo,</div><div dir=3D"ltr"><br></di=
v><div dir=3D"ltr">Sorry about the empty reply =E2=80=94 sent it by acciden=
t.<br></div><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail_quot=
e_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(2=
04,204,204);padding-left:1ex">
Yeah, current patch needs to be fixed. Question is, the output of the<br>
expression of DEFINE clause must be a strict boolean or, it is allowed<br>
to accept an expression coercive to boolean?<br>
<br>
If we prefer the former, we should use exprType() instead.<br></blockquote>=
<div><br></div>Good question. Both the SQL standard and PostgreSQL&#39;s ex=
isting<br>behavior point toward allowing coercion.<br><br>In the SQL standa=
rd (ISO/IEC 19075-5), DEFINE specifies a &quot;Boolean<br>condition&quot; f=
or each pattern variable. The standard does not suggest<br>a stricter type =
requirement on DEFINE than on other boolean contexts<br>like WHERE or HAVIN=
G.<br><br>PostgreSQL&#39;s WHERE clause already accepts implicit casts to b=
oolean<br>via coerce_to_boolean(). You can verify this with Zsolt&#39;s set=
up:<br><br><font face=3D"monospace">=C2=A0 =C2=A0 CREATE TYPE truthyint AS =
(v int);<br>=C2=A0 =C2=A0 CREATE FUNCTION truthyint_to_bool(truthyint) RETU=
RNS boolean AS $$<br>=C2=A0 =C2=A0 =C2=A0 SELECT ($1).v &lt;&gt; 0;<br>=C2=
=A0 =C2=A0 $$ LANGUAGE SQL IMMUTABLE STRICT;<br>=C2=A0 =C2=A0 CREATE CAST (=
truthyint AS boolean)<br>=C2=A0 =C2=A0 =C2=A0 WITH FUNCTION truthyint_to_bo=
ol(truthyint)<br>=C2=A0 =C2=A0 =C2=A0 AS ASSIGNMENT;<br><br>=C2=A0 =C2=A0 C=
REATE TABLE test_coerce (id serial, val truthyint);<br>=C2=A0 =C2=A0 INSERT=
 INTO test_coerce VALUES<br>=C2=A0 =C2=A0 =C2=A0 (1, ROW(1)), (2, ROW(0)), =
(3, ROW(5)), (4, ROW(0));<br><br>=C2=A0 =C2=A0 SELECT id, val FROM test_coe=
rce WHERE val ORDER BY id;<br>=C2=A0 =C2=A0 -- returns rows 1 and 3 (where =
val casts to true)<br></font><br>As Zsolt noted, the same query works corre=
ctly with an actual<br>boolean column. The issue is specifically that the i=
mplicit cast<br>is not being applied.<br><br>All other boolean contexts in =
the parser (WHERE, HAVING, JOIN/ON,<br>WHEN, etc.) use coerce_to_boolean() =
and assign the result back.<br>I think DEFINE should be consistent with the=
se =E2=80=94 it would be<br>surprising if `DEFINE A AS val` rejected a type=
 that `WHERE val`<br>accepts.<br><br>Regards,<br>Henson<br><div>=C2=A0</div=
></div></div>

--000000000000c7e06a064cc90ecb--