PureData System for Analytics: nzsql error, 2448 Not enough memory for n-squared join

Answer

When running SQL one may encounter the following error:

ERROR: 2448 : Not enough memory for n-squared join

This error is indicative of the fact that a cartesian/cross-product join is occurring and the number of resultant rows is significantly large.

A cartesian/cross-product join will occur when a table is referenced in the WHERE clause and is not joined to any other tables, for example:

select employee.name,
dept.name
from employee,
dept
where employee.dept='010-4'

In the above case the dept and employee tables are not joined and thus a cartesian/cross-product will occur. In this case, for every row in the employee table this will be joined to every row in the dept table. If the employee table has 100 rows and the dept table has 5 rows, the result is 500 rows.

Additional evidence of the cartesian / cross-product will be demonstrated in the /nz/data/plans/*.pln plan file as follows:

9[02]: spu ProjectNode, 1 cols, projectFlags=0x0
0:
9[03]: spu CrossProductNode table 1000000529
-- 't'::BOOL
-- Cost=269083.6..2961901.0 Rows=4.3B Width=0 Size=0 Conf=100 {(C.C1)}

This is usually a coding error in the way the SQL is written. The query needs to be modified such that it contains all the necessary join conditions in the WHERE clause for all of the tables listed in the FROM clause.