Thread: execute prepared statement passing parameter expression with COLLATE clause
hi.
$Subject setup
CREATE COLLATION case_insensitive (provider = icu, locale =
'@colStrength=secondary', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale =
'@colStrength=primary;colCaseLevel=yes', deterministic = false);
DROP TABLE IF EXISTS pktable cascade;
CREATE TABLE pktable (x text COLLATE case_insensitive);
INSERT INTO pktable VALUES ('A');
DEALLOCATE q6;
PREPARE q6 AS SELECT * FROM pktable WHERE x = $1;
select * from pktable where x = 'Å' collate ignore_accents;
--return one row
execute q6('Å' collate ignore_accents);
--return zero rows
not sure return zero rows is desired.
Re: execute prepared statement passing parameter expression with COLLATE clause
From
Tender Wang
Date:
jian he <jian.universality@gmail.com> 于2024年10月24日周四 16:56写道：
hi.
$Subject setup
CREATE COLLATION case_insensitive (provider = icu, locale =
'@colStrength=secondary', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale =
'@colStrength=primary;colCaseLevel=yes', deterministic = false);
DROP TABLE IF EXISTS pktable cascade;
CREATE TABLE pktable (x text COLLATE case_insensitive);
INSERT INTO pktable VALUES ('A');
DEALLOCATE q6;
PREPARE q6 AS SELECT * FROM pktable WHERE x = $1;
select * from pktable where x = 'Å' collate ignore_accents;
--return one row
execute q6('Å' collate ignore_accents);
--return zero rows
not sure return zero rows is desired.
QUERY PLAN
---------------------------------------------------------
Seq Scan on pktable (cost=0.00..27.00 rows=7 width=32)
Filter: (x = 'Å'::text)
(2 rows)
postgres=# explain select * from pktable where x = 'Å' collate ignore_accents;
QUERY PLAN
---------------------------------------------------------
Seq Scan on pktable (cost=0.00..27.00 rows=7 width=32)
Filter: (x = 'Å'::text COLLATE ignore_accents)
(2 rows)
The filter expr in the two queries is different. And I debug the texteq; the collid is also different.
So the result of the two queries is different. I don't look execute more in details.
Thanks,
Tender Wang
jian he <jian.universality@gmail.com> writes:
> select * from pktable where x = 'Å' collate ignore_accents;
> --return one row
> execute q6('Å' collate ignore_accents);
> --return zero rows
> not sure return zero rows is desired.
The parameter symbol just represents a value, which does not
carry any collation information. The collation to use was
determined when the prepared statement was parsed, and is not
going to change on the basis of what you write in EXECUTE.
We could have a discussion about whether this is desirable,
but it's prett much moot, because this is how the SQL committee
designed SQL's collation feature. It's not going to change.
regards, tom lane