The Vertica Database Forums

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

HI!

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

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

Hi!


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