4

all. I have a weird situation when executing a native SQL query against an Oracle database. If the query is executed via SQL client software (in my case, DbVisualizer) I have one result set; if my application (Java, Spring-based) executes it, the result is different.

select      
    c.id      
    , c.parentId     
    , c.name      
    , c.sequence      
    , c.isSuppressed      
    , c.isGotoCategory      
    , c.hasChildren      
    , c.startDate      
    , c.endDate  
from (  
    select      
        category.category_id as id      
        , category.parent_category_id as parentId      
        , nvl(context.category_name, category.category_name) as name      
        , nvl(context.sequence_num, category.sequence) as sequence      
        , nvl(context.is_suppressed, 'N') as isSuppressed      
        , decode(category.syndicate_url, null, 'N', 'Y') as isGotoCategory      
        , decode(category.is_leaf, 1, 'N', 'Y') as hasChildren      
        , category.start_date as startDate      
        , category.end_date as endDate  
    from (      
        select          
            category.category_id          
            , category.parent_category_id          
            , category.category_name          
            , category.start_date          
            , category.end_date          
            , category.syndicate_url          
            , category.sequence          
            , connect_by_isleaf as is_leaf          
            , level as category_level      
        from          
            category      
        start with 
            category.category_id =  (     
                select          
                category_id      
            from          
                category      
            where          
                parent_category_id is null      
            start with          
                category_id = 3485          
                connect by prior parent_category_id = category_id  
        )           
            connect by category.parent_category_id = prior category.category_id and level <= (4 + 1)  
    ) category      
        inner join category_destination_channel channel on channel.category_id = category.category_id          
            and channel.publish_flag = 'Y'          
            and channel.destination_channel_id = 1      
        left join contextual_category context on context.category_id = category.category_id          
            and context.context_type = 'DESKTOP'  
    where       
        category.category_level <= 4       
        and category.start_date <= sysdate 
        and category.end_date >= sysdate   
) c  
where      
    c.isSuppressed <> 'Y' 

The query above is the one that problematic one. When executed via SQL client, the outer restriction applies (c.isSuppressed <> 'Y') and the records are filtered out. When the query is executed by the application the outer restriction doesn't seem to be applied at all and my result set has records that should not be there.

Anyone has faced this kind of problem before?

My application is built with: Java 7, Spring 4.x, Oracle 11 (with OJDBC driver version 11.2.0.3). Application server is JBOSS EAP 6.3 by my tests are made with Jetty (maven-jetty-plugin 6.1.26).

I already considered some possible causes of the problem - application accessing wrong database, unusual issue while using @SqlResultSetMapping - but ruled them out with some tests. Don't know what to consider anymore.

Any help is appreciated. Thanks in advance.

Prerak Tiwari
  • 3,436
  • 4
  • 34
  • 64
Matheus Moreira
  • 2,338
  • 4
  • 24
  • 31
  • Do any of your clients have different National Language Support (NLS) settings? For example, if you have dates stored as strings the NLS_DATE_FORMAT could make implicit date comparisons work differently. NLS_COMP and NLS_SORT could also matter if there are internationalization issues. – Jon Heller Aug 27 '15 at 18:39
  • 1
    I'd be suspicious of parsing for your application and possible escape chars or something truncating the query text. Have you examined the query text generated by the application to see if it's even trying to execute the complete query? – Calvinthesneak Aug 27 '15 at 18:39
  • @JonHeller there is no data comparison in the query but I'll check the setting. – Matheus Moreira Aug 28 '15 at 18:53
  • @Calvinthesneak I debugged Hibernate code and it seems that it is sending the correct query to the database. The only substitution made is named parameter to question marks. – Matheus Moreira Aug 28 '15 at 18:54

0 Answers0