jian he <[email protected]> 于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
