ERROR 5600: Invalid predicate in projection-select. Only PK

Moderator: NorbertKrupa

Post Reply
Roopalini
Newbie
Newbie
Posts: 13
Joined: Sun Jan 26, 2014 9:40 pm

ERROR 5600: Invalid predicate in projection-select. Only PK

Post by Roopalini » Tue Apr 08, 2014 10:50 pm

Forum,

I have the following two tables (ORDER_DETAIL and LU_EMPLOYEE)

Schemas of the two tables are mentioned below

Code: Select all

dbadmin=> \d superapp.order_detail
                                        List of Fields by Tables
  Schema  |    Table     |    Column    | Type  | Size | Default | Not Null | Primary Key | Foreign Key
----------+--------------+--------------+-------+------+---------+----------+-------------+-------------
 superapp | ORDER_DETAIL | ORDER_ID     | int   |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | ITEM_ID      | int   |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | ORDER_DATE   | date  |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | EMP_ID       | int   |    8 |         | t        | t           |
 superapp | ORDER_DETAIL | PROMOTION_ID | int   |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | QTY_SOLD     | float |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | UNIT_PRICE   | float |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | UNIT_COST    | float |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | DISCOUNT     | float |    8 |         | f        | f           |
 superapp | ORDER_DETAIL | CUSTOMER_ID  | int   |    8 |         | f        | f           |
(10 rows)

Code: Select all

dbadmin=> \d superapp.lu_employee
                                                    List of Fields by Tables
  Schema  |    Table    |     Column     |    Type     | Size | Default | Not Null | Primary Key |          Foreign Key
----------+-------------+----------------+-------------+------+---------+----------+-------------+-------------------------------
 superapp | LU_EMPLOYEE | EMP_ID         | int         |    8 |         | f        | f           | superapp.ORDER_DETAIL(EMP_ID)
 superapp | LU_EMPLOYEE | EMP_LAST_NAME  | varchar(50) |   50 |         | f        | f           |
 superapp | LU_EMPLOYEE | EMP_FIRST_NAME | varchar(50) |   50 |         | f        | f           |
 superapp | LU_EMPLOYEE | EMP_SSN        | varchar(50) |   50 |         | f        | f           |
 superapp | LU_EMPLOYEE | BIRTH_DATE     | date        |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | HIRE_DATE      | date        |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | SALARY         | int         |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | COUNTRY_ID     | int         |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | DIST_CTR_ID    | int         |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | MANAGER_ID     | int         |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | LOCATION_ID    | int         |    8 |         | f        | f           |
 superapp | LU_EMPLOYEE | FTE_FLAG       | varchar(50) |   50 |         | f        | f           |

I am trying to create a projection like the one below and get the error ERROR 5600: Invalid predicate in projection-select. Only PK. I have the PK and FK created properly , then why am I not able to create the projection.

Code: Select all

CREATE PROJECTION superapp.order_details_proj_manual_1
(
EMP_ID,
ITEM_ID,
QTY_SOLD,
UNIT_PRICE,
DISCOUNT,
UNIT_COST
)
AS
SELECT
        a11.EMP_ID,
        a11.ITEM_ID,
        a11.QTY_SOLD,
        a11.UNIT_PRICE,
        a11.DISCOUNT,
        a11.UNIT_COST
FROM
        SUPERAPP.ORDER_DETAIL a11 join SUPERAPP.LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
WHERE
        a12.emp_id is not null
ORDER BY
        a11.EMP_ID 
SEGMENTED BY hash(ORDER_DETAIL.EMP_ID) ALL NODES;
Thanks

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: ERROR 5600: Invalid predicate in projection-select. Onl

Post by NorbertKrupa » Wed Apr 09, 2014 1:06 am

Just looking at your table definitions, your foreign key is not defined as NOT NULL. Also, I don't believe you can do the segmentation on the pre-join projection. This should be done on the projections on the fact and dimension table.

You may want to take a look at my post on pre-join projections.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: ERROR 5600: Invalid predicate in projection-select. Onl

Post by scutter » Wed Apr 09, 2014 3:36 am

SUPERAPP.ORDER_DETAIL a11 join SUPERAPP.LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
WHERE
a12.emp_id is not null

You can’t include a WHERE clause in the prejoin projection definition. This particular clause doesn’t add any value anyway - the join is an equality join on EMP_ID, and NULLs can’t satisfy equality.

The segmentation is ok - you just need to use a11 instead of order_detail.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Roopalini
Newbie
Newbie
Posts: 13
Joined: Sun Jan 26, 2014 9:40 pm

Re: ERROR 5600: Invalid predicate in projection-select. Onl

Post by Roopalini » Wed Apr 09, 2014 11:12 pm

Thanks for your responses. It is still not working. I removed the WHERE clause 'WHERE a12.emp_id is not null' and made the FK as NOT NULL as well , however I still get error when I try to create the projection "failed: projection is unsafe". I understood from another post that we need to mention "ksafe 1" in the create projection statement, but that didn't fix it either.

-[ RECORD 1 ]---------+-----------------------------
node_name | v_c000_temp_wh_node0001
projection_schema | superapp
projection_id | 45035996277023420
projection_name | order_details_proj_manual_1
anchor_table_name | LU_EMPLOYEE
refresh_status | failed: projection is unsafe
refresh_phase |
refresh_method |
refresh_failure_count | 1
session_id | c000vert01na01-58206:0xd136e
refresh_start | 2000-01-01 00:00:00+00
refresh_duration_sec | 450367643
is_executing | f
runtime_priority |
transaction_id |
-[ RECORD 2 ]---------+-----------------------------


K SAFE is set to 1 in the node

select designed_fault_tolerance from system;
-[ RECORD 1 ]------------+--
designed_fault_tolerance | 1

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: ERROR 5600: Invalid predicate in projection-select. Onl

Post by NorbertKrupa » Thu Apr 10, 2014 1:20 am

Here's how I got it to work:

Table set up

Code: Select all

-- dimension table
CREATE TABLE public.order_detail (
order_id int,
item_id int,
order_date date,
emp_id int NOT NULL PRIMARY KEY,
promotion_id int,
qty_sold float,
unit_price float,
unit_cost float,
discount float,
customer_id int);

-- fact table
CREATE TABLE public.lu_employee (
emp_id int,
emp_last_name varchar(50),
emp_first_name varchar(50),
emp_ssn varchar(50),
birth_date date,
hire_date date,
salary int,
country_id int,
dist_ctr_id int,
manager_id int,
location_id int,
fte_flag varchar(50));

ALTER TABLE public.lu_employee
ADD CONSTRAINT fk_lu_employee FOREIGN KEY (emp_id)
REFERENCES public.order_detail (emp_id);
Attempt 1

Code: Select all

CREATE PROJECTION public.order_details_proj_manual_1 (
    EMP_ID,
    ITEM_ID,
    QTY_SOLD,
    UNIT_PRICE,
    DISCOUNT,
    UNIT_COST)
AS
SELECT
    a11.EMP_ID,
    a11.ITEM_ID,
    a11.QTY_SOLD,
    a11.UNIT_PRICE,
    a11.DISCOUNT,
    a11.UNIT_COST
FROM
    public.order_detail a11 
JOIN public.lu_employee a12 
    ON a11.EMP_ID = a12.EMP_ID
WHERE
    a12.emp_id is not null
ORDER BY
    a11.EMP_ID 
SEGMENTED BY hash(ORDER_DETAIL.EMP_ID) ALL NODES;
ERROR 5600: Invalid predicate in projection-select. Only PK=FK equijoins are allowed
Attempt 2

We remove the predicate.

Code: Select all

CREATE PROJECTION public.order_details_proj_manual_1 (
    EMP_ID,
    ITEM_ID,
    QTY_SOLD,
    UNIT_PRICE,
    DISCOUNT,
    UNIT_COST)
AS
SELECT
    a11.EMP_ID,
    a11.ITEM_ID,
    a11.QTY_SOLD,
    a11.UNIT_PRICE,
    a11.DISCOUNT,
    a11.UNIT_COST
FROM
    public.order_detail a11 
JOIN public.lu_employee a12 
    ON a11.EMP_ID = a12.EMP_ID
ORDER BY
    a11.EMP_ID 
SEGMENTED BY hash(ORDER_DETAIL.EMP_ID) ALL NODES;
ERROR 5630: Nullable FKs are not allowed in projection definition
Attempt 3

We set that column to NOT NULL and try again.

Code: Select all

ALTER TABLE public.lu_employee ALTER COLUMN emp_id SET NOT NULL;

CREATE PROJECTION public.order_details_proj_manual_1 (
    EMP_ID,
    ITEM_ID,
    QTY_SOLD,
    UNIT_PRICE,
    DISCOUNT,
    UNIT_COST)
AS
SELECT
    a11.EMP_ID,
    a11.ITEM_ID,
    a11.QTY_SOLD,
    a11.UNIT_PRICE,
    a11.DISCOUNT,
    a11.UNIT_COST
FROM
    public.order_detail a11 
JOIN public.lu_employee a12 
    ON a11.EMP_ID = a12.EMP_ID
ORDER BY
    a11.EMP_ID 
SEGMENTED BY hash(ORDER_DETAIL.EMP_ID) ALL NODES;
ERROR 3953: Missing FROM-clause entry for table "ORDER_DETAIL"
Attempt 4

We remove the segmentation.

Code: Select all

CREATE PROJECTION public.order_details_proj_manual_1 (
    EMP_ID,
    ITEM_ID,
    QTY_SOLD,
    UNIT_PRICE,
    DISCOUNT,
    UNIT_COST)
AS
SELECT
    a11.EMP_ID,
    a11.ITEM_ID,
    a11.QTY_SOLD,
    a11.UNIT_PRICE,
    a11.DISCOUNT,
    a11.UNIT_COST
FROM
    public.order_detail a11
JOIN public.lu_employee a12 
    ON a11.EMP_ID = a12.EMP_ID
ORDER BY
    a11.EMP_ID;
WARNING 4116: No super projections created for table public.lu_employee.
HINT: Default super projections will be automatically created with the next DML
CREATE PROJECTION
Success!

As mentioned in the warning message, default projections should be created unless you're fine with default super projections.

A few other thoughts... I find it curious that the fte_flag is a varchar(50) and not a boolean type. Also, the SSN isn't being stored in plain text, right?
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: ERROR 5600: Invalid predicate in projection-select. Onl

Post by scutter » Thu Apr 10, 2014 1:41 am

> ERROR 3953: Missing FROM-clause entry for table “ORDER_DETAIL"

You don’t need to remove the segmentation. Just change the ORDER_DETAIL to a11.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: ERROR 5600: Invalid predicate in projection-select. Onl

Post by NorbertKrupa » Thu Apr 10, 2014 2:09 pm

That would make a lot more sense. This worked successfully:

Code: Select all

CREATE PROJECTION public.order_details_proj_manual_1 (
    EMP_ID,
    ITEM_ID,
    QTY_SOLD,
    UNIT_PRICE,
    DISCOUNT,
    UNIT_COST)
AS
SELECT
    a11.EMP_ID,
    a11.ITEM_ID,
    a11.QTY_SOLD,
    a11.UNIT_PRICE,
    a11.DISCOUNT,
    a11.UNIT_COST
FROM
    public.order_detail a11 
JOIN public.lu_employee a12 
    ON a11.EMP_ID = a12.EMP_ID
ORDER BY
    a11.EMP_ID 
SEGMENTED BY hash(a11.EMP_ID) ALL NODES;
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica Database Development”