[
https://issues.apache.org/jira/browse/DERBY-4424?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eranda Sooriyabandara updated DERBY-4424:
-----------------------------------------
Attachment: OuterJoinTest.diff
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.
> Convert outerjoin.sql into JUnit
> --------------------------------
>
> Key: DERBY-4424
> URL: https://issues.apache.org/jira/browse/DERBY-4424
> Project: Derby
> Issue Type: Improvement
> Reporter: Eranda Sooriyabandara
> Assignee: Eranda Sooriyabandara
> Priority: Minor
> Fix For: 10.6.0.0
>
> Attachments: OuterJoinTest.diff, OuterJoinTest.diff
>
>
> Conversion of the outerjoin test into JUnit
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
