Content-Type: multipart/alternative;
 boundary="------------e5Wv2QXlSl7OM0PnNq6R0NR2"
Message-ID: <d121f2ef-7517-a3b0-92b0-b5d37922cbac@denodo.com>
Date: Mon, 16 Jan 2023 16:42:06 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.1
Content-Language: en-US
To: pgsql-odbc@lists.postgresql.org
From: =?UTF-8?Q?Jacobo_S=c3=a1nchez_L=c3=b3pez?= <jsanchez@denodo.com>
Subject: Substitution of convert function "{fn CONVERT(1, SQL_BIGINT)}" to
 text litreral '1'
Archived-At: 
 <https://www.postgresql.org/message-id/d121f2ef-7517-a3b0-92b0-b5d37922cbac%40denodo.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------e5Wv2QXlSl7OM0PnNq6R0NR2
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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


--------------e5Wv2QXlSl7OM0PnNq6R0NR2
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi</p>
    <p>    I am doing some tests and found that when using a query with
      the convert function like</p>
    <p>    <font face="monospace">SELECT SUM({fn CONVERT(1,
        SQL_BIGINT)})</font><br>
    </p>
    <p>    It gets translated to <br>
    </p>
    <p>   <font face="monospace">SELECT SUM('1')</font></p>
    <p>    Which fails due to the aggregation function receiving a text
      literal instead of a number.</p>
    <p>    I have narrowed down to this line in convert.c
(<a class="moz-txt-link-freetext" href="https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=convert.c;h=d94fdb685df0a3430a13650f5140bb3bbb1d6272;hb=HEAD#l5744">https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=convert.c;h=d94fdb685df0a3430a13650f5140bb3bbb1d6272;hb=HEAD#l5744</a>)
      deciding to add the quotes:<br>
    </p>
    <p><font face="monospace">5743                
        else if (isdigit((unsigned char) *pptr))<br>
        5744                     add_quote = TRUE;</font><br>
    </p>
    <p>    How expected is that convert function to produce a text
      literal which makes the query to fail?</p>
    <p><br>
    </p>
    <p>    The simpliest way to reproduce it I found is with a python
      script using a DSN called PostgreSQL35W (tested with version
      13.02.0000):</p>
    <p><br>
    </p>
    <div style="color: #d4d4d4;background-color: #1e1e1e;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;"><div><span style="color: #c586c0;">import</span><span style="color: #d4d4d4;"> </span><span style="color: #4ec9b0;">pyodbc</span><span style="color: #d4d4d4;"> </span></div>
<div>
</div><div><span style="color: #9cdcfe;">query</span><span style="color: #d4d4d4;"> = </span><span style="color: #ce9178;">"SELECT SUM({fn CONVERT(1, SQL_INTEGER)})"</span></div><div><span style="color: #9cdcfe;">connection</span><span style="color: #d4d4d4;"> = </span><span style="color: #4ec9b0;">pyodbc</span><span style="color: #d4d4d4;">.</span><span style="color: #dcdcaa;">connect</span><span style="color: #d4d4d4;">(</span><span style="color: #ce9178;">'DSN=PostgreSQL35W'</span><span style="color: #d4d4d4;">,</span><span style="color: #9cdcfe;">autocommit</span><span style="color: #d4d4d4;">=</span><span style="color: #569cd6;">True</span><span style="color: #d4d4d4;">)</span></div><span style="color: #9cdcfe;">cursor</span><span style="color: #d4d4d4;"> = </span><span style="color: #9cdcfe;">connection</span><span style="color: #d4d4d4;">.</span><span style="color: #dcdcaa;">cursor</span><span style="color: #d4d4d4;">()</span><span style="color: #9cdcfe;">
</span></div>
    <div style="color: #d4d4d4;background-color: #1e1e1e;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;"><span style="color: #9cdcfe;">cursor</span><span style="color: #d4d4d4;">.</span><span style="color: #dcdcaa;">execute</span><span style="color: #d4d4d4;">(</span><span style="color: #9cdcfe;">query</span><span style="color: #d4d4d4;">)</span><div><span style="color: #dcdcaa;"></span><span style="color: #d4d4d4;">
</span></div><div><span style="color: #c586c0;">for</span><span style="color: #d4d4d4;"> </span><span style="color: #9cdcfe;">row</span><span style="color: #d4d4d4;"> </span><span style="color: #c586c0;">in</span><span style="color: #d4d4d4;"> </span><span style="color: #9cdcfe;">cursor</span><span style="color: #d4d4d4;">.</span><span style="color: #dcdcaa;">fetchall</span><span style="color: #d4d4d4;">():</span></div><div><span style="color: #d4d4d4;">    </span><span style="color: #dcdcaa;">print</span><span style="color: #d4d4d4;">(</span><span style="color: #9cdcfe;">row</span><span style="color: #d4d4d4;">)</span></div>

</div>
    <p></p>
    <p><br>
    </p>
    <p>Best regards,</p>
    <p>Jacobo<br>
    </p>
    <p><br>
    </p>
  </body>
</html>

--------------e5Wv2QXlSl7OM0PnNq6R0NR2--