On 05/12/2014 10:18 AM, Craig Ringer wrote:
> Do we need a way to schema-qualify the operator used in NULLIF, or to
> provide an operator alias that it gets dumped as?
A bit more digging shows that this doesn't affect LEAST or GREATEST,
which I'd expect it to as well. Nor DISTINCT (otherwise someone would've
yelled long ago).
LEAST and GREATEST look up the operators from the b-tree opclass for the
type(s) they're operating on using the type cache. So they don't care
about search_path, they respect the schema of the base type they're
operating on, per the case for T_MinMaxExpr in executor/execQual.c .
NULLIF does not do so, per the case in T_NullIfExpr. But we don't
actually get that far.
The immediate failure is actually:
hstore_test=# \set VERBOSITY verbose
hstore_test=# CREATE VIEW hstore_test_schema.hstore_test_view AS
SELECT NULLIF(column1, column2) AS comparison FROM
hstore_test_schema.hstore_test_table;
ERROR: 42883: operator does not exist: public.hstore = public.hstore
LINE 2: SELECT NULLIF(column1, column2) AS comparison FROM hstore_te...
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LOCATION: op_error, parse_oper.c:722
which dies in:
#2 0x000000000051ad34 in make_op (pstate=pstate@entry=0x1189f38,
opname=0x1189c10, ltree=ltree@entry=0x118a528, rtree=0x118a590,
location=58) at parse_oper.c:770
#3 0x00000000005155e1 in transformAExprNullIf (a=0x1189bc0,
pstate=0x1189f38) at parse_expr.c:1021
instead of using the b-tree opclass of the base type to find the operator.
This looks like a bug that'll want backpatching and it's not a data loss
risk, so I don't think it's urgent to shove this in before 9.4 closes.
I'll look for time to try out a possible fix soon, after reading all the
code that shows how DISTINCT and GREATEST/LEAST handle this in detail.
I'm hard up against a deadline at the moment so it'll be a little while.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
