Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: softfail (athena.apache.org: transitioning domain of
 drvyvyan@hotmail.com does not designate 216.139.236.26 as permitted sender)
Date: Tue, 19 Mar 2013 16:56:58 -0700 (PDT)
From: drv <drvyvyan@hotmail.com>
To: derby-dev@db.apache.org
Message-ID: <1363737418606-128229.post@n7.nabble.com>
Subject: RestrictedVTI.initScan() does not pass certain Table Functions'
 predicate expressions
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.