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.
