ERROR: b of OFFSET b clause is not supported for expressions

Moderator: NorbertKrupa

Post Reply
eanders94040
Newbie
Newbie
Posts: 2
Joined: Tue Oct 30, 2012 9:42 pm

ERROR: b of OFFSET b clause is not supported for expressions

Post by eanders94040 » Tue Oct 30, 2012 9:49 pm

I'm connecting to Vertica thru JDBC, and I'm getting the below error while calling Connection.prepareStatement on the below SQL. Does anyone know what the error means and how to fix it?

java.sql.Connection Error calling Connection.prepareStatement:
java.sql.SQLException: [Vertica][VJDBC](2013) ERROR: b of OFFSET b clause is not supported for expressions


SELECT COALESCE(COUNT(main.v0), 0) AS CollapsingFunction0
FROM
(SELECT p.ID AS v0,
p.RatingAvgApproved AS v1,
p.ReviewCountApproved AS v2,
p.RatingAvgRejected AS v3,
p.ReviewCountRejected AS v4,
p.Views_all_dates AS v5,
p.Views_365_days AS v6,
p.Visitors_30_days AS v7,
COALESCE(COUNT(r.ID), 0) AS v8,
COALESCE(AVG(r.Rating), 0) AS v9
FROM cabelas.Review r
JOIN
(SELECT nr.ProductID
FROM cabelas.NotificationReason nr
WHERE nr.UserID = ?) pstReviewFilterTable ON pstReviewFilterTable.ProductID = r.ProductID
JOIN cabelas.Product p ON p.ID = r.ProductID
JOIN
(SELECT nr.ProductID
FROM cabelas.NotificationReason nr
WHERE nr.UserID = ?) pstProductFilterTable ON pstProductFilterTable.ProductID = p.ID
WHERE 1 = 1
AND r.Status IN (?)
AND r.ProductInactive = ?
AND r.SubmissionTime >= ?
AND r.SubmissionTime < ?
GROUP BY p.ID,
p.RatingAvgApproved,
p.ReviewCountApproved,
p.RatingAvgRejected,
p.ReviewCountRejected,
p.Views_all_dates,
p.Views_365_days,
p.Visitors_30_days
HAVING COUNT(r.ID) >= ?
LIMIT ?
OFFSET ?) main

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: ERROR: b of OFFSET b clause is not supported for express

Post by id10t » Tue Oct 30, 2012 10:20 pm

HI!

Query planner parsed the part "... ? OFFSET ? ..." as expression, try next:
1. Swap position between LIMIT and OFFSET
2. Define ORDER BY clause (mandatory)

eanders94040
Newbie
Newbie
Posts: 2
Joined: Tue Oct 30, 2012 9:42 pm

Re: ERROR: b of OFFSET b clause is not supported for express

Post by eanders94040 » Wed Oct 31, 2012 1:25 am

OO, I thought the adding an ORDER BY suggestion was going to work, but alas, no dice. And I reversed the LIMIT and OFFSET. Updated query below. Still the same error.



SELECT COALESCE(COUNT(main.v0), 0) AS CollapsingFunction0
FROM
(SELECT p.ID AS v0,
p.RatingAvgApproved AS v1,
p.ReviewCountApproved AS v2,
p.RatingAvgRejected AS v3,
p.ReviewCountRejected AS v4,
p.Views_all_dates AS v5,
p.Views_365_days AS v6,
p.Visitors_30_days AS v7,
COALESCE(COUNT(r.ID), 0) AS v8,
COALESCE(AVG(r.Rating), 0) AS v9
FROM cabelas.Review r
JOIN
(SELECT nr.ProductID
FROM cabelas.NotificationReason nr
WHERE nr.UserID = ?) pstReviewFilterTable ON pstReviewFilterTable.ProductID = r.ProductID
JOIN cabelas.Product p ON p.ID = r.ProductID
JOIN
(SELECT nr.ProductID
FROM cabelas.NotificationReason nr
WHERE nr.UserID = ?) pstProductFilterTable ON pstProductFilterTable.ProductID = p.ID
WHERE 1 = 1
AND r.Status IN (?)
AND r.ProductInactive = ?
AND r.SubmissionTime >= ?
AND r.SubmissionTime < ?
GROUP BY p.ID,
p.RatingAvgApproved,
p.ReviewCountApproved,
p.RatingAvgRejected,
p.ReviewCountRejected,
p.Views_all_dates,
p.Views_365_days,
p.Visitors_30_days
HAVING COUNT(r.ID) >= ?
ORDER BY p.ReviewCountApproved ASC,p.ID ASC
OFFSET ? LIMIT ?) main

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: ERROR: b of OFFSET b clause is not supported for express

Post by id10t » Wed Oct 31, 2012 5:47 am

Hi!


Try as done here [line 129] - just concatenate with plus operator. It's ugly, unsafe, I know, but this code works.

Post Reply

Return to “JDBC”