Hi Bryan,

I faced a problem with fix the error in convented code of,

ij> select max(name), max(resdate) from inventory join timeslots on
inventory.capacity is not null

left outer join reservations on inventory.itemno = reservations.itemno and
reservations.slot = timeslots.slot;

1

---------------------------------------------------------------------------------------------------------------

Yang

WARNING 01003: Null values were eliminated from the argument of a column
function.

Because the converted code by the DERBY-2151 tool for the "warning
message(WARNING
01003)" was,

if (usingEmbedded())

which give me a error in "assertNotNull("Expected warning but found none",
sqlWarn);" saying that no warning was found.

I thought thats because of the statement returns result set and the warning
message must associates with the result set. I change the statement(st) with
the resultset(rs). But it give me the error message "Operation getWarning
not permitted. Verify that autocommit is off".

Can you help me to figure it out.

Here I am attaching the patch file with this.

Thanks

Hi Bryan,
Here is the patch file.

Hi Eranda, thanks for working on this test.

The handling of the warning message is a little bit delicate, and I had to fiddle with it a bit.
Basically, the problem is that the warning is not generated in embedded mode until after
you call rs.next(), but if you wait until you have processed the entire result set, then the
warning is cleared when the result set is closed.

So I think you can only get this warning after calling rs.next(), but before calling rs.close(),
and since the call to JDBC.assertFullResultSet() was doing both the rs.next() and the rs.close()
for you, you weren't able to see the warning in your test code.

So I re-wrote this section of the test a little bit and was able to get the
warning to appear in a way that the test could check it.

Here's the section of the test after I fiddled with the test code a bit. Please try putting
this code into the test and see if it now works for you.

rs = st.executeQuery(
" select max(name), max(resdate) from inventory join "
+ "timeslots on inventory.capacity is not null "
+ "left outer join reservations on inventory.itemno = "
+ "reservations.itemno and reservations.slot = timeslots.slot");

rs.next(); // This causes the warning to be generated now.
if (usingEmbedded())

assertEquals("Yang", rs.getString(1));
assertEquals("2000-07-13", rs.getString(2));

I also deleted the declaration of the "sqlWarn" variable from the top of
the test method and moved it to this area because this was the only
place that variable was used and so it seemed a little easier to read that way.

Does anyone know what this warning is supposed to tell us?

One small concern I have with the patch, is that I'm not sure it preserves the intention of the runtime statistics tests. The new test only checks that the plans contained a table scan and did not contain a distinct scan. The comments in the original test seem to indicate that the intention was to test certain properties of the predicates. Unfortunately, it's difficult to know exactly which properties of the predicates to check, since the comments are rather vague. One of the comments is more specific, though, and says "make sure predicates are null tolerant", but the new test only checks the results from the query. I think "null tolerant" means we should check that the predicates have the property "Ordered nulls: false".

Hi Bryan, Knut,

The warning was WARNING 01003: Null values were eliminated from the argument
of a column function.

I think the "make sure predicates are null tolerant" means that prove that
predicate can be null.
But also don't see how the runtime statistics are make sure that. Here is
the part which relevant to this.

ij> – make sure predicates are null tolerant

select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2

where char(tt2.c2) is null;

C1 |C2 |C3 |C1 |C2 |C3

-----------------------------------------------------------------------

1 |2 |3 |NULL |NULL |NULL

ij> – where java.lang.Integer::toString(tt2.c2) = '2';

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

1

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Statement Name:

null

Statement Text:

– make sure predicates are null tolerant

select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2

where char(tt2.c2) is null

Parse Time: 0

Bind Time: 0

Optimize Time: 0

Generate Time: 0

Compile Time: 0

Execute Time: 0

Begin Compilation Timestamp : null

End Compilation Timestamp : null

Begin Execution Timestamp : null

End Execution Timestamp : null

Statement Execution Plan Text:

Project-Restrict ResultSet (4):

Number of opens = 1

Rows seen = 3

Rows filtered = 2

restriction = true

projection = false

constructor time (milliseconds) = 0

open time (milliseconds) = 0

next time (milliseconds) = 0

close time (milliseconds) = 0

restriction time (milliseconds) = 0

projection time (milliseconds) = 0

Source result set:

Hash Left Outer Join ResultSet:

Number of opens = 1

Rows seen from the left = 3

Rows seen from the right = 2

Empty right rows returned = 0

Rows filtered = 0

Rows returned = 3

constructor time (milliseconds) = 0

open time (milliseconds) = 0

next time (milliseconds) = 0

close time (milliseconds) = 0

Left result set:

Table Scan ResultSet for TT1 at read committed isolation level
using instantaneous share row locking chosen by the optimizer

Number of opens = 1

Rows seen = 3

Rows filtered = 0

Fetch Size = 16

constructor time (milliseconds) = 0

open time (milliseconds) = 0

next time (milliseconds) = 0

close time (milliseconds) = 0

next time in milliseconds/row = 0

scan information:

Bit set of columns fetched=All

Number of columns fetched=3

Number of pages visited=1

Number of rows qualified=3

Number of rows visited=8

Scan type=heap

start position:

null

stop position:

null

qualifiers:

None

Right result set:

Hash Scan ResultSet for TT2 at read committed isolation level
using instantaneous share row locking:

Number of opens = 3

Hash table size = 3

Hash key is column number 1

Rows seen = 2

Rows filtered = 0

constructor time (milliseconds) = 0

open time (milliseconds) = 0

next time (milliseconds) = 0

close time (milliseconds) = 0

next time in milliseconds/row = 0

scan information:

Bit set of columns fetched=All

Number of columns fetched=3

Number of pages visited=1

Number of rows qualified=3

Number of rows visited=8

Scan type=heap

start position:

null

stop position:

null

scan qualifiers:

None

next qualifiers:

Column[0][0] Id: 1

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Also I have a problem with,

assertStatementError("42972", st,
"select * from t1 a left outer join t2 b "
+ "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where "
+ "a.c1 = t1.c1 and a.c1 = 1)");

because this sql statement doesn't give the error 42972.

Here I am attaching the diff file with this.

Hi Eranda, thanks for the updated patch! I will try to have a look at it this weekend.

Hi Bryan,

I found the reason for the error I previously mentioned. This is because of
the

The reason for the error was more than one object table includes same name
columns.

After solve the issue https://issues.apache.org/jira/browse/DERBY-4380

the scope of the ON clauses makes it clear which table they belong to in
each case, so they should not fail.
The patch file was,

http://issues.apache.org/jira/secure/attachment/12425239/on_subquery_v2.diff

Hi Eranda, thanks for attaching the updated patch with the license granted.

The patch applies and builds cleanly in my environment, but when I run
the test, I get an error.

Can you see if you can figure out what might be making this test case fail?

thanks,

bryan

...........E...............
Time: 30.233
There was 1 error:
1) testBug2897(org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest)java.lang.NullPointerException
at org.apache.derbyTesting.junit.RuntimeStatisticsParser.<init>(RuntimeStatisticsParser.java:49)
at org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest.testBug2897(OuterJoinTest.java:2293)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

FAILURES!!!
Tests run: 26, Failures: 0, Errors: 1

Copying a comment that was raised on the derby-dev list. I think
the comment makes a good point, though I don't have a good
idea about what other naming convention to use.

I think that as long as we are consistent about using DERBY-NNNN
in test comments to refer to JIRA-hosted Deby issues, then it will
be fairly clear that other bug number references refer to pre-JIRA
bug tracking systems.

bryan
=====================================================
> > 1) testBug2897(org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest)java.lang.NullPointerException

Btw, the number here, 2897, refers to pre-JIRA days. Should we make up
some systematic way of referring to such old error number so as not
to confuse them with JIRA numbering? I imagine we don't want to remove
the old number information since I have seen the IBM folks sometimes make
use of the old numbers.. Maybe it's enough that we always refer to
JIRA errors as DERBY-nnnn in comments and some adapted string for
identifiers, e.g. [dD]erbyNNNN.

Dag

Hi Bryan,
I am sorry for bit late to submit the patch. Here I did some changes.
Break into some more tests which I thought it would be clear if they are separated.
I use the name the old jira issues according to the Dag.
Now the test build and runs successful in my machine. I hope you get the same results and I think now you can commit it to the trunk.
Eranda

Thanks Eranda for the updated patch! I will try to have a look soon.

Hi Eranda,

In general, we don't put "@author" tags into Apache code, as a matter of Apache policy.

Is it OK with you if I remove the "@author" tag from OuterJoinTest prior to commit?

Hi Bryan,
Yes its perfectly ok.
That tag I used when I demonstrate the code. I forgot to remove it.
Thanks

Thanks Eranda for all the work on this patch!

The latest test looks fine, and it runs successfully in my environment.

Committed to the subversion trunk as revision 919250.

Hi Bryan,
Thanks for committing to the trunk.
I am closing this issue.