db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From drv <drvyv...@hotmail.com>
Subject RestrictedVTI.initScan() does not pass certain Table Functions' predicate expressions
Date Tue, 19 Mar 2013 23:56:58 GMT
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.

Mime
View raw message