Обсуждение: Substitution of convert function "{fn CONVERT(1, SQL_BIGINT)}" to text litreral '1'

Поиск

Список

Период

Сортировка

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

От

Jacobo Sánchez López

Дата:

16 января 2023 г., 15:42:06

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):

import pyodbc 
query = "SELECT SUM({fn CONVERT(1, SQL_INTEGER)})"
connection = pyodbc.connect('DSN=PostgreSQL35W',autocommit=True)
cursor = connection.cursor() 

cursor.execute(query) 
for row in cursor.fetchall():
    print(row)

Best regards,

Jacobo

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Substitution of convert function "{fn CONVERT(1, SQL_BIGINT)}" to text litreral '1'

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