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.