Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 141.146.126.69 as permitted sender)
Message-ID: <5149ABC4.3040901@oracle.com>
Date: Wed, 20 Mar 2013 05:29:56 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: RestrictedVTI.initScan() does not pass certain Table Functions'
 predicate expressions
References: <1363737418606-128229.post@n7.nabble.com>
In-Reply-To: <1363737418606-128229.post@n7.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi David,

I think it's worth filing a JIRA for this issue. If the defect is shared 
by VTIs and table functions then there's a possibility that ordinary 
table scans suffer from it too. That would raise the problem's urgency.

Thanks,
-Rick

On 3/19/13 4:56 PM, drv wrote:
> This post is related to
> http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-td127988.html#a127995
>
> Hi Rick/all -
>
> In re-factoring a Restriction into a Qualifier[][], I noticed that Derby
> does not always push down predicates. We had this before with VTIs as well
> and I was hoping with would be fixed with Table Functions...
>
> This seems to happen when the expression becomes a little complex: In the
> trace below I run 6 queries. The initScan() method passes me the correct
> Restriction for the first 3, but nothing for the last 3:
>
> Do I need to open a bug report?
>
> Thanks
> David
>
> ====
>
> 2013-03-19 23:50:51.230~623750438 TF_TEST1 ------------------->  Entered
> RestrictedTableFunctionClass(String string) - originalSQL: SELECT COUNT(*)
> FROM TABLE( TF_TEST1() ) T WHERE C1>6
> 2013-03-19 23:50:51.230~624009129 TF_TEST1 ------------------->  initScan()
> projectedCols: [C1, null], predicates: "C1">  6
> 2013-03-19 23:50:51.230~624123999 TF_TEST1 ------------------->  Running SQL:
> select * from (VALUES
> (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff'),(7,'ggg'))
> T(C1,C2) where "C1">  6
> Tue Mar 19 23:50:51 GMT 2013 : Connection number: 13.
> 2013-03-19 23:50:51.266~660627432 TF_TEST1 ------------------->  Entered
> RestrictedTableFunctionClass(String string) - originalSQL: SELECT COUNT(*)
> FROM TABLE( TF_TEST1() ) T WHERE C1>1 AND C2<'d'
> 2013-03-19 23:50:51.267~660835226 TF_TEST1 ------------------->  initScan()
> projectedCols: [C1, C2], predicates: ( "C2"<  'd' ) AND ( "C1">  1 )
> 2013-03-19 23:50:51.267~660913206 TF_TEST1 ------------------->  Running SQL:
> select * from (VALUES
> (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff'),(7,'ggg'))
> T(C1,C2) where ( "C2"<  'd' ) AND ( "C1">  1 )
> Tue Mar 19 23:50:51 GMT 2013 : Connection number: 14.
> 2013-03-19 23:50:51.294~688381136 TF_TEST1 ------------------->  Entered
> RestrictedTableFunctionClass(String string) - originalSQL: SELECT COUNT(*)
> FROM TABLE( TF_TEST1() ) T WHERE C1>6 OR C2<'d'
> 2013-03-19 23:50:51.294~688588462 TF_TEST1 ------------------->  initScan()
> projectedCols: [C1, C2], predicates: ( "C1">  6 ) OR ( "C2"<  'd' )
> 2013-03-19 23:50:51.294~688652435 TF_TEST1 ------------------->  Running SQL:
> select * from (VALUES
> (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff'),(7,'ggg'))
> T(C1,C2) where ( "C1">  6 ) OR ( "C2"<  'd' )
> Tue Mar 19 23:50:51 GMT 2013 : Connection number: 15.
> 2013-03-19 23:50:51.318~712423513 TF_TEST1 ------------------->  Entered
> RestrictedTableFunctionClass(String string) - originalSQL: SELECT COUNT(*)
> FROM TABLE( TF_TEST1() ) T WHERE C1>6 OR (C1>1 AND C2<'d')
> 2013-03-19 23:50:51.318~712587413 TF_TEST1 ------------------->  initScan()
> projectedCols: [C1, C2], predicates: null
> 2013-03-19 23:50:51.318~712650918 TF_TEST1 ------------------->  Running SQL:
> select * from (VALUES
> (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff'),(7,'ggg'))
> T(C1,C2)
> Tue Mar 19 23:50:51 GMT 2013 : Connection number: 16.
> 2013-03-19 23:50:51.339~733547445 TF_TEST1 ------------------->  Entered
> RestrictedTableFunctionClass(String string) - originalSQL: SELECT COUNT(*)
> FROM TABLE( TF_TEST1() ) T WHERE C1>6 OR ((C1>1 AND C2<'d') AND C2>'b')
> 2013-03-19 23:50:51.339~733705275 TF_TEST1 ------------------->  initScan()
> projectedCols: [C1, C2], predicates: null
> 2013-03-19 23:50:51.340~733768313 TF_TEST1 ------------------->  Running SQL:
> select * from (VALUES
> (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff'),(7,'ggg'))
> T(C1,C2)
> Tue Mar 19 23:50:51 GMT 2013 : Connection number: 17.
> 2013-03-19 23:50:51.352~746577715 TF_TEST1 ------------------->  Entered
> RestrictedTableFunctionClass(String string) - originalSQL: SELECT COUNT(*)
> FROM TABLE( TF_TEST1() ) T WHERE C1 in ( 1, 4 ) OR C2>'f'
> 2013-03-19 23:50:51.353~746751888 TF_TEST1 ------------------->  initScan()
> projectedCols: [C1, C2], predicates: null
> 2013-03-19 23:50:51.353~746839675 TF_TEST1 ------------------->  Running SQL:
> select * from (VALUES
> (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'fff'),(7,'ggg'))
> T(C1,C2)
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/RestrictedVTI-initScan-does-not-pass-certain-Table-Functions-predicate-expressions-tp128229.html
> Sent from the Apache Derby Developers mailing list archive at Nabble.com.
>