Re: Re: [BUGS] BUG #11767: ODBC driver bug when fetching constant string columns

On 12/02/2014 02:28 PM, Faith, Jeremy wrote:
>> On Fri, Oct 24, 2014 at 9:36 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
>> wrote:
>>
>>> On Wed, Oct 22, 2014 at 11:22 PM,  <keyurgovande(at)gmail(dot)com> wrote:
>>>> The following bug has been logged on the website:
>>>>
>>>> Bug reference:      11767
>>>> Logged by:          Keyur Govande
>>>> Email address:      keyurgovande(at)gmail(dot)com
>>>> PostgreSQL version: 9.0.3
>>> You should update to 9.0.18, you are 3.5 years of bug fixes.
>>>
>>
>> Ah I apologize, I chose the wrong version in the bug report dropdown. This
>> bug was observed with the latest driver: psqlodbc-09.03.0300 connecting to
>> a Postgres server 9.1.14.
>>
> I can confirm that this bug exists in psqlodbc-09.03.0300 connecting to PostgreSQL 9.3.5.

Ok, thanks for the testing.

>>>> If we run a SELECT statement like: SELECT id, varchar_col, date_col,
>>> 'random
>>>> string' as random;
>>>> via ODBC, then the last column comes back as junk into PHP.
>>>>
>>>> In PHP we use the SQL_DESC_OCTET_LENGTH to figure out how many bytes to
>>>> allocate. But for that column, SQL_DESC_OCTET_LENGTH returns 0 and we
>>> don't
>>>> allocate enough space.

(https://bugs.php.net/bug.php?id=68087)

Hmm. Why does that cause junk data to be returned, though? Even if you
allocate a single byte of storage, that's enough for the
null-terminator, and you should just end up with an empty string, not junk.

>>>> The driver issue seems to be that when SQLColAttributes() is called with
>>>> SQL_COLUMN_TYPE for column 'random', it returns SQL_VARCHAR even though
>>> the
>>>> PG type is PG_TYPE_UNKNOWN. The promotion is happening here:
>>>>
>>>
http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l665
>>>>
>>>> I think the same promotion needs to happen when fetching octet-length
>>> here:
>>>>
>>>
http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l1275
>>>>
>>>> Thoughts? This is not a problem when using the Vertica ODBC driver for
>>>> example.

Yeah, we should fix that in psqlodbc. We can't really determine a sane
max size for the column, but we should do the same for unknown that we
do for text or varchar columns. I believe the attached patch would fix
this. The fix itself is a single line, but it includes a test case.

Note that a text (or unknown) column can hold up to 1 GB of text. By the
letter of the spec, I think SQL_DESC_OCTET_LENGTH should return 1GB for
a text column (or an unknown-type column), but that would be
impractical. So the driver just makes a guess. The data will be
truncated, if the value in the column is long enough. It would be good
to check for SQLSTATE=01004 result from SQLFetch, and perhaps resize the
buffers or give a warning or error, if truncation happens.

- Heikki