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
