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
ERROR: b of OFFSET b clause is not supported for expressions
Moderator: NorbertKrupa
-
- Newbie
- Posts: 2
- Joined: Tue Oct 30, 2012 9:42 pm
Re: ERROR: b of OFFSET b clause is not supported for express
HI!
Query planner parsed the part "... ? OFFSET ? ..." as expression, try next:
1. Swap position between LIMIT and OFFSET
2. Define ORDER BY clause (mandatory)
Query planner parsed the part "... ? OFFSET ? ..." as expression, try next:
1. Swap position between LIMIT and OFFSET
2. Define ORDER BY clause (mandatory)
-
- Newbie
- Posts: 2
- Joined: Tue Oct 30, 2012 9:42 pm
Re: ERROR: b of OFFSET b clause is not supported for express
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
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
Re: ERROR: b of OFFSET b clause is not supported for express
Hi!
Try as done here [line 129] - just concatenate with plus operator. It's ugly, unsafe, I know, but this code works.
Try as done here [line 129] - just concatenate with plus operator. It's ugly, unsafe, I know, but this code works.