Created
December 17, 2013 17:13
-
-
Save alexanderdean/8008664 to your computer and use it in GitHub Desktop.
Redshift bug when working with JSONs and UNIONs
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 1. Setup | |
DROP table bug_table cascade; | |
CREATE TABLE bug_table ( | |
some_json varchar(200), | |
some_flag boolean | |
); | |
CREATE VIEW bug_view_1 AS | |
SELECT | |
some_json AS json, 'a' AS some_str | |
FROM bug_table | |
WHERE some_flag = FALSE | |
UNION SELECT | |
some_json AS json, 'b' AS some_str | |
FROM bug_table; | |
CREATE VIEW bug_view_2 AS | |
SELECT | |
json, some_str, NULL AS index, some_str AS some_str2 | |
FROM bug_view_1 | |
WHERE some_str IN ('a', 'b') | |
UNION SELECT -- First array position | |
json, some_str, 0::smallint AS index, | |
CASE json_extract_path_text(json_extract_array_element_text(json, 0), 'list_type') | |
WHEN 'blah' THEN 'Found blah' || some_str | |
ELSE null | |
END AS some_str2 | |
FROM bug_view_1 | |
WHERE some_str NOT IN ('a', 'b'); | |
-- 2. Run | |
SELECT | |
some_str2 | |
FROM bug_view_2 | |
UNION SELECT | |
json_extract_path_text(json_extract_array_element_text(json, index), 'content') | |
FROM bug_view_2; | |
-- 3. Expected error | |
[Err] ERROR: Assert | |
DETAIL: | |
----------------------------------------------- | |
error: Assert | |
code: 1000 | |
context: mod == -1 || (mod-VARHDRSZ) == size - | |
query: 1872099 | |
location: pg_utils.cpp:997 | |
process: padbmaster [pid=15651] | |
----------------------------------------------- |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Did you ever find a solution to this?