Substitution of convert function "{fn CONVERT(1, SQL_BIGINT)}" to text litreral '1'

From: Jacobo Sánchez López <jsanchez(at)denodo(dot)com>
To: pgsql-odbc(at)lists(dot)postgresql(dot)org
Subject: Substitution of convert function "{fn CONVERT(1, SQL_BIGINT)}" to text litreral '1'
Date: 2023-01-16 15:42:06
Message-ID: d121f2ef-7517-a3b0-92b0-b5d37922cbac@denodo.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi

    I am doing some tests and found that when using a query with the
convert function like

SELECT SUM({fn CONVERT(1, SQL_BIGINT)})

    It gets translated to

SELECT SUM('1')

    Which fails due to the aggregation function receiving a text
literal instead of a number.

    I have narrowed down to this line in convert.c
(https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=convert.c;h=d94fdb685df0a3430a13650f5140bb3bbb1d6272;hb=HEAD#l5744)
deciding to add the quotes:

5743 else if (isdigit((unsigned char) *pptr))
5744                     add_quote = TRUE;

    How expected is that convert function to produce a text literal
which makes the query to fail?

    The simpliest way to reproduce it I found is with a python script
using a DSN called PostgreSQL35W (tested with version 13.02.0000):

importpyodbc
query= "SELECT SUM({fn CONVERT(1, SQL_INTEGER)})"
connection= pyodbc.connect('DSN=PostgreSQL35W',autocommit=True)
cursor= connection.cursor()
cursor.execute(query)
forrowincursor.fetchall():
print(row)

Best regards,

Jacobo

Browse pgsql-odbc by date

  From Date Subject
Next Message Chigodaev, Aleksandr 2023-01-27 11:49:32 an error while compiling psqlODBC on linux
Previous Message postgresql.org 2022-12-09 07:30:02 End of year update!!