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;
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 |