Обсуждение: 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;
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