[DRILL-6078] Query with INTERVAL in predicate does not return any rows

• Type: Bug
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.12.0
• Component/s: Query Planning & Optimization
• Assignee: Chunhui Shi
• Reporter: Robert Hou
• Reviewer: Gautam Parai
• Created: 09/Jan/18 07:04
• Updated: 14/Sep/18 21:23

Description

This query does not return any rows when accessing MapR DB tables.

SELECT
C.C_CUSTKEY,
C.C_NAME,
SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE,
C.C_ACCTBAL,
N.N_NAME,
C.C_ADDRESS,
C.C_PHONE,
C.C_COMMENT
FROM
customer C,
orders O,
lineitem L,
nation N
WHERE
C.C_CUSTKEY = O.O_CUSTKEY
AND L.L_ORDERKEY = O.O_ORDERKEY
AND O.O_ORDERDate >= DATE '1994-03-01'
AND O.O_ORDERDate < DATE '1994-03-01' + INTERVAL '3' MONTH
AND L.L_RETURNFLAG = 'R'
AND C.C_NATIONKEY = N.N_NATIONKEY
GROUP BY
C.C_CUSTKEY,
C.C_NAME,
C.C_ACCTBAL,
C.C_PHONE,
N.N_NAME,
C.C_ADDRESS,
C.C_COMMENT
ORDER BY
REVENUE DESC
LIMIT 20

This query works against JSON tables. It should return 20 rows.

Issue Links

• links to: GitHub Pull Request #1089