BUG #8533: JSON error caused by optimisation
The following bug has been logged on the website:
Bug reference: 8533
Logged by: Mike
Email address: m@pplcast.com
PostgreSQL version: 9.3.1
Operating system: all
Description:
Not sure if bug or feature, but the query below without the "offset 0" fails
with "ERROR: cannot extract field from a non-object", while the one with
succeeds as intended. Please excuse the messiness.
=# explain select aaa->'bbb'->'ccc'->'ddd'->>1, aaa->'bbb'->'ccc' from
(select aaa from yyy where not json_isarray(aaa->'bbb'->'ccc')) _(aaa) where
(aaa->'bbb'->'ccc'->'ddd'->>1) = 'eee';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on yyy (cost=0.00..3502.00 rows=32 width=32)
Filter: ((((((aaa -> 'bbb'::text) -> 'ccc'::text) -> 'ddd'::text) ->> 1)
= 'eee'::text) AND (NOT json_isarray(((aaa -> 'bbb'::text) ->
'ccc'::text))))
(2 rows)
=# explain select aaa->'bbb'->'ccc'->'ddd'->>1, aaa->'bbb'->'ccc' from
(select aaa from yyy where not json_isarray(aaa->'bbb'->'ccc') offset 0)
_(aaa) where (aaa->'bbb'->'ccc'->'ddd'->>1) = 'eee';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on _ (cost=0.00..3526.31 rows=32 width=32)
Filter: (((((_.aaa -> 'bbb'::text) -> 'ccc'::text) -> 'ddd'::text) ->> 1)
= 'eee'::text)
-> Seq Scan on yyy (cost=0.00..3379.92 rows=6485 width=32)
Filter: (NOT json_isarray(((aaa -> 'bbb'::text) -> 'ccc'::text)))
(4 rows)
CREATE OR REPLACE FUNCTION json_isarray(obj json) RETURNS bool AS $$
BEGIN
PERFORM json_array_elements(obj);
RETURN true;
EXCEPTION WHEN invalid_parameter_value THEN
RETURN false;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
m@pplcast.com writes:
Not sure if bug or feature, but the query below without the "offset 0" fails
with "ERROR: cannot extract field from a non-object", while the one with
succeeds as intended. Please excuse the messiness.
Yeah, this is not surprising, since as you can see from the query plans,
by default the planner will flatten the sub-select, and it ends up putting
your json_isarray() function after the filter conditions it's meant to
protect. That it does so is a feature, not a bug, because PL functions
are by default assigned a higher cost than built-in C functions; so
they'll be put at the end of any list of conditions to be checked at the
same plan node.
If you want something less ugly than the OFFSET 0 hack as a workaround,
you could try assigning your function a small cost (less than 1).
Another possible approach is to make the "select where not json_isarray"
bit into an explicitly declared view, which you could mark as a SECURITY
view to prevent the planner from changing the qual order. That might
turn out badly from an optimization standpoint though.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs