db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eranda Sooriyabandara (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4424) Convert outerjoin.sql into JUnit
Date Thu, 03 Dec 2009 15:23:20 GMT

     [ 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.


Mime
View raw message