From: | Tender Wang <tndrwang(at)gmail(dot)com> |
---|---|
To: | jian he <jian(dot)universality(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: execute prepared statement passing parameter expression with COLLATE clause |
Date: | 2024-10-24 09:19:57 |
Message-ID: | CAHewXNm9DRgn2zGb6mZKBw_m+Kk40k+vqBC1nK86KpWF=j_VbA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Morrissette Vance | 2024-10-24 09:55:01 | Re: Commutation of array SOME/ANY and ALL operators |
Previous Message | Steven Niu | 2024-10-24 09:09:46 | Re: Use function smgrclose() to replace the loop |