The following bug has been logged online:
Bug reference: 2683
Logged by: Vitali Stupin
Email address: [EMAIL PROTECTED]
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.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
