Skip to content

Instantly share code, notes, and snippets.

@alexanderdean
Created December 17, 2013 17:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save alexanderdean/8008664 to your computer and use it in GitHub Desktop.
Save alexanderdean/8008664 to your computer and use it in GitHub Desktop.
Redshift bug when working with JSONs and UNIONs
-- 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]
-----------------------------------------------
@omarish
Copy link

omarish commented Mar 10, 2014

Did you ever find a solution to this?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment