Thread: syntax error at or near "ROWS"
PostgreSQL version: 16.4
Operating system: Microsoft Windows 10 Enterprise Version 10.0.19044
SQLGetInfo with SQL_DBMS_NAME says version 16.0.4 though
Driver PSQLODBC35W.DLL version 13.02.0000
This error occurs only with some statements when using binding parameters
through ODBC
This statement is an example of when the error occurs
SELECT "Id","Begin","End","Logfile" FROM "ServerSession" ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
but some more complex SQL statements (with `... OFFSET ? ROWS FETCH FIRST ? ROW ONLY`) works just fine
Having fixed values (e.g. `...OFFSET 100 ROWS FETCH FIRST 10 ROW ONLY`) works for all statements
I still haven't figured out the pattern what the differences really are that make some statements to work and some not to work (I haven't dug into the open source code at all (yet))
Operating system: Microsoft Windows 10 Enterprise Version 10.0.19044
SQLGetInfo with SQL_DBMS_NAME says version 16.0.4 though
Driver PSQLODBC35W.DLL version 13.02.0000
This error occurs only with some statements when using binding parameters
through ODBC
This statement is an example of when the error occurs
SELECT "Id","Begin","End","Logfile" FROM "ServerSession" ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
but some more complex SQL statements (with `... OFFSET ? ROWS FETCH FIRST ? ROW ONLY`) works just fine
Having fixed values (e.g. `...OFFSET 100 ROWS FETCH FIRST 10 ROW ONLY`) works for all statements
I still haven't figured out the pattern what the differences really are that make some statements to work and some not to work (I haven't dug into the open source code at all (yet))
/ Kristian
p.s.
I made a report to the "wrong" mail-list first where I was adviced to use this one
https://www.postgresql.org/message-id/3145581.1725892966%40sss.pgh.pa.us
d.s.
https://www.postgresql.org/message-id/3145581.1725892966%40sss.pgh.pa.us
d.s.
I have dug into this a bit more and turned on the postmaster log
This statement fails if the first parameter is "abc" but works with "abcde"
SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
The postmaster log with "abc" (cbColDef and cbValueMax both 3)
This statement fails if the first parameter is "abc" but works with "abcde"
SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
The postmaster log with "abc" (cbColDef and cbValueMax both 3)
2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116
2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY
The postmaster log with "abcde" (cbColDef and cbValueMax both 5)
2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY
2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde"
2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4
2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1' 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3
2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings depending of the size of earlier string but not always), but I won't speculate of the reasons
We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement
/ Kristian
So have you solved your issue ?
Dave Cramer
www.postgres.rocks
On Thu, 19 Sept 2024 at 06:35, Kristian Ivarsson <sten.kristian.ivarsson@gmail.com> wrote:
I have dug into this a bit more and turned on the postmaster log
This statement fails if the first parameter is "abc" but works with "abcde"
SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
The postmaster log with "abc" (cbColDef and cbValueMax both 3)2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 1162024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLYThe postmaster log with "abcde" (cbColDef and cbValueMax both 5)2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde"2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int42024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1' 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $32024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings depending of the size of earlier string but not always), but I won't speculate of the reasonsWe want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement/ Kristian
Hi Dave No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? is nonstandard SQL) The only reason I added the LIMIT-statement example was to show the differences in the behaviour When $1 is "abc" it seems like the rest comes out as $2::int4 and $3::int4 When $1 is "abcde" it seems like rest comes out as $2 and $3 Only "abcde" works with FETCH FIRST-statement Both "abc" and "abcde" works with the LIMIT-statement (it seems like the following parameters has different types though(as stated)) / Kristian > HI Kristian, > So have you solved your issue ? > Dave Cramer > www.postgres.rocks > > I have dug into this a bit more and turned on the postmaster log > > This statement fails if the first parameter is "abc" but works with "abcde" > > SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCHFIRST ? ROW ONLY > > The postmaster log with "abc" (cbColDef and cbValueMax both 3) > > 2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116 > > 2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile"= $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY > > The postmaster log with "abcde" (cbColDef and cbValueMax both 5) > > 2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession"WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY > > 2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1' > > By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde" > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession"WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4 > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1' > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession"WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3 > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1' > > There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings dependingof the size of earlier string but not always), but I won't speculate of the reasons > > We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement > > / Kristian
Hi Kristian,
As far as I know PostgreSQL does not support OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
Dave Cramer
www.postgres.rocks
On Thu, 19 Sept 2024 at 09:06, Kristian Ivarsson <sten.kristian.ivarsson@gmail.com> wrote:
Hi Dave
No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? is non standard SQL)
The only reason I added the LIMIT-statement example was to show the differences in the behaviour
When $1 is "abc" it seems like the rest comes out as $2::int4 and $3::int4
When $1 is "abcde" it seems like rest comes out as $2 and $3
Only "abcde" works with FETCH FIRST-statement
Both "abc" and "abcde" works with the LIMIT-statement (it seems like the following parameters has different types though (as stated))
/ Kristian
> HI Kristian,
> So have you solved your issue ?
> Dave Cramer
> www.postgres.rocks
> > I have dug into this a bit more and turned on the postmaster log
> > This statement fails if the first parameter is "abc" but works with "abcde"
> > SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
> > The postmaster log with "abc" (cbColDef and cbValueMax both 3)
> > 2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116
> > 2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY
> > The postmaster log with "abcde" (cbColDef and cbValueMax both 5)
> > 2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY
> > 2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
> > By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde"
> > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4
> > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1'
> > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3
> > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
> > There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings depending of the size of earlier string but not always), but I won't speculate of the reasons
> > We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement
> > / Kristian
Hi Dave Well, as you can see, it works occasionally and it always works with fixed values You can read more about it here https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-fetch/ / Kristian > Hi Kristian, > As far as I know PostgreSQL does not support OFFSET ? ROWS FETCH FIRST ? ROWS ONLY > Dave Cramer > www.postgres.rocks > On Thu, 19 Sept 2024 at 09:06, Kristian Ivarsson <sten.kristian.ivarsson@gmail.com<mailto:sten.kristian.ivarsson@gmail.com>>wrote: > Hi Dave > No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? isnon standard SQL) > The only reason I added the LIMIT-statement example was to show the differences in the behaviour > When $1 is "abc" it seems like the rest comes out as $2::int4 and $3::int4 > When $1 is "abcde" it seems like rest comes out as $2 and $3 > Only "abcde" works with FETCH FIRST-statement > Both "abc" and "abcde" works with the LIMIT-statement (it seems like the following parameters has different types though(as stated)) > / Kristian > > HI Kristian, > > So have you solved your issue ? > > Dave Cramer > > www.postgres.rocks > > > I have dug into this a bit more and turned on the postmaster log > > > This statement fails if the first parameter is "abc" but works with "abcde" > > > SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCHFIRST ? ROW ONLY > > > The postmaster log with "abc" (cbColDef and cbValueMax both 3) > > > 2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116 > > > 2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile"= $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY > > > The postmaster log with "abcde" (cbColDef and cbValueMax both 5) > > > 2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession"WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY > > > 2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1' > > > By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde" > > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession"WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4 > > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1' > > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession"WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3 > > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1' > > > There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings dependingof the size of earlier string but not always), but I won't speculate of the reasons > > > We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement > > > / Kristian
Ah,
I see the problem is you are trying to use parameters
Can you post this as a bug on postgresql-interfaces/psqlodbc (github.com)
Dave Cramer
www.postgres.rocks
On Thu, 19 Sept 2024 at 09:28, Kristian Ivarsson <sten.kristian.ivarsson@gmail.com> wrote:
Hi Dave
Well, as you can see, it works occasionally and it always works with fixed values
You can read more about it here https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-fetch/
/ Kristian
> Hi Kristian,
> As far as I know PostgreSQL does not support OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
> Dave Cramer
> www.postgres.rocks
> On Thu, 19 Sept 2024 at 09:06, Kristian Ivarsson <sten.kristian.ivarsson@gmail.com<mailto:sten.kristian.ivarsson@gmail.com>> wrote:
> Hi Dave
> No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? is non standard SQL)
> The only reason I added the LIMIT-statement example was to show the differences in the behaviour
> When $1 is "abc" it seems like the rest comes out as $2::int4 and $3::int4
> When $1 is "abcde" it seems like rest comes out as $2 and $3
> Only "abcde" works with FETCH FIRST-statement
> Both "abc" and "abcde" works with the LIMIT-statement (it seems like the following parameters has different types though (as stated))
> / Kristian
> > HI Kristian,
> > So have you solved your issue ?
> > Dave Cramer
> > www.postgres.rocks
> > > I have dug into this a bit more and turned on the postmaster log
> > > This statement fails if the first parameter is "abc" but works with "abcde"
> > > SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
> > > The postmaster log with "abc" (cbColDef and cbValueMax both 3)
> > > 2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116
> > > 2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY
> > > The postmaster log with "abcde" (cbColDef and cbValueMax both 5)
> > > 2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY
> > > 2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
> > > By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde"
> > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4
> > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1'
> > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3
> > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'
> > > There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings depending of the size of earlier string but not always), but I won't speculate of the reasons
> > > We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement
> > > / Kristian