> 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.
Ok. we should be consistent with WHERE etc. unless the standard
requres DEFINE stricter. But if the tle is in the target list
(possibly with resjunk attribute), it could be different from the one
in the DEFINE because of the casting by coerce_to_boolean(). i.e.:
- tle in the target list has no cast node
- tle in the DEFINE may have cast node
I don't know how this could affect subsequent RPR process but I would
like to keep the prerequisites that tle in DEINE and the traget list
is identical.
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
