# The filter of function left is not pushed down **URL:** \ **Category:** Site Feedback\ **Created:** [May 24, 2021, 4:04am UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512 "2021-05-24T04:04:19Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![wujx](https://avatars.discourse-cdn.com/v4/letter/w/e19adc/32.png) [@wujx](https://community.dremio.com/u/wujx)\ **Post date:** [May 24, 2021, 4:04am UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512/1 "2021-05-24T04:04:19Z")
Env info: Dremio dremio-community-15.5.0 DB type: mysql5.7 Table struct: CREATE TABLE `prj_working1` ( `PRJ_W_DATE` date NULL DEFAULT NULL, `EMP_ID` varchar(36) CHARACTER SET utf8 COLLATE utf8\_general\_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1\_swedish\_ci ROW\_FORMAT = Dynamic; * * * Used sql below to query the mysql table, check the physical plan found filter is not pushed down. ```auto SELECT * FROM prj_working1 WHERE LEFT(PRJ_W_DATE, 7) = LEFT(NOW(), 7) AND EMP_ID = '7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9' ``` 00-00 Screen : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {9.1 rows, 14.790119999999998 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6393 00-01 Project(Fragment=[$0], Records=[$1], Path=[$2], Metadata=[$3], Partition=[$4], FileSize=[$5], IcebergMetadata=[$6]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {9.0 rows, 14.690119999999999 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6392 00-02 WriterCommitter(final=[/opt/dremio-community-15.5.0-202105040508160960-dddd4dca/data/pdfs/results/1f588834-866c-2fe0-7506-d0a22a5ed900]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {8.0 rows, 14.69005 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6391 00-03 Writer : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {7.0 rows, 13.69005 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6390 00-04 Project(PRJ\_W\_DATE=[$0], EMP\_ID=[$1]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {6.0 rows, 12.69005 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6389 00-05 Project(PRJ\_W\_DATE=[$0], EMP\_ID=[CAST(‘7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9’):VARCHAR(65536) CHARACTER SET “ISO-8859-1” COLLATE “ISO-8859-1$en\_US$primary”]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {5.0 rows, 12.69003 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6388 00-06 SelectionVectorRemover : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {4.0 rows, 8.69002 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6387 00-07 Filter(condition=[AND(=(LEFT($0, 7), ‘2021-05’), =($1, ‘7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9’))]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {3.0 rows, 7.69002 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6386 00-08 Project(PRJ\_W\_DATE=[$0], EMP\_ID=[$1]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {2.0 rows, 1.00002 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6385 00-09 Jdbc(sql=[SELECT `prj_working1`.`PRJ_W_DATE`, `prj_working1`.`EMP_ID` FROM `test`.`prj_working1`]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {tiny}, id = 6298 * * * Change the function LEFT(PRJ\_W\_DATE, 7) to LEFT(CAST(PRJ\_W\_DATE AS CHAR), 7) , and run again, check the physical plan found this query can be pushed down. ```auto SELECT * FROM prj_working1 WHERE LEFT(CAST(PRJ_W_DATE AS CHAR), 7) = LEFT(NOW(), 7) AND EMP_ID = '7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9' ``` 00-00 Screen : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {9.1 rows, 5.10013 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6118 00-01 Project(Fragment=[$0], Records=[$1], Path=[$2], Metadata=[$3], Partition=[$4], FileSize=[$5], IcebergMetadata=[$6]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {9.0 rows, 5.00013 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6117 00-02 WriterCommitter(final=[/opt/dremio-community-15.5.0-202105040508160960-dddd4dca/data/pdfs/results/1f588b63-bfcc-0bef-2155-05911c7a4800]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {8.0 rows, 5.00006 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6116 00-03 Writer : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize, VARBINARY(65536) IcebergMetadata): rowcount = 1.0, cumulative cost = {7.0 rows, 4.00006 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6115 00-04 Project(PRJ\_W\_DATE=[$0], EMP\_ID=[$1]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {6.0 rows, 3.0000600000000004 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6114 00-05 Project(PRJ\_W\_DATE=[$0], EMP\_ID=[$1]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {5.0 rows, 3.0000400000000003 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6113 00-06 Project(PRJ\_W\_DATE=[$0], EMP\_ID=[$1]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {4.0 rows, 3.00002 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6112 00-07 Jdbc(sql=[SELECT `prj_working1`.`PRJ_W_DATE`, CAST(‘7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9’ AS CHAR(65536)) AS `EMP_ID` FROM `test`.`prj_working1` WHERE LEFT(CAST(`prj_working1`.`PRJ_W_DATE` AS CHAR(65536)), 7) = ‘2021-05’ AND `prj_working1`.`EMP_ID` = ‘7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9’]) : rowType = RecordType(DATE PRJ\_W\_DATE, VARCHAR(65536) EMP\_ID): rowcount = 1.0, cumulative cost = {3.0 rows, 3.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 6032 ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [May 24, 2021, 6:52am UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512/2 "2021-05-24T06:52:00Z")
@wujx Able to reproduce the behavior, have asked about this internally if it is a bug, will keep you posted ---
**Author:** ![Michael\_Flower](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/michael_flower/32/3165_2.png) [@Michael\_Flower](https://community.dremio.com/u/Michael_Flower)\ **Post date:** [May 24, 2021, 1:05pm UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512/3 "2021-05-24T13:05:34Z")
@wujx : I found that if I created a derived column, and then filter on that instead, then the filter is pushed down: eg. - Create VDS: - Use the VDS in the query: SELECT PRJ\_W\_DATE, EMP\_ID FROM prj\_working1\_vds WHERE PRJ\_W\_DATE\_YYYY\_MM = LEFT(NOW(), 7) AND EMP\_ID = ‘7bcfa8e5-7ca1-45df-b371-a7d06bf9b7a9’; ---
**Author:** ![wujx](https://avatars.discourse-cdn.com/v4/letter/w/e19adc/32.png) [@wujx](https://community.dremio.com/u/wujx)\ **Post date:** [May 25, 2021, 3:21am UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512/4 "2021-05-25T03:21:52Z")
@Michael_Flower @balaji.ramaswamy Thanks a lot for the solution and updates Now we have workaround for this issue, and it’s an optimization issue for product. So I have two concerns: 1. If this issue will be fixed in your plan? So we can run the sql more convenience without create vds or change sql. 2. What’s your suggest usage of VDS? Can VDS resolve sql pushdown issue in many situation? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [May 25, 2021, 6:45am UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512/5 "2021-05-25T06:45:50Z")
@wujx #1 Is currently in Engineering backlog #2 In general push down should happen, if not depending on the workaround, a VDS might help ---
**Author:** ![wujx](https://avatars.discourse-cdn.com/v4/letter/w/e19adc/32.png) [@wujx](https://community.dremio.com/u/wujx)\ **Post date:** [May 26, 2021, 8:38am UTC](https://community.dremio.com/t/the-filter-of-function-left-is-not-pushed-down/7512/6 "2021-05-26T08:38:26Z")
Thank you for your helps~ Will try more about it.