[SPARK-9182] filter and groupBy on DataFrames are not passed through to jdbc source

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 1.4.1
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Greg Rahn
• Created: 19/Jul/15 21:22
• Updated: 12/Dec/22 18:11
• Resolved: 04/Jan/21 05:52

Description

When running all of these API calls, the only one that passes the filter through to the backend jdbc source is equality. All filters in these commands should be able to be passed through to the jdbc database source.

val url="jdbc:postgresql:grahn"
val prop = new java.util.Properties
val emp = sqlContext.read.jdbc(url, "emp", prop)

emp.filter(emp("sal") === 5000).show()
emp.filter(emp("sal") < 5000).show()
emp.filter("sal = 3000").show()
emp.filter("sal > 2500").show()
emp.filter("sal >= 2500").show()
emp.filter("sal < 2500").show()
emp.filter("sal <= 2500").show()
emp.filter("sal != 3000").show()
emp.filter("sal between 3000 and 5000").show()
emp.filter("ename in ('SCOTT','BLAKE')").show()

We see from the PostgreSQL query log the following is run, and see that only equality predicates are passed through.

LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp WHERE sal = 5000
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp WHERE sal = 3000
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp
LOG:  execute <unnamed>: SET extra_float_digits = 3
LOG:  execute <unnamed>: SELECT "empno","ename","job","mgr","hiredate","sal","comm","deptno" FROM emp

Issue Links

• duplicates: SPARK-22386 Data Source V2 improvements
• is related to: SPARK-24994 Add UnwrapCastInBinaryComparison optimizer to simplify literal types
• is related to: SPARK-9938 Constant folding in binaryComparison
• relates to: SPARK-12449 Pushing down arbitrary logical plans to data sources
• links to: [Github] Pull Request #8049 (yjshen)
• links to: [Github] Pull Request #8718 (HyukjinKwon)