This discussion is archived
11 Replies Latest reply: Apr 26, 2011 5:52 AM by 856631 RSS

Charset not supported when using convert and a join

856631 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot, I installed Oracle 11g XE today, and the bug is fixed in this release.
    Looks like my original instinct was right!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points