Re: execute prepared statement passing parameter expression with COLLATE clause

jian he <jian(dot)universality(at)gmail(dot)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.
>
>
postgres=# explain execute q6('Å' collate ignore_accents);
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