Thread: 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