public inbox for [email protected]  

help / color / mirror / Atom feed

From: Henson Choi <[email protected]>
To: Tatsuo Ishii <[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: Thu, 12 Mar 2026 09:44:29 +0900
Message-ID: <CAAAe_zBswm=JGsBi3Hn-8kVbH3LgAcOvWJ3nnoAeWbiwR7NOig@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAN4CZFOEpz6vXitAkcCGixzAzp4uRRahyScWixKQZU=K4tngtQ@mail.gmail.com>
	<CAAAe_zAFv0E_EhFxZueJ1n9pZpPaTVChMhDmndzugzovgz0o2Q@mail.gmail.com>
	<[email protected]>

Hi Tatsuo,

Sorry about the empty reply — 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 — it would be
surprising if `DEFINE A AS val` rejected a type that `WHERE val`
accepts.

Regards,
Henson

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]
  Subject: Re: Row pattern recognition
  In-Reply-To: <CAAAe_zBswm=JGsBi3Hn-8kVbH3LgAcOvWJ3nnoAeWbiwR7NOig@mail.gmail.com>

* 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