Horton

Thread

Commits

  1. Non-decimal integer literals

  1. SQL statements error in pgsql 16 but not in pgsql 12

    Frank Komsic <komsicf@shoeicanada.com> — 2024-07-15T22:57:24Z

    Hi,

I was trying to upgrade our database from version 12.18 to the latest 16.3

After upgrading, the application has reported errors or “junk info” in the sql command line.

Example SQL code:
Message: ERROR: 42601: trailing junk after numeric literal at or near "19749O"
SQL: SELECT bb,mass,classifier,lot
FROM lab.pt_f003_view
WHERE (lot = 19749OR lot= 19750)
ORDER BY lot,bb


The above sql command works in version 12 but does not work in version 16 due to missing space in “WHERE (lot = 19749OR lot= 19750)”

Why did this work before and now it does not work?

Thanks

Frank Komsic

  2. Re: SQL statements error in pgsql 16 but not in pgsql 12

    David G. Johnston <david.g.johnston@gmail.com> — 2024-07-15T23:23:42Z

    On Mon, Jul 15, 2024 at 3:57 PM Frank Komsic <komsicf@shoeicanada.com>
wrote:

> The above sql command works in version 12 but does not work in version 16
> due to missing space in “WHERE (lot = 19749OR lot= 19750)”
>
>
>
> Why did this work before and now it does not work?
>
>
>

It seems to be a side-effect of supporting non-decimal literals.

https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.com
Git: 6fcda9aba83449082124825b6d375c0a61e21c42

This comment in particular refers to a v15 change that is probably related
if you want to dive deeper.

https://www.postgresql.org/message-id/flat/1467947.1669851407%40sss.pgh.pa.us#66391c571b4db866e222450fb3fa9eb9

Suffice to say, we became more strict here since v12 and you will need to
modify the query accordingly.

David J.

  3. Re: SQL statements error in pgsql 16 but not in pgsql 12

    Bharani SV-forum <esteembsv-forum@yahoo.com> — 2024-07-16T14:56:26Z

     cross checkWHERE (lot = 19749 OR lot= 19750)

where the OR is tagged to numeric 19749.
    On Monday, July 15, 2024 at 06:58:06 PM EDT, Frank Komsic <komsicf@shoeicanada.com> wrote:  
 
  
Hi,
 
  
 
I was trying to upgrade our database from version 12.18 to the latest 16.3
 
  
 
After upgrading, the application has reported errors or “junk info” in the sql command line.
 
  
 
Example SQL code:
 
Message: ERROR: 42601: trailing junk after numeric literal at or near "19749O"
SQL: SELECT bb,mass,classifier,lot
FROM lab.pt_f003_view
WHERE (lot = 19749OR lot= 19750)
ORDER BY lot,bb
 
  
 
  
 
The above sql command works in version 12 but does not work in version 16 due to missing space in “WHERE (lot = 19749OR lot= 19750)”
 
  
 
Why did this work before and now it does not work?
 
  
 
Thanks
 
  
 
Frank Komsic

  4. Re: SQL statements error in pgsql 16 but not in pgsql 12

    David G. Johnston <david.g.johnston@gmail.com> — 2024-07-16T15:00:06Z

    On Tue, Jul 16, 2024 at 7:58 AM Bharani SV-forum <esteembsv-forum@yahoo.com>
wrote:

> cross check
> WHERE (lot = 19749 *OR* lot= 19750)
>
> where the OR is tagged to numeric 19749.
>
>
What does "tagged" mean here?

David J.

  5. Re: SQL statements error in pgsql 16 but not in pgsql 12

    Frank Komsic <komsicf@shoeicanada.com> — 2024-07-16T18:01:35Z

    Thank you for your reply.

I gather I will have to lookover the entire code and set the SQL statements correctly.

Thanks

Frank

From: David G. Johnston <david.g.johnston@gmail.com>
Date: Monday, July 15, 2024 at 7:24 PM
To: Frank Komsic <komsicf@shoeicanada.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: SQL statements error in pgsql 16 but not in pgsql 12
On Mon, Jul 15, 2024 at 3:57 PM Frank Komsic <komsicf@shoeicanada.com<mailto:komsicf@shoeicanada.com>> wrote:
The above sql command works in version 12 but does not work in version 16 due to missing space in “WHERE (lot = 19749OR lot= 19750)”

Why did this work before and now it does not work?


It seems to be a side-effect of supporting non-decimal literals.

https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.com
Git: 6fcda9aba83449082124825b6d375c0a61e21c42

This comment in particular refers to a v15 change that is probably related if you want to dive deeper.

https://www.postgresql.org/message-id/flat/1467947.1669851407%40sss.pgh.pa.us#66391c571b4db866e222450fb3fa9eb9

Suffice to say, we became more strict here since v12 and you will need to modify the query accordingly.

David J.

  6. Re: SQL statements error in pgsql 16 but not in pgsql 12

    Bharani SV-forum <esteembsv-forum@yahoo.com> — 2024-07-18T14:50:14Z

     tagged mean " add to something "
hope ur picture posted with the sql query, i can see asWHERE (lot = 19749OR lot= 19750)instead of 
WHERE (lot = 19749 OR lot= 19750)

Hope u have understood
    On Tuesday, July 16, 2024 at 11:00:54 AM EDT, David G. Johnston <david.g.johnston@gmail.com> wrote:  
 
 On Tue, Jul 16, 2024 at 7:58 AM Bharani SV-forum <esteembsv-forum@yahoo.com> wrote:

 cross checkWHERE (lot = 19749 OR lot= 19750)

where the OR is tagged to numeric 19749.


What does "tagged" mean here?
David J.

  7. Re: SQL statements error in pgsql 16 but not in pgsql 12

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-18T14:57:40Z

    Bharani SV-forum <esteembsv-forum@yahoo.com> writes:
>  tagged mean " add to something "
> hope ur picture posted with the sql query, i can see asWHERE (lot = 19749OR lot= 19750)instead of 
> WHERE (lot = 19749 OR lot= 19750)

That's not a great choice of word.  In any case, the point here
is that the SQL standard requires some whitespace between a number
and a following word, and starting from PG 15 we're enforcing that
to reduce ambiguity.

			regards, tom lane