Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VKUfN-0007sz-MD for pgsql-general@arkaria.postgresql.org; Fri, 13 Sep 2013 14:48:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VKUfN-0000qh-6N for pgsql-general@arkaria.postgresql.org; Fri, 13 Sep 2013 14:48:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VKUfL-0000qW-G4 for pgsql-general@postgresql.org; Fri, 13 Sep 2013 14:48:11 +0000 Received: from mail01.pnet.xcon.it ([62.48.53.71]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VKUfE-0001ha-D6 for pgsql-general@postgresql.org; Fri, 13 Sep 2013 14:48:11 +0000 Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by mail01.pnet.xcon.it (Postfix) with ESMTP id 913AC30136EBF for ; Fri, 13 Sep 2013 16:48:03 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from mail01.pnet.xcon.it ([10.68.1.19]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id QIlraT3Tefsm for ; Fri, 13 Sep 2013 16:47:24 +0200 (CEST) Received: from bnc1.pnet.xcon.it (bnc1.pnet.xcon.it [10.68.1.23]) by mail01.pnet.xcon.it (Postfix) with ESMTP id 7156430136373 for ; Fri, 13 Sep 2013 16:48:03 +0200 (CEST) Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by bnc1.pnet.xcon.it (Postfix) with ESMTP id 619A81022E23 for ; Fri, 13 Sep 2013 16:48:03 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from bnc1.pnet.xcon.it ([10.68.1.23]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id uqEGCX0Yl4zN for ; Fri, 13 Sep 2013 16:47:22 +0200 (CEST) Received: from [192.168.0.147] (unknown [83.149.163.202]) (Authenticated sender: giuseppe.broccolo@2ndquadrant.it) by bnc1.pnet.xcon.it (Postfix) with ESMTPSA id 276551022E36 for ; Fri, 13 Sep 2013 16:48:02 +0200 (CEST) Message-ID: <52332613.2020904@2ndquadrant.it> Date: Fri, 13 Sep 2013 16:49:55 +0200 From: Giuseppe Broccolo User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130804 Thunderbird/17.0.8 MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: problem with query References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------080107040409050405050901" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multi-part message in MIME format. --------------080107040409050405050901 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Il 12/09/2013 22:34, Roberto Scattini ha scritto: > hi, today we discovered that this query doesn't return the expected > values: > > SELECT DISTINCT > p.id , p.apellido AS "Apellido", p.nombre AS "Nombre", > pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS > "Fecha Ingreso PG", e.estado AS "Estado", to_char(pe.fecha, > 'dd/mm/yyyy') AS "Fecha Estado" > FROM personal.personas AS p > LEFT JOIN personal.personas_da_pg AS da ON p.id > =da.id_persona > LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id > > LEFT JOIN personal.estados AS e ON pe.id_estado=e.id > LEFT JOIN procu_departamento AS d ON d.id > =da.id_departamento > LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama, > 4)||'000'=dto.c_organigrama > LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama, > 3)||'0000'=dir.c_organigrama > LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama, > 2)||'00000'=dg.c_organigrama > LEFT JOIN personal.funciones AS pf ON pf.id =da.funcion_id > LEFT JOIN personal.profesiones AS pp ON pp.id > =p.id_profesion > WHERE p.apellido ilike '%nuñez%' > ORDER BY "Apellido" The problem is the encoding: SQL_ASCII encodes only the first 128 characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with SQL_ASCII encoding, they will be seen as two distinct characters: ascii_test=# SELECT length(E'ñ'); length -------- 2 (1 row) ascii_test=# SELECT length(E'Ñ'); length -------- 2 (1 row) ascii_test=# SELECT 'ñ'::bytea; bytea -------- \xc3b1 (1 row) ascii_test=# SELECT 'Ñ'::bytea; bytea -------- \xc391 (1 row) Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply case insensitive pattern matching to data which does not encode any string (in the SQL_ASCII encoding) and works as a normal LIKE. Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ' and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway, and problem remains. The best way is to work with a UTF8 encoded database. Is there a particular reason to work with SQL_ASCII encoding? Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it --------------080107040409050405050901 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
Il 12/09/2013 22:34, Roberto Scattini ha scritto:
hi, today we discovered that this query doesn't return the expected values:

SELECT DISTINCT
 p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS "Fecha Ingreso PG", e.estado AS "Estado", to_char(pe.fecha, 'dd/mm/yyyy') AS "Fecha Estado"
 FROM personal.personas AS p
  LEFT JOIN personal.personas_da_pg AS da ON p.id=da.id_persona
  LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id
  LEFT JOIN personal.estados AS e ON pe.id_estado=e.id
  LEFT JOIN procu_departamento AS d ON d.id=da.id_departamento
  LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama, 4)||'000'=dto.c_organigrama
  LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama, 3)||'0000'=dir.c_organigrama
  LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama, 2)||'00000'=dg.c_organigrama
  LEFT JOIN personal.funciones AS pf ON pf.id=da.funcion_id
  LEFT JOIN personal.profesiones AS pp ON pp.id=p.id_profesion
 WHERE p.apellido ilike '%nuñez%'
 ORDER BY "Apellido"
The problem is the encoding: SQL_ASCII encodes only the first 128 characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with SQL_ASCII encoding, they will be seen as two distinct characters:

ascii_test=# SELECT length(E'ñ');
 length
--------
      2
(1 row)
ascii_test=# SELECT length(E'Ñ');
 length
--------
      2
(1 row)
ascii_test=# SELECT 'ñ'::bytea;
 bytea 
--------
 \xc3b1
(1 row)
ascii_test=# SELECT 'Ñ'::bytea;
 bytea 
--------
 \xc391
(1 row)

Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply case insensitive pattern matching to data which does not encode any string (in the SQL_ASCII encoding) and works as a normal LIKE.

Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ' and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway, and problem remains.

The best way is to work with a UTF8 encoded database. Is there a particular reason to work with SQL_ASCII encoding?

Giuseppe.
-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
--------------080107040409050405050901--