Thread: syntax error at or near "ROWS"

syntax error at or near "ROWS"

From
Kristian Ivarsson
Date:
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))


/ 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.

Re: syntax error at or near "ROWS"

From
Kristian Ivarsson
Date:
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

Re: syntax error at or near "ROWS"

From
Dave Cramer
Date:

HI 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 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

Re: syntax error at or near "ROWS"

From
Kristian Ivarsson
Date:
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



Re: syntax error at or near "ROWS"

From
Dave Cramer
Date:
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


Re: syntax error at or near "ROWS"

From
Kristian Ivarsson
Date:
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





Re: syntax error at or near "ROWS"

From
Dave Cramer
Date:
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