SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Some columns nulled when using rownum with data from an inner select

772653May 12 2010 — edited Sep 23 2010

Hello,

In one of the complex queries generated by my application, some of the columns return null values when there are some actual values. While the complex query is generated by Hibernate, the problem can be reproduced by using SQL directly. It seems that this is partly due to rownum.

Here is an example query that fails:

select row_.*, rownum from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) order by this_.COL_B asc ) row_

h2. Sample data

This can be applied to the following example table/data:

CREATE TABLE "MY_TABLE"
( "ID" VARCHAR2(20) PRIMARY KEY NOT NULL,
"COL_A" VARCHAR2(20),
"COL_B" VARCHAR2(20),
"COL_C" VARCHAR2(20)
) ;

Insert into MY_TABLE (ID,COL_A,COL_B,COL_C) values ('ID1','QWE','WER','ERT');
Insert into MY_TABLE (ID,COL_A,COL_B,COL_C) values ('ID2','ASD','SDF','DFG');
Insert into MY_TABLE (ID,COL_A,COL_B,COL_C) values ('ID3','ZXC','XCV','CVB');

h2. Query without rownum

select row_.* from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) order by this_.COL_B asc ) row_

This query produces the following results:

ID, COL_A, COL_B, COL_C, ROWNUM
ID2, ASD, SDF, DFG
ID1, QWE, WER, ERT
ID3, ZXC, XCV, CVB

h2. Query with rownum

select row_.*, rownum from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) order by this_.COL_B asc ) row_

This query produces the following results:

ID, COL_A, COL_B, COL_C, ROWNUM
ID2, (null), SDF, (null), 1
ID1, (null), WER, (null), 2
ID3, (null), XCV, (null), 3

h2. Other variants

Removing the inner "where this_.ID in (select this_.ID as y0_ from MY_TABLE this_)", which is completely unnecessary indeed seems to produce the right results.

However, I'd like to know if there's something wrong (unnecessary complexity excluded) with this query:

select row_.*, rownum from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) order by this_.COL_B asc ) row_

Does anyone know why COL_A and COL_C are not populated (and why is it just the column with the ORDER BY)?

I do realise the Hibernate framework (which is used by my application) does things in a convoluted way here, but unless there's something wrong which I can't spot here, this seems to be a legitimate query and should still produce the right results.

(I'm using Oracle 11g R2)

Best wishes,

Bruno.

Frank Kulash

Hi, Bruno,

Welcome to the forum!

user4204334 wrote:
Hello,

In one of the complex queries generated by my application, some of the columns return null values when there are some actual values. While the complex query is generated by Hibernate, the problem can be reproduced by using SQL directly.

I can't reproduce it in Oracle 11.1.0.6.0. (Sorry, I don't have 11.2).
Col_a and col_c are never NULL in your table. None of your queries do anything that should make them appear as NULL, and none of your queries, when I run them in Oracle 11.1, do.

Can you work around this by using the analytic ROW_NUMBER function instead of ROWNUM?

SELECT	  id, col_a, col_b, col_c
,	  ROW_NUMBER () OVER (ORDER BY  col_b)	AS r_num
FROM	  my_table
ORDER BY  col_b
;

 

By the way, whenever you post code on this site, please format it so that it's easy for people to see the main clause, sub-queries, and other large structures.
Type these 6 characters:

\

(small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.

1 person found this helpful

764043

ROWID is what is referred to a pseudo-column. It is not data in the database or table so much as it is a mapping of the location, in a specific datafile of

the physical location of a row of data. So, to load data from other tables, This row id is not necessary. If you want, you can avoid to produce the right

result actual data from table. To fetch rownum, you can use either rownum as a column directly or rownum() function.

Null: I never see any null data in your provided Insert stmts. So it won’t show NULL. If you have any specified null in actual data. Then only it will show null

SELECT col_a, col_b, col_c, ROW_NUMBER () OVER (ORDER BY col_b) AS r_num

FROM (SELECT col_a, col_b, col_c

FROM my_table

ORDER BY col_b);

Or

SELECT col_a, col_b, col_c, ROW_NUMBER () OVER (ORDER BY col_c) AS r_num

FROM my_table

ORDER BY col_b;

772653

Hi Frank,

Thanks for trying on 11.1, that was a good hint. Our DBA also tried on another installation 11.1 and couldn't reproduce the problem there either. We've just tried on another installation of 11.2.0.1.0 and the problem is reproducible there. I think we're going to try to apply more recent patches and then report it via our Oracle support channel.

Thanks for the suggestion regarding the alternative query too. It does work. Unfortunately, I don't really have control (at least in that mode) over the way the query is produced, since I'm using a framework (Hibernate) to do so. I totally agree this isn't the best way of writing that query, but I think it's a problem if frameworks can't expect a certain level of correctness in the queries they produce (however complex those queries are), so I think that's a bug as far as I can tell.

(Sorry about the code formatting style too, I couldn't find how to do this from the help on the side... I guess I should have checked the FAQ).

Best wishes,

Bruno.

772653

Leaving aside the question of the rowid (since generated by a framework), just to clarify, the "(null)" was just there to mean empty or actual null, not the "(null)", and that's indeed the problem: some of the results are null when they shouldn't be.

Just to clarify with the appropriate formatting, starting from scratch with these statements:

CREATE TABLE "MY_TABLE"
( "ID" VARCHAR2(20) PRIMARY KEY NOT NULL,
"COL_A" VARCHAR2(20),
"COL_B" VARCHAR2(20),
"COL_C" VARCHAR2(20)
);

Insert into MY_TABLE (ID,COL_A,COL_B,COL_C) values ('ID1','QWE','WER','ERT');
Insert into MY_TABLE (ID,COL_A,COL_B,COL_C) values ('ID2','ASD','SDF','DFG');
Insert into MY_TABLE (ID,COL_A,COL_B,COL_C) values ('ID3','ZXC','XCV','CVB');

select row_.*, rownum from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from 
     MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) order by this_.COL_B asc ) row_;

I get this output in 11.2 (but the correct one in 11.1):

CREATE TABLE succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
ID                   COL_A                COL_B                COL_C                ROWNUM                 
-------------------- -------------------- -------------------- -------------------- ---------------------- 
ID2                                       SDF                                       1                      
ID1                                       WER                                       2                      
ID3                                       XCV                                       3                      

3 rows selected

In contrast, this query (no rownum):

select row_.* from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from 
     MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) order by this_.COL_B asc ) row_;

produces the expected output on 11.2 (and 11.1 presumably):

ID                   COL_A                COL_B                COL_C                
-------------------- -------------------- -------------------- -------------------- 
ID2                  ASD                  SDF                  DFG                  
ID1                  QWE                  WER                  ERT                  
ID3                  ZXC                  XCV                  CVB                  

3 rows selected

Best wishes,

Bruno.

532658

Maybe it's not the rownum thats the problem. If You move the order by to the end and change it to work on the resultset like this:

select row_.*, rownum from (select this_.ID, this_.COL_A, this_.COL_B, this_.COL_C from
MY_TABLE this_ where this_.ID in (select this_.ID as y0_ from MY_TABLE this_) ) row_
order by row_.COL_B asc;

Then it works.
/Goran

user7664325

Hello
I set parameter
optimizer_features_enable=10.2.0.4
and results of the query is ok.

I Think patch set Patchset 10098816 11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER is the best solution.

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 21 2010

Added on May 12 2010

#11g, #sql

6 comments

2,425 views

SQL & PL/SQL

Some columns nulled when using rownum with data from an inner select

Comments

Post Details