Skip site navigation (1) Skip section navigation (2)

BUG #2683: spi_exec_query in plperl returns column names which are not marked as UTF8

From: "Vitali Stupin" <Vitali(dot)Stupin(at)ria(dot)ee>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2683: spi_exec_query in plperl returns column names which are not marked as UTF8
Date: 2006-10-10 07:35:53
Message-ID: 200610100735.k9A7ZrqL060997@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
The following bug has been logged online:

Bug reference:      2683
Logged by:          Vitali Stupin
Email address:      Vitali(dot)Stupin(at)ria(dot)ee
PostgreSQL version: 8.1.4
Operating system:   sparc-sun-solaris2.10
Description:        spi_exec_query in plperl returns column names which are
not marked as UTF8
Details: 

If database uses UTF8 encoding, then spi_exec_query in plperl should return
query results in UTF8 encoding. But unfortunately only data is marked as
UTF8, while column names are not.

The following test function demonstrates this bug:
CREATE OR REPLACE FUNCTION spi_test("varchar")
  RETURNS varchar AS
$BODY$
my $result = spi_exec_query($_[0]);
my @row_keys = keys %{$result->{rows}};
elog(WARNING, 'Column name: "' . $row_keys[0] . '" is UTF8: ' .
(utf8::is_utf8($row_keys[0]) ? 'y' : 'n'));
my $test_result = join(',', @row_keys) . '|';
for $row_key (@row_keys){
	$value = $result->{rows}[0]->{$row_key};
	elog(WARNING, 'Value: "' . $value . '" is UTF8: ' . (utf8::is_utf8($value)
? 'y' : 'n'));
	$test_result .= $value . '|';
}
elog(WARNING, 'Result: "' . $test_result . '" is UTF8: ' .
(utf8::is_utf8($test_result) ? 'y' : 'n'));
return $test_result;
$BODY$
  LANGUAGE 'plperlu' VOLATILE;

When it is called as:
select spi_test('select ''val_'' AS "col_"')

The following output is produced:
"col_|val_|"

And the generated warnings are:
WARNING:  Column name: "col_" is UTF8: n
WARNING:  Value: "val_" is UTF8: y
WARNING:  Result: "col_|val_|" is UTF8: y


Therefore it is possible to make a conclusion, that after execution of
query, column names contain a valid UTF8 string without UTF8 flag. When two
strings, one of which is marked as UTF8 and the other marked as non UTF8,
are concatenated, Perl automatically tries to convert column name into UTF8
as if it was in ISO-8859-1 encoding. As a result new string contains invalid
column name.

This bug can also be reproduced if spi_exec_query makes SELECT from the
actual table, when column names contain UTF8 characters.

This bug was also reproduced on:
PostgreSQL 8.1.3 on sparc-sun-solaris2.10;
PostgreSQL 8.1.3 on i686-pc-linux-gnu.

Responses

pgsql-hackers by date

Next:From: Per JensenDate: 2006-10-10 09:27:06
Subject: Test of 8.2beta1 fails
Previous:From: Merlin MoncureDate: 2006-10-10 05:48:20
Subject: Re: array_accum aggregate

pgsql-bugs by date

Next:From: Milen A. RadevDate: 2006-10-10 10:22:35
Subject: BUG #2684: Memory leak in libpq
Previous:From: Jaime CasanovaDate: 2006-10-08 01:53:24
Subject: Re: BUG #2681: duplicate key violates unique constraint

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group