#449 A couple of queries producing syntax error

[Abbas Butt]

These queries have been taken form puzzle # 10 of JOE CELKO’S SQL PUZZLES & ANSWERS Second Edition.

Steps to reproduce

1. Create a table

CREATE TABLE Pensions(sin CHAR(10) NOT NULL, pen_year INTEGER NOT NULL, month_cnt INTEGER DEFAULT 0 NOT NULL CHECK (month_cnt BETWEEN 0 AND 12), earnings DECIMAL(8,2) DEFAULT 0.00 NOT NULL);

1. Create a view

CREATE VIEW PenPeriods (sin, start_year, end_year, earnings_tot)

AS SELECT P0.sin, P0.pen_year, P1.pen_year,

(SELECT SUM(earnings)

FROM Pensions AS P2

WHERE P2.sin = P0.sin

AND P2.pen_year BETWEEN P0.pen_year

AND P1.pen_year)

FROM Pensions AS P0, Pensions AS P1

WHERE P1.sin = P0.sin

AND P1.pen_year >= (P0.pen_year - 4)

AND 0 < ALL (SELECT month_cnt

FROM Pensions AS P3

WHERE P3.sin = P0.sin

AND P3.pen_year BETWEEN P0.pen_year

AND P1.pen_year)

AND 60 <= (SELECT SUM(month_cnt)

FROM Pensions AS P4

WHERE P4.sin = P0.sin

AND P4.pen_year BETWEEN P0.pen_year

AND P1.pen_year);

1. Do these inserts

INSERT INTO Pensions VALUES('1', 2006, 12, 10);

INSERT INTO Pensions VALUES('1', 2005, 12, 10);

INSERT INTO Pensions VALUES('1', 2004, 12, 10);

INSERT INTO Pensions VALUES('1', 2003, 12, 10);

INSERT INTO Pensions VALUES('1', 2002, 12, 10);

INSERT INTO Pensions VALUES('1', 2001, 0, 0);

INSERT INTO Pensions VALUES('2', 2006, 10, 1);

INSERT INTO Pensions VALUES('2', 2005, 10, 1);

INSERT INTO Pensions VALUES('2', 2004, 10, 1);

INSERT INTO Pensions VALUES('2', 2003, 10, 1);

INSERT INTO Pensions VALUES('2', 2002, 10, 1);

INSERT INTO Pensions VALUES('2', 2001, 10, 1);

INSERT INTO Pensions VALUES('3', 2005, 10, 1);

INSERT INTO Pensions VALUES('3', 2004, 10, 1);

INSERT INTO Pensions VALUES('3', 2003, 0, 0);

INSERT INTO Pensions VALUES('3', 2002, 10, 1);

INSERT INTO Pensions VALUES('3', 2001, 10, 1);

INSERT INTO Pensions VALUES('3', 2000, 10, 1);

INSERT INTO Pensions VALUES('4', 2006, 10, 1);

INSERT INTO Pensions VALUES('4', 2005, 10, 1);

INSERT INTO Pensions VALUES('4', 2004, 10, 1);

INSERT INTO Pensions VALUES('4', 2003, 10, 1);

INSERT INTO Pensions VALUES('4', 2002, 10, 1);

INSERT INTO Pensions VALUES('4', 2001, 0, 0);

INSERT INTO Pensions VALUES('4', 2000, 10, 1);

INSERT INTO Pensions VALUES('5', 2006, 10, 1);

INSERT INTO Pensions VALUES('5', 2005, 10, 1);

INSERT INTO Pensions VALUES('5', 2004, 10, 1);

INSERT INTO Pensions VALUES('5', 2003, 10, 1);

INSERT INTO Pensions VALUES('5', 2002, 10, 1);

INSERT INTO Pensions VALUES('5', 2001, 0, 0);

INSERT INTO Pensions VALUES('5', 2000, 10, 1);

INSERT INTO Pensions VALUES('6', 2004, 0, 1);

INSERT INTO Pensions VALUES('6', 2003, 12, 1);

INSERT INTO Pensions VALUES('6', 2002, 12, 1);

INSERT INTO Pensions VALUES('6', 2001, 12, 0);

INSERT INTO Pensions VALUES('6', 2000, 12, 1);

1. Run the query

SELECT P0.sin,

P0.pen_year AS start_year,

P2.pen_year AS end_year,

SUM(P1.earnings) AS sumofearning

FROM Pensions AS P0, Pensions AS P1, Pensions AS P2

WHERE P0.month_cnt > 0

AND P1.month_cnt > 0

AND P2.month_cnt > 0

AND P0.sin = P1.sin

AND P0.sin = P2.sin

AND P0.pen_year BETWEEN P2.pen_year - 59 AND (P2.pen_year - 4)

AND P1.pen_year BETWEEN P0.pen_year AND P2.pen_year

GROUP BY P0.sin, P0.pen_year, P2.pen_year

HAVING SUM(P1.month_cnt) >= 60

AND (P2.pen_year - P0.pen_year) = (COUNT(*) - 1) ORDER BY 1;

The query produces the following result on Postgres

sin     | start_year | end_year | earnings_tot

------------+------------+----------+--------------

1 | 2002 | 2006 | 50.00

2 | 2001 | 2006 | 6.00

(2 rows)

However on Postgres-XC it results in

ERROR: syntax error at or near ","

1. Now Run this query

SELECT

*,

MOD(total_month_cnt,12) AS nonutilized_first_year_month_cnt,

(total_month_cnt % 12) / (first_year_month_cnt * 1.0) * first_year_earnings AS first_year_adjustment,

earnings_tot - (MOD(total_month_cnt, 12))/(first_year_month_cnt * 1.0) * first_year_earnings AS adjusted_earnings_tot

FROM

(

SELECT P1.sin,

P1.pen_year AS first_year,

P2.pen_year AS last_year,

P1.month_cnt AS First_year_month_cnt,

P1.earnings AS first_year_earnings,

COUNT(P3.pen_year) AS year_cnt,

SUM(P3.month_cnt) AS total_month_cnt,

SUM(P3.earnings) AS earnings_tot

FROM Pensions AS P1

INNER JOIN Pensions AS P2

ON P1.sin = P2.sin

INNER JOIN Pensions AS P3

ON P1.sin = P3.sin

WHERE P3.pen_year BETWEEN P1.pen_year

AND P2.pen_year

AND P3.month_cnt > 0

GROUP BY P1.sin, P1.pen_year, P2.pen_year,

P1.month_cnt, P1.earnings

HAVING COUNT(P3.pen_year) = P2.pen_year - P1.pen_year + 1

AND SUM(P3.month_cnt) BETWEEN 60 AND 60 + P1.month_cnt - 1

) AS A

WHERE A.last_year =

(

SELECT MAX(last_year)

FROM

(

 SELECT P2.pen_year AS last_year

 FROM Pensions AS P1

 INNER JOIN Pensions AS P2

 ON P1.sin = P2.sin

 INNER JOIN Pensions AS P3

 ON P1.sin = P3.sin

 WHERE P3.pen_year BETWEEN P1.pen_year AND

 P2.pen_year

 AND P3.month_cnt > 0

 AND P1.sin = A.sin

 GROUP BY P1.sin, P1.pen_year, P2.pen_year,

 P1.month_cnt

 HAVING COUNT(P3.pen_year) = P2.pen_year -

 P1.pen_year + 1

 AND SUM(P3.month_cnt) BETWEEN 60 AND 60 +

 P1.month_cnt - 1

) AS B

)ORDER BY 1;

It results in the following results in Postgres
(These results have been broken down to multiple lines for readability)

sin | first_year | last_year | first_year_month_cnt | first_year_earnings

-----+------------+-----------+----------------------+---------------------

1 | 2002 | 2006 | 12 | 10.00

2 | 2001 | 2006 | 10 | 1.00

| year_cnt | total_month_cnt | earnings_tot | nonutilized_first_year_month_cnt
+----------+-----------------+--------------+----------------------------------
| 5 | 60 | 50.00 | 0
| 6 | 60 | 6.00 | 0

| first_year_adjustment | adjusted_earnings_tot
+-----------------------+---------------------------
| 0.0000000000000000000 | 50.0000000000000000000000
| 0.0000000000000000000 | 6.0000000000000000000000

(2 rows)

However on Postgres-XC it results in

ERROR: syntax error at or near ","

1. Now Run this query

WITH Ranges (sin, first_year, last_year, earnings_tot)

AS

(

SELECT

P1.sin,

P1.pen_year,

P2.pen_year,

SUM(P3.earnings) AS earnigns_tot

FROM Pensions AS P1

INNER JOIN Pensions AS P2

ON P1.sin = P2.sin

INNER JOIN Pensions AS P3

ON P1.sin = P3.sin

WHERE P3.pen_year BETWEEN P1.pen_year AND P2.pen_year

AND P3.month_cnt > 0

GROUP BY P1.sin, P1.pen_year, P2.pen_year, P1.month_cnt

HAVING SUM(P3.month_cnt) BETWEEN 60 AND 60 + P1.month_cnt - 1

AND COUNT(P3.pen_year) = P2.pen_year - P1.pen_year + 1

),

LastRange (sin, last_year) AS (SELECT sin, MAX(last_year) FROM Ranges GROUP BY sin)

SELECT R.* FROM Ranges AS R INNER JOIN LastRange AS L ON R.sin = L.sin AND R.last_year = L.last_year ORDER BY 1;

It results in the following results in Postgres

sin     | first_year | last_year | earnings_tot

------------+------------+-----------+--------------

1 | 2002 | 2006 | 50.00

2 | 2001 | 2006 | 6.00

(2 rows)

However on Postgres-XC it results in

ERROR: syntax error at or near ","