BUG #8533: JSON error caused by optimisation

From: m(at)pplcast(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8533: JSON error caused by optimisation
Date: 2013-10-16 19:43:23
Message-ID: E1VWX07-0005Yy-Qh@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8533
Logged by: Mike
Email address: m(at)pplcast(dot)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;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kaveh Mousavi Zamani 2013-10-17 15:21:05 Missing record in binary replica 9.3
Previous Message timo.gurr 2013-10-16 10:09:28 BUG #8532: postgres fails to start with timezone-data >=2013e