Netezza Code Generation Failure

ERROR:  256 : Code generation failure

In my experience this error means “I can’t figure out how to execute your query” or “The plan I created to execute your query failed.” Here’s a query that will cause the error:

create table T_CHAR_ID as
	select	'1' as ID union 
	select	'2' union
	select	'3';
	
create table T_INT_ID as
	select	4 as ID union
	select	5 union
	select	6;

select	*
from	(	select	ID
		from	T_CHAR_ID
		union all
		select	ID
		from	T_INT_ID ) I
where	ID = 3;

However, it will work without the where clause. Why?

If you look at the query plan you will see that Netezza pushes the where clause into the sub-select.

QUERY VERBOSE PLAN:

Node 1.
  [SPU Sequential Scan table "T_CHAR_ID" {(T_CHAR_ID."ID")}]
      -- Estimated Rows = 1, Width = 1, Cost = 0.0 .. 0.0, Conf = 80.0 
      Restrictions:
        (T_CHAR_ID."ID" = 3)
      Projections:
        1:T_CHAR_ID."ID"
Node 2.
  [SPU Sub-query Scan table "*SELECT* 1" Node "1" {}]
      -- Estimated Rows = 1, Width = 1, Cost = 0.0 .. 0.0, Conf = 0.0
      Projections:
        1:INT4(0."1")
Node 3.
  [SPU Sequential Scan table "T_INT_ID" {(T_INT_ID."ID")}]
      -- Estimated Rows = 1, Width = 4, Cost = 0.0 .. 0.0, Conf = 80.0 
      Restrictions:
        (T_INT_ID."ID" = 3)
      Projections:
        1:T_INT_ID."ID"
Node 4.
  [SPU Sub-query Scan table "*SELECT* 2" Node "3" {(0."1")}]
      -- Estimated Rows = 1, Width = 4, Cost = 0.0 .. 0.0, Conf = 0.0
      Projections:
        1:0."1"
Node 5.
  [SPU Append Nodes: , "2", "4 (stream)" {}]
      -- Estimated Rows = 2, Width = 4, Cost = 0.0 .. 0.0, Conf = 0.0
      Projections:
        1:0."1"
Node 6.
  [SPU Sub-query Scan table "I" Node "5" {}]
      -- Estimated Rows = 2, Width = 4, Cost = 0.0 .. 0.0, Conf = 100.0
      Projections:
        1:I."ID"
  [SPU Return]
  [Host Return]

This is fine. Filtering each table for ‘ID = 3’ and appending the results is equivalent to appending all their rows and then filtering for ‘ID = 3’.

When we put operations within parenthesis we usually expect them to be resolved before the outer operations. But Netezza is pretty smart, and sees that doing the filtering first is, in this case, both logically equivalent and requires less processing. Imagine if each table had a million rows. Would it really make sense to pull each of those into a two million row temporary set (two million rows read) and then scan for a single value (two million rows read again)?

SQL Server behaves the same way, except it seems a little smarter about implicit casting. This query does not fail, and the query plan shows that it scanned each table individually, just as Netezza did:

declare @charId as table ( ID varchar(4));
insert	into @charId ( ID )
values  ( '1'), ('2'), ('3');

declare @intId as table ( ID int );
insert	into @intId ( ID )
values  (4), (5), (6);



select	*
from	(	select	ID
		from	@charId 
		union all
		select	ID
		from	@intId ) i
where	ID = 3;

example 2
I don’t know why Netezza doesn’t handle this situation the same, because it does do implicit casts in most other situations.

When you run into the “Code Generation Error” you should try examining your query, maybe also the query plan if you’re adept at reading those (the plan graph in Aginity is quite useful).

Here’s a forum post where the error was caused by the table having multiple versions, and grooming the old table versions resolved the issue. Take note of David Birmingham’s explanation from the thread. Altering a table by adding a column creates what he calls a ‘shadow table’, and in the background when you query your table Netezza is joining it with the shadow table to create the new table. This can cause the query plan generated to fail.

If you alter a table, you should always groom versions afterwards in order to avoid this error (and to avoid a performance penalty).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s