11 Replies Latest reply: Apr 26, 2011 7:52 AM by 856631 RSS

    Charset not supported when using convert and a join

    856631
      Take the following tables:
      --------------------------------------------------------
      --  DDL for Table TEST_ASSOCIATION
      --------------------------------------------------------
      
        CREATE TABLE "TEST_ASSOCIATION" 
         (     "PARENT_ID" VARCHAR2(20 BYTE), 
           "CHILD_ID" VARCHAR2(20 BYTE)
         ) ;
      --------------------------------------------------------
      --  DDL for Table TEST_OBJECT
      --------------------------------------------------------
      
        CREATE TABLE "TEST_OBJECT" 
         (     "ID" NUMBER, 
           "NAME" VARCHAR2(20 BYTE)
         ) ;
      Now this query works perfectly:
      select * from test_object t_o
      where convert(t_o.name, 'US7ASCII') like '%a%'
      And this one doesn't (error ORA-01482):
      select * from test_object t_o
      where
      exists (
        select * from test_association t_a
        inner join test_object t_o2
        on t_a.parent_id = t_o2.id
        where t_o2.id = t_o.id
      )
      and convert(t_o.name, 'US7ASCII') like '%a%'
      I suspect the inner query corrupts the CONVERT function, making it fail.

      As anyone encountered something like that? Is it possible to correct this?
        • 1. Re: Charset not supported when using convert and a join
          riedelme
          Welcome to OTN!
          user12853818 wrote:
          I suspect the inner query corrupts the CONVERT function, making it fail.
          I doubt that the EXISTS subquery is affecting the convert function since they're different WHERE clause predicates. It is more likely that the EXISTS subquery isn't finding anything.

          Test the query with only the EXISTS subquery after commenting the predicate with the CONVERT function - I suspect that the subquery is not finding anything. Fix the subquery and your query should work.
          • 2. Re: Charset not supported when using convert and a join
            856631
            Hi, sorry for not saying hello first!

            The following query (which is my real life problem) works perfectly and returns the expected number of rows (5):
            SELECT * 
            FROM
              uf u1_
            INNER JOIN structure_soins s0_
            ON
              u1_.id = s0_.id
            WHERE
              EXISTS
              (SELECT
                1
              FROM
                as_structures a2_
              INNER JOIN structure_soins s3_
              ON
                a2_.parent_id = s3_.id
              WHERE
                a2_.child_id = u1_.id
              AND s3_.id     = 85865
              )
            /*AND ((UPPER(CONVERT(NVL(s0_.libelle_basins, s0_.libelle), 'US7ASCII')) LIKE '%A%')
            OR (s0_.code LIKE '%A%'))*/
            I turned this in my head and in my query launcher, but couldn't figure it out except for an Oracle bug...
            • 3. Re: Charset not supported when using convert and a join
              riedelme
              So, if I understand correctly the EXISTS clause works, the CONVERT() clause works, but they don't work when used together.

              Probably not an Oracle bug although it is possible. Data or logic issues are far more likely. Check the actual data returned by both instead of just the # of rows; its ulikely but possible that each WHERE clause predicate is excluding rows found by the other while returning the same # or rows in both cases.

              Oh, if you're intending to stick around change your handle to a better name so you can be recognized; "userwhatever" is pretty vague :)
              • 4. Re: Charset not supported when using convert and a join
                856631
                Thanks, I already changed my username (first thing I did after joining), but it seems it takes time to be taken into account.

                Anyway, both query parts return data from the same set. The subquery is to pick an object within a hierarchy, and the convert to make a search that is accent-insensitive.

                For example, i did a restrictive search to filter only one element with the CONVERT clause:
                -- This looks all objects children of the object ID: 85865
                
                SELECT *
                FROM
                  uf u1_
                INNER JOIN structure_soins s0_
                ON
                  u1_.id = s0_.id
                WHERE 
                  EXISTS
                  (SELECT
                    1
                  FROM
                    as_structures a2_
                  INNER JOIN structure_soins s3_
                  ON
                    a2_.parent_id = s3_.id
                  WHERE
                    a2_.child_id = u1_.id
                  AND s3_.id     = 85865
                  )
                /* AND UPPER(CONVERT(NVL(s0_.libelle_basins, s0_.libelle), 'US7ASCII')) LIKE '%SMURS%'*/
                
                -- RETURNS 5 objects
                
                88501     (null)     0     0     88501     F     999     CR FICTIF     (null)     (null)     (null)     0     2011-04-13 17:02:00     0     (null)
                89741     (null)     0     0     89741     F     3     TRANSPORTS SANITAIRE     (null)     (null)     (null)     0     2011-04-13 13:48:00     (null)     (null)
                89740     (null)     0     0     89740     F     2     GEST.DDE TRA-SA     (null)     (null)     (null)     0     2011-04-13 13:48:00     (null)     (null)
                89742     (null)     0     0     89742     F     4     SMURS     (null)     (null)     (null)     0     2011-04-13 13:48:00     (null)     (null)
                88500     (null)     0     0     88500     F     1     ADMINISTRATION     (null)     (null)     (null)     0     2011-04-13 13:46:52     (null)     (null)
                
                
                -- This filters ALL objects for one named '%SMURS%'
                
                SELECT *
                FROM
                  uf u1_
                INNER JOIN structure_soins s0_
                ON
                  u1_.id = s0_.id
                WHERE /*
                  EXISTS
                  (SELECT
                    1
                  FROM
                    as_structures a2_
                  INNER JOIN structure_soins s3_
                  ON
                    a2_.parent_id = s3_.id
                  WHERE
                    a2_.child_id = u1_.id
                  AND s3_.id     = 85865
                  )
                AND*/ UPPER(CONVERT(NVL(s0_.libelle_basins, s0_.libelle), 'US7ASCII')) LIKE '%SMURS%'
                
                -- RETURNS the '%SMURS%' object 
                
                89742     (null)     0     0     89742     F     4     SMURS     (null)     (null)     (null)     0     2011-04-13 13:48:00     (null)     (null)
                As you can see, I don't think this is a data issue.
                • 5. Re: Charset not supported when using convert and a join
                  riedelme
                  Looks like 4 of the 5 rows should be filtered out since they don't match the last criteria. The 89742 row seems to qualify for both conditions, so your bug idea is looking more promising though I don't want to go there quite yet ;)

                  What version of Oracle?

                  If you are using SQL*PLUS or SQL*Developer there is a tool called AUTOTRACE that will indicate how WHERE clause predicates are being applied. It might help indicate the WHERE clause usage. There a tiny possibility a query transformation behind the scenes is causing problems.

                  What happens if you divide the query into 2 queries - each with one part of the WHERE clause - connected with INTERSECT? Do you get the row you're expecting then?

                  Is the "OR (s0_.code LIKE '%A%')" clause still being used anywhere?

                  Edited by: riedelme on Apr 20, 2011 2:20 PM
                  • 6. Re: Charset not supported when using convert and a join
                    856631
                    Thanks for taking the time to answer me.

                    I'm using Oracle Database 10g Express Edition Release 10.2.0.1.0
                    I dropped the "OR (s0_.code LIKE '%A%')" clause from my testing, in order to focus on the matter at hand.

                    Your intersect method provided the expected result (row 89742).

                    As for the AUTOTRACE results I've got:
                    http://dl.dropbox.com/u/811022/oracle_autotrace_1.png
                    http://dl.dropbox.com/u/811022/oracle_autotrace_2.png

                    This looks a bit weird, although I must admit, I don't know what to do with it:
                    UPPER(CONVERT(NVL(S0_.LIBELLE_BASINS,S0_.LIBELLE),'(1')) LIKE '%SMURS%'
                    • 7. Re: Charset not supported when using convert and a join
                      riedelme
                      Ah, SQL*Developer. the info I asked you for was there but harder to read. SQL*PLUS AUTOTRACE gives a somewhat easier to spot section something like
                      SQL> set autotrace on
                      SQL> l
                        1* select * from dual where dummy = 'X'
                      SQL> /
                      
                      D                                                                               
                      -                                                                               
                      X                                                                               
                      
                      
                      Execution Plan
                      ----------------------------------------------------------                      
                      Plan hash value: 3543395131                                                     
                                                                                                      
                      --------------------------------------------------------------------------      
                      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
                      --------------------------------------------------------------------------      
                      |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |      
                      |*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |      
                      --------------------------------------------------------------------------      
                                                                                                      
                      Predicate Information (identified by operation id):                             
                      ---------------------------------------------------                             
                                                                                                      
                         1 - filter("DUMMY"='X')                                                      
                      
                      
                      Statistics
                      ----------------------------------------------------------                      
                                0  recursive calls                                                    
                                0  db block gets                                                      
                                3  consistent gets                                                    
                                0  physical reads                                                     
                                0  redo size                                                          
                              225  bytes sent via SQL*Net to client                                   
                              253  bytes received via SQL*Net from client                             
                                2  SQL*Net roundtrips to/from client                                  
                                0  sorts (memory)                                                     
                                0  sorts (disk)                                                       
                                1  rows processed                                                     
                      
                      SQL> spool off
                      The info was still there in the execution plans you posted but a little harder to spot, as "Predicats d'acess" and "Predicats de filtre" in the first file. As you expected the where clause info isn't telling us much but at least the predicates (where clause conditions) are being recognized.

                      Oracle messages look a whole lot more interesting in foreign languages. Of course, some of the messages in English might just as well be in another language ;)

                      Lets see what we have:
                      1. 2 conditions united with AND return no rows
                      2. 2 conditiions used in distinct queries merged with INTERSECT return expected row(s)

                      I can't see any difference - if the 2 clauses find the same row the query should return that row. Logically the AND and the INTERSECT should be similar enough to return the row in both cases (there are some subtle differences that I'm not thinking about right now regarding duplicate rows).

                      I'm out of ideas apart from your original idea of a bug :(. If you have support you can open an SR.

                      As a workaround - SRs can take awhile to get through - test the INTERSECT (or UNION if it falls short) solution some more, if possible adding comments to your code identifying the issue.

                      Edited by: riedelme on Apr 21, 2011 6:17 AM

                      Just looked at your query again. Its unusual in ANSI SQL to use both ON and WHERE clauses to define table joins (the access predicates in your second screenshot). There are (rare) bugs with ANSI sql (usually with using FULL OUTER JOINS). Try recoding into classic SQL and see what happens (I admit this is unlikely to help, but is worth a try). Your query will look something like
                      select whatever
                         from table1, table2
                       where table1.key = table2.key
                           and table1.column = filter_value
                      • 8. Re: Charset not supported when using convert and a join
                        856631
                        I rewrote my query as you suggested:
                        SELECT
                          *
                        FROM
                          uf u1_
                        INNER JOIN structure_soins s0_
                        ON
                          u1_.id = s0_.id
                        WHERE
                          EXISTS
                          (SELECT
                            1
                          FROM
                            as_structures a2_,
                            structure_soins s3_
                          WHERE
                            a2_.parent_id  = s3_.id
                          AND a2_.child_id = u1_.id
                          AND s3_.id       = 85865
                          )
                        AND UPPER(CONVERT(NVL(s0_.libelle_basins, s0_.libelle), 'US7ASCII')) LIKE '%SMURS%'
                        But still got the same error:
                        ORA-01482: jeu de caractères non pris en charge
                        01482. 00000 -  "unsupported character set"
                        *Cause:    The character set used to perform the operation, such as
                                   the CONVERT function, is not a supported character set.
                        *Action:   Use one of the supported character sets.
                        Erreur à la ligne 20, colonne 56
                        Well, having no support, I guess my only option is to go with the INTERSECT method. It's a shame I can't submit a bug report though.
                        • 9. Re: Charset not supported when using convert and a join
                          riedelme
                          Not quite - I thought to eliminate the ANSI INNER join altogether to see what happens, something like (untested)
                          SELECT  *
                            FROM  uf u1_, structure_soins s0_
                           where   u1_.id = s0_.id
                              and  EXISTS  (SELECT 1
                                                      FROM  as_structures a2_,  structure_soins s3_
                                                    WHERE  a2_.parent_id  = s3_.id
                                                         AND a2_.child_id = u1_.id
                                                        AND s3_.id       = 85865
                                                   ) 
                              AND UPPER(CONVERT(NVL(s0_.libelle_basins, s0_.libelle), 'US7ASCII')) LIKE '%SMURS%'
                          Your original idea of a bug is looking more likely :(
                          • 10. Re: Charset not supported when using convert and a join
                            Peter Gjelstrup
                            Hello Alex,

                            Could be a variant of Bug 4686909.

                            First reported in 10.2.0.1 and fixed in 11.0.0.0

                            Bug text:
                            The following SELECT statement fails with ORA-1482:

                            select d1.dummy
                            from dual d1
                            where
                            d1.dummy in (select max(1) from dual d2 where d2.dummy = d1.dummy)
                            and convert(d1.dummy ,'al32utf8') = 'k';

                            - The ORA-1482 error reproduces if MAX or MIN function is used, but not if
                            other functions, for example lower, upper, to_char, to_number are used
                            - The ORA-1482 error does not reproduce if the subquery is not a correlated
                            subquery
                            - The ORA-1482 error reproduces for any destination character set used in
                            the convert function
                            Release Notes:
                            convert function used anywhere except select list was throwing error-1482
                            There is a similar bug, Bug 4746930, which suggests a workaround:
                            WORKAROUND:

                            use CURSOR_SHARING=EXACT
                            Regards
                            Peter
                            • 11. Re: Charset not supported when using convert and a join
                              856631
                              Thanks a lot, I installed Oracle 11g XE today, and the bug is fixed in this release.
                              Looks like my original instinct was right!