Thread: BUG #5068: LIKE

The following bug has been logged online:

Bug reference:      5068
Logged by:          Paulo
Email address:      correio.vip@gmail.com
PostgreSQL version: 8.4
Operating system:   win2003
Description:        LIKE
Details:

like to select a field containing Numeric 4 and compared with a char(30)
field.
In oracle and postgresql to version 8.2 worked perfectly, now the new
version no longer works, as we have many queries in this format.


attached to select what worked and from 8.4 no longer works, which featured
not work and select track


...

   ("A"."ACL_CdCurLet"  LIKE  btrim(:DB-LIKE))
                    OR
   (                       (UPPER(translate(btrim("B"."ALE_Ano"),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                        LIKE
                        (UPPER(translate(btrim(:DB-LIKE),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV'))) )





           EXEC SQL
            DECLARE CSR-CURLET CURSOR FOR SELECT

                   "A"."EMP_CdEmpresa"
                  ,"A"."ACL_CdCurLet"
                  ,"A"."ALE_CdLetivo"
                  ,"A"."ACU_CdCurso"
                  ,"A"."ACL_NrAulasDia"
                  ,"A"."ACL_DuracaoAula"
                  ,"A"."ACL_Nome"

                  ,"B"."EMP_CdEmpresa"
                  ,"B"."ALE_CdLetivo"
                  ,"B"."ALE_Ano"
                  ,"B"."ALE_Periodo"
                  ,"B"."ALE_NmLetivo"
                  ,"B"."ALE_DataInicio"
                  ,"B"."ALE_DataFinal"
                  ,"C"."ACU_CdCurso"
                  ,"C"."ACU_NmCurso"
                  ,"C"."ACU_AbrCurso"
                  ,"C"."AEN_CdEnsino"
                  ,"D"."AEN_CdEnsino"
                  ,"D"."AEN_NmEnsino"
                  ,"D"."AEN_AbrEnsino"
              FROM "aMatCurr" "A"
                  ,"aLETIVO" "B"
                  ,"aCURSO" "C"
                  ,"aENSINO" "D"
             WHERE ( "A"."EMP_CdEmpresa" = "B"."EMP_CdEmpresa" )
              AND  ( "A"."ALE_CdLetivo" = "B"."ALE_CdLetivo" )
              AND  ( "A"."ACU_CdCurso" = "C"."ACU_CdCurso" )
              AND  ( "C"."AEN_CdEnsino" = "D"."AEN_CdEnsino" )

              AND  ( "B"."EMP_CdEmpresa" = :aLETIVO-EMP-CdEmpresa )

              AND  (

                    ("A"."ACL_CdCurLet"  LIKE  btrim(:DB-LIKE))

                    OR
                    (
                        (UPPER(translate(btrim("B"."ALE_Ano"),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                        LIKE
                        (UPPER(translate(btrim(:DB-LIKE),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                    )

                    OR
                    (
                        (UPPER(translate(btrim("C"."ACU_NmCurso"),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                        LIKE
                        (UPPER(translate(btrim(:DB-LIKE),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                    )

                    OR
                    (
                        (UPPER(translate(btrim("C"."ACU_AbrCurso"),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                        LIKE
                        (UPPER(translate(btrim(:DB-LIKE),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                    )

                    OR
                    (
                        (UPPER(translate(btrim("D"."AEN_NmEnsino"),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                        LIKE
                        (UPPER(translate(btrim(:DB-LIKE),

'ÁÀÂÃÄÉÈÊËÍÌÎÏÓÒÕÔÖÚÙÛÜÇKZYW',
                         'AAAAAEEEEIIIIOOOOOUUUUCCSIV')))
                    )

                   )

             ORDER BY  "B"."ALE_Ano"
                      ,"C"."ACU_NmCurso"
                      ,"A"."ACL_Nome"

               LIMIT :w-Limit

            END-EXEC

On Sun, 2009-09-20 at 01:00 +0000, Paulo wrote:
> like to select a field containing Numeric 4 and compared with a
> char(30)
> field.
> In oracle and postgresql to version 8.2 worked perfectly, now the new
> version no longer works, as we have many queries in this format.

Hard to say without having the complete schema and query, but I suspect
that you are running afoul of the restricted implicit casts.  You might
need to insert explicit casts.