Skip to content

Archive for

ERROR: Execution of implicit cross joins is not permitted

Error:
Execution of implicit cross joins is not permitted

Cause:
While running a query with joins you may get this error when ENABLE_CROSSJOIN_CONTROL parameter in /nz/data/postgresql.conf is set to ON (default value is OFF). If this parameter is set to ON then the system will prohibit implicit cross join.

Resolution:
1) Check the current setting for this parameter.
SYSTEM(ADMIN)=> show enable_crossjoin_control;
NOTICE: ENABLE_CROSSJOIN_CONTROL is on

2) Check if this entry exists in postgres.conf file and is set to ON. If it exists then either comment that or set it to OFF.

NOTE: If this entry exists then it will be there for some reason. Make sure you ar not causig any other issue by just changing this setting for one trouble query.

3) Restart NPS (will need outage) or easy way (no outage needed and runs in seconds) is to run “pkill -HUP postmaster” at Unix promt for the above change to take effect.

4) Check the setting again for this parameter to make sure its set to OFF.
SYSTEM(ADMIN)=> show enable_crossjoin_control;
NOTICE: ENABLE_CROSSJOIN_CONTROL is off

5) Try running the query again.

Case sensitive object name – Issue & Resolution

ISSUE:
Getting error message like below, even when you can see the objects exists:
ERROR: Relation ‘TESTING’ does not exist
ERROR: Attribute ‘name’ not found

DESCRIPTION:
In Netezza we can create case-sensitive object names by enclosing the in double quotes (“”) while creating the objects. If object is not enclosed in double quotes then Netezza by default will consider it uppercase. Same is the scenario when we query these objects.

RESOLUTION:
Avoid creating case sensitive objects except if it is needed. To query these objects use double quotes around the object name . Make sure it is spelled in correct case for case sensitive objects.

EXAMPLE:
Here is a example of how case sensitive and non case sensitive object names work in Netezza.

TESTDB(ADMIN)=> create table “Testing” (name char(10), “Name” char(10));
CREATE TABLE

TESTDB(ADMIN)=> select * from Testing;
ERROR: Relation ‘TESTING’ does not exist

TESTDB(ADMIN)=> select * from “Testing”;
NAME | Name
——+——
(0 rows)

TESTDB(ADMIN)=> select name from “Testing”;
NAME
——
(0 rows)

TESTDB(ADMIN)=> select name,”Name” from “Testing”;
NAME | Name
——+——
(0 rows)

TESTDB(ADMIN)=> select “name”,”Name” from “Testing”;
ERROR: Attribute ‘name’ not found

TESTDB(ADMIN)=> select “NAME”,”Name” from “Testing”;
NAME | Name
——+——
(0 rows)

TESTDB(ADMIN)=> drop table Testing;
ERROR: Relation ‘TESTING’ does not exist

TESTDB(ADMIN)=> drop table “Testing”;
DROP TABLE