Fixing bizarre Assert errors on Amazon Redshift

Where I work, we run multiple instances of databases on Amazon Redshift, with identical structures and largely identical data.

Recently, we encountered repeated, difficult to reproduce errors with views incorporating several tables using UNION ALL. Specifically, something as simple as:

SELECT * FROM <view_name> LIMIT 100;

Would result in an error as below:

[Amazon](500310) Invalid operation: Assert
  error:  Assert
  code:      1000
  context:   mod == -1 || (mod - VARHDRSZ) == size -
  query:     1789002
  location:  pg_utils.cpp:1192
  process:   padbmaster [pid=31177]
1 statement failed.

Execution time: 15.87s  

No obvious issues presented themselves after running an EXPLAIN or digging in to the query in the AWS console. After some back and forth with AWS support, they shared this feedback. In short, dropping and re-creating the view, or running CREATE OR REPLACE VIEW ... with the same definition, fixes it. Hopefully, this proves useful to someone else out there encountering bizarre Assert errors while trying to use views in Redshift.

Robert, Just got back from our Redshift engineer. Turns out that its an issue with the software (You can call it a bug). What happen sometime while using UNION ALL for view creation, the derived type of a column become wrong for example , a numeric((38,4) can sometime derive at numeric(3838,65531) or None internally which leads to a assert error.