weird cast behavior in "IN (values)" clause

Started by Alvaro Herreraalmost 15 years ago2 messageshackers
Jump to latest
#1Alvaro Herrera
alvherre@2ndquadrant.com

I just came across this:

alvherre=# select * from pg_class where oid::regclass in ('foo');
ERROR: invalid input syntax for type oid: "foo"
LÍNEA 1: select * from pg_class where oid::regclass in ('foo');
^
alvherre=# select * from pg_class where oid::regclass in ('foo', 'foo');
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------
foo | 2200 | 16448 | 0 | 10 | 0 | 16446 | 0 | 0 | 0 | 0 | 0 | t | f | p | r | 1 | 0 | f | t | f | f | f | 720 | |
(1 fila)

Not sure what to make of it.

(The reason I put the regclass cast in the oid instead of the other way
around is that I was trying a bunch of other tables, so it was
oid::regclass IN ('foo', 'bar', 'baz')
which is a lot easier to type than attaching a regclass cast to each
literal).

I am not sure why it would be valid to list two literals in the values
but not one.

--
Álvaro Herrera <alvherre@alvh.no-ip.org>

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alvaro Herrera (#1)
Re: weird cast behavior in "IN (values)" clause

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

I am not sure why it would be valid to list two literals in the values
but not one.

The discrepancy seems to be because transformAExprIn uses a different
type resolution method when there's more than one non-Var in the RHS.

Maybe we should apply select_common_type even when there's only one
RHS non-Var, even though we don't want to use a ScalarArrayOpExpr?

Curious that it's acted like this since 8.2 and nobody complained.

regards, tom lane