Oracle query seems to return every row twice in ResultSet

[david.karr]

I have a simple Oracle (10g) query with a simple where clause, and no
joins. When I run that query in SQuirreL, it gives me the 8 records
that I expect. When I run that query in a Java application running in
WebLogic 10.1, the "while" loop calling "rs.next()" returns all 8
records twice, making it seem like there were 16 records.

I use "setFetchSize(20)" on the statement. I used to use "AND ROWNUM
<= 20". Both produce the same result. I originally didn't have
"SELECT DISTINCT ...", but I tried adding that. Still no difference.

Is there something obvious that could be causing this?

The query is very simple. It just looks like this, with most of the
column names removed, and the table name changed:

SELECT ID, ... FROM TABLE_NAME tn WHERE tn.CREATION_DATE = ?

The somewhat elided code for the loop looks like this:

--------------------------
try {
rs = getRecords(connection);

// Indicates whether at least one row was found.
boolean foundOne = false;

while (rs.next()) {
foundOne = true;
final Record record = makeRecord(rs); // makeRecord() just has a bunch
of "getString()" calls on the rs.
processRecord(record, counters);
++ recordsProcessed;
}

if (!foundOne) {
break;
}

if (overTimeBudget(startTime)) {
break;
}

logInfo("Processed " + recordsProcessed + " record" +
(recordsProcessed == 1 ? "" : "s") + " so far.");
}

 

[Roedy Green]

What could be going wrong here?

see http://mindprod.com/jgloss/sscce.html

We need to see the details.

see http://mindprod.com/jgloss/iterator.html
to make sure you know precisely how they work.
--
Roedy Green Canadian Mind Products
http://mindprod.com

"You can have quality software, or you can have pointer arithmetic; but you cannot have both at the same time."
~ Bertrand Meyer (born: 1950 age: 59) 1989, creator of design by contract and the Eiffel language.

 

[David Karr]

seehttp://mindprod.com/jgloss/sscce.html

We need to see the details.

I'm not sure what else I can provide. A fully working (or failing, as
the case might be) example would be pretty unlikely.

The only other directly relevant code is the "makeRecord()" method,
which is approximately this:

-------------
private Record makeRecord(final ResultSet rs)
throws SQLException {

Record record = new Record();

record.id = trimOrNull(rs.getString(1));
.
.
.
record.creationDate = rs.getDate(21);
record.maskAddress = trimOrNull(rs.getString(22));
record.badAddress = rs.getInt(24);
record.unitType = trimOrNull(rs.getString(25));
.
.
.
record.badRecord = rs.getInt(31);

logInfo("Returning record [" + record.id + "]");

return (record);
}

private String trimOrNull(String str) {
return (str != null ? str.trim() : null);
}
---------------

 

[Wojtek]

David Karr wrote :

I'm not sure what else I can provide. A fully working (or failing, as
the case might be) example would be pretty unlikely.

The only other directly relevant code is the "makeRecord()" method,
which is approximately this:

-------------
private Record makeRecord(final ResultSet rs)
throws SQLException {

Record record = new Record();

record.id = trimOrNull(rs.getString(1));

You should use getters and setters, so the above would be:
record.setID( trimOrNull(rs.getString(1)) );

logInfo("Returning record [" + record.id + "]");

and:
logInfo("Returning record [" + record.getID() + "]");

return (record);
}

private String trimOrNull(String str) {
return (str != null ? str.trim() : null);
}

This is an expensive way of doing it. Trim the value before you store
it, then just retrieve it.

You did not say HOW the records were duplicated:

1
1
2
2
3
3
4
4

or

1
2
3
4
1
2
3
4

 

[David Karr]

David Karr wrote :







You should use getters and setters, so the above would be:
     record.setID( trimOrNull(rs.getString(1)) );

Does this have any relevance to the problem?

This is an expensive way of doing it. Trim the value before you store
it, then just retrieve it.

Actually, I realized it has to be a little more complicated than
that. I have to set it to null if the string is empty or nothing but
blanks. My current method implements that, but this is also
irrelevant to the problem.

You did not say HOW the records were duplicated:

1
1
2
2
3
3
4
4

or

1
2
3
4
1
2
3
4

The latter.

The other interesting detail is that I don't see this happen all the
time, even when it's retrieving the exact same set of records. My
current test case has 7 particular records. Sometimes it correctly
returns only the 7 records, sometimes it starts over again after the
7th, retrieving 1-7 again.

 

[Daniel Pitts]

Does this have any relevance to the problem?


Actually, I realized it has to be a little more complicated than
that. I have to set it to null if the string is empty or nothing but
blanks. My current method implements that, but this is also
irrelevant to the problem.

Look into commons-lang, they have a nifty class with a nice static method:
org.apache.commons.lang.StringUtils.trimToNull(String )
<http://commons.apache.org/lang/apid...StringUtils.html#trimToNull(java.lang.String)>
Yes, you *can* write the code yourself, but why bother? Not to mention
there are a lot of other nifty classes and methods in that library

The latter.

The other interesting detail is that I don't see this happen all the
time, even when it's retrieving the exact same set of records. My
current test case has 7 particular records. Sometimes it correctly
returns only the 7 records, sometimes it starts over again after the
7th, retrieving 1-7 again.

Sounds like the problem is on line 42 of the file you didn't show us.
In other words, we can't help you unless you can provide an SSCCE. Over
half of the benefit from constructing an SSCCE is in the process of
constructing it. You may find that you have done something silly, or
that its not actually happining the way you suspect. At the very
least, I would add some kind of logging to your while loop, to test
whether you're actually going through the result-set twice, or if the
result-set is twice as long as it should be.

 

[Lew]

Look into commons-lang, they have a nifty class with a nice static method:
org.apache.commons.lang.StringUtils.trimToNull(String )
<http://commons.apache.org/lang/apid...StringUtils.html#trimToNull(java.lang.String)>

Yes, you *can* write the code yourself, but why bother? Not to mention

Because it's only a one-liner and it avoids pulling in a whole library that
has at least one major bug that it has refused to fix.

there are a lot of other nifty classes and methods in that library

Including their version of type-safe enumeration that has a nasty bug in it
for Java 5 and later.

 

[Wojtek]

David Karr wrote :

Does this have any relevance to the problem?

Nope. But it illustratess good OOP practice.

The latter.

The other interesting detail is that I don't see this happen all the
time, even when it's retrieving the exact same set of records. My
current test case has 7 particular records. Sometimes it correctly
returns only the 7 records, sometimes it starts over again after the
7th, retrieving 1-7 again.

One of the things about code is that it does not change over time by
itself, ie it does not rust. It is impossible for the same code with
the same data to return different results.

Have you tried setting a conditional breakpoint? One that only breaks
when the counter reaches a value which is one over the expected number
of records? Then you can view the variable conditions and see what just
happened.

 

[David Karr]

I have a simple Oracle (10g) query with a simple where clause, and no
joins. When I run that query in SQuirreL, it gives me the 8 records
that I expect. When I run that query in a Java application running in
WebLogic 10.1, the "while" loop calling "rs.next()" returns all 8
records twice, making it seem like there were 16 records.

Never mind. Dumb mistake. It wasn't an Oracle problem. The problem
was truly present in code that wasn't shown here, as the code doing
the query was executed in a loop, because of an earlier design
decision.

 

[Daniel Pitts]

Because it's only a one-liner and it avoids pulling in a whole library
that has at least one major bug that it has refused to fix.


Including their version of type-safe enumeration that has a nasty bug in
it for Java 5 and later.

On noes, there is a Bug in one infrequently used piece of my operating
system. Time to shut down the computer and throw it out the window.

WHAT!?

There are still many useful methods in there, and it isn't worth
throwing the whole thing out because of one bug. Otherwise *nothing*
would be worth using (save maybe TeX )

 

[Lew]

On noes, there is a Bug in one infrequently used piece of my operating
system. Time to shut down the computer and throw it out the window.

WHAT!?

There are still many useful methods in there, and it isn't worth
throwing the whole thing out because of one bug.  Otherwise *nothing*
would be worth using (save maybe TeX )

You mischaracterize my complaint. I don't mistrust commons-lang
because it has one bug, I mistrust it because the bug was caused by an
intentional violation of the JLS that they refuse to correct. It's
the refusal to correct that I find squirrely, especially since they
give no reason for the refusal and the bug was caused by their
intentional violation of the spec.

The bug in question is that their type-safe enumeration expects that a
reference to a class literal ('Foo.class') to initialize that class, a
behavior specifically forbidden by the JLS. As a result, since Java 5
certain calls to retrieve an Apache enumeration literal return 'null'
because the class is not initialized and the literals don't exist
yet. I've seen this cause trouble in a major production system. The
bug report in the Apache project shows this bug as closed with no
intention to fix it and no explanation why not.

That attitude is what turns me off, not the presence of the bug per
se.

 

[Lew]

You mischaracterize my complaint.  I don't mistrust commons-lang
because it has one bug, I mistrust it because the bug was caused by an
intentional violation of the JLS that they refuse to correct.  It's
the refusal to correct that I find squirrely, especially since they
give no reason for the refusal and the bug was caused by their
intentional violation of the spec.

<http://issues.apache.org/jira/browse/LANG-76>

 

[Roedy Green]

Never mind. Dumb mistake. It wasn't an Oracle problem. The problem
was truly present in code that wasn't shown here, as the code doing
the query was executed in a loop, because of an earlier design
decision.

Sometimes just tracing the code will bring this up. You see something
unexpected. Trace usually shows the problem before it shows up later
in the external behaviour of the external program.

You don't have to be 100% on top of everything happening, just keep an
eye out for anything unexpected.
--
Roedy Green Canadian Mind Products
http://mindprod.com

"If you think it’s expensive to hire a professional to do the job, wait until you hire an amateur."
~ Red Adair (born: 1915-06-18 died: 2004-08-07 at age: 89)

 

[Lew]

Well, there _are_ explanations. If I get it correctly, the bug is
related to using their EnumUtils class, using as parameter a Class
instance for a class which has not been initialized yet. They feel
that it is the responsability of the caller to ensure that the
provided Class instance has been initialized.

I assess that it is the responsibility of a library writer not to rely on a
bug to make their library work. Blaming the user for failing to work around a
bug in the library is just evil.

There is application code which relies on a JVM bug (namely a violation
of the JLS by JDK 1.4 and previous) for proper operation with EnumUtils,
but it is hardly the fault of EnumUtils or the source code in
common-langs. They _could_ embed a workaround (forcing initialization

Of course it is their fault. They intentionally relied on behavior that
violated the Java spec to make their library work. What did they *think*
would happen when the bug was fixed, hm?

from within EnumUtils) just to tolerate nominally invalid code, but they
do not, mostly because the workaround is expensive (it involves
reflection, and must be performed at each call) and the EnumUtils method
are supposed to be highly efficient.

So that excuses relying on a bug to make the library work?

I am incredulous that you would support such a ridiculous practice.

One could point out that the lack of an efficient class initialization
method (that is, a method which is inexpensive when the class turns
out to have already been initialized) is a deficiency of Java and
should be corrected. Actually, one did point out:
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4993813
It is a RFE since early 2004. The irksome part is that the JVM must have
such a method somewhere (I know mine has), it is only a matter of making
it public.

That has nothing to do with commons-lang intentionally using a bug to support
its Enum functionality.

Besides, since JDK 1.4 and previous have the abovementioned violation
(simply using X.class causes initialization of class X), and JDK 5 and
newer have a built-in, better integrated enum facilities, then the point
becomes mooter and mooter.

So does that mean it's all right to code to a bug just because it happens to
be there? What about non-Sun implementations of Java? Can we count on all
implementations not to follow the specification?

And just because Java 5 has 'enum' doesn't mean that a good library of
type-safe enumerations wouldn't be useful. For example, there's a lot of code
that has to serialize and deserialize to pre-Java 5 applications still, and
that code can't really use Java enums yet. Furthermore, some of the
complaints people have about Java enums are best handled by using another,
non-built-in enumeration type such as the commons-lang Enum, were it not for
the bug.

I am shocked and amazed that anyone would think that intentionally using a bug
to make one's code work is a good practice or in any way defensible.

 

[Lew]

You are the one who wants to lay blame and distribute good or bad
points. That is only programming, but you want to make it a moral issue.
At least try to do it properly, and lay blame on who deserves it (and
not who just committed the capital sin of displeasing you).

I'm just expressing an opinion and labeling it as my own personal reaction.
You're the one labeling it "capital sin".

 

[Arne Vajhøj]

Their library works without relying on any bug. Some external code relies
on the bug, and just happens to get caught when using their library.
When you feed a null reference to a standard JDK class which reacts
by throwing a NullPointerException, is this a bug of the standard JDK
class, or of the code which calls it incorrectly ?

Same here. EnumUtils expects an initialized class. Some application code
feeds it an uninitialized class. And things break.

What does the docs say?

"class" or "class that must be initialized" ?

First => lib issue

Second => app issue

Arne

 

[Lew]

What does the docs say?

"class" or "class that must be initialized" ?

First => lib issue

Second => app issue

enumClass - the class of the Enum to get

Nowhere do the docs suggest that the class must be initialized first.

 

[Arne Vajhøj]

<http://commons.apache.org/lang/api-release/org/apache/commons/lang/enums/EnumUtils.html>

Every method takes a 'Class' argument about which the method docs all say:

Nowhere do the docs suggest that the class must be initialized first.

Then I will consider it a bug in the lib.

They can pick whether they consider it a bug in the docs or a bug in
the code.

But if an argument is specified to be Class with no special
notes then people expect that Foobar.class is valid no matter
whether a Foobar has been instantiated or not.

Arne