Return-Path: X-Original-To: apmail-drill-issues-archive@minotaur.apache.org Delivered-To: apmail-drill-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id B40F01802E for ; Tue, 4 Aug 2015 01:14:04 +0000 (UTC) Received: (qmail 94134 invoked by uid 500); 4 Aug 2015 01:14:04 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 94096 invoked by uid 500); 4 Aug 2015 01:14:04 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 94085 invoked by uid 99); 4 Aug 2015 01:14:04 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Aug 2015 01:14:04 +0000 Date: Tue, 4 Aug 2015 01:14:04 +0000 (UTC) From: "Khurram Faraaz (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (DRILL-3599) Wrong results returned by LEAD(col-name, -1) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 Khurram Faraaz created DRILL-3599: ------------------------------------- Summary: Wrong results returned by LEAD(col-name, -1) Key: DRILL-3599 URL: https://issues.apache.org/jira/browse/DRILL-3599 Project: Apache Drill Issue Type: Bug Components: Execution - Flow Affects Versions: 1.2.0 Environment: private-branch-with-new-window-funcs Reporter: Khurram Faraaz Assignee: Chris Westin Query that uses LEAD(col-name,-1) returns incorrect results. 1. Should we allow this, because an offset -1 does not make sense (offset value must be a non-negative integer) 2. If we should support this, then our current results are different from those returned by Postgres. Results returned by Drill {code} 0: jdbc:drill:schema=dfs.tmp> select c1, lead(c1,-1) over w from union_01 window w as (partition by c3 order by c1) order by c1; +---------+---------+ | c1 | EXPR$1 | +---------+---------+ | -36559 | -36559 | | -36559 | 1224 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -36559 | -36559 | | -788 | null | | -409 | null | | -168 | -121 | | -150 | 402 | | -146 | -1 | | -121 | null | | -104 | 848 | | -104 | -104 | | -1 | 0 | | 0 | 10000 | | 0 | 0 | | 0 | 0 | | 160 | 160 | | 160 | 160 | | 160 | 160 | | 160 | null | | 160 | 160 | | 402 | 402 | | 402 | 402 | | 402 | 402 | | 402 | null | | 402 | 402 | | 848 | 848 | | 848 | 848 | | 848 | 848 | | 848 | 848 | | 848 | 848 | | 848 | 848 | | 848 | 848 | | 848 | null | | 848 | 848 | | 878 | null | | 1224 | 1224 | | 1224 | 1224 | | 1224 | 1224 | | 1224 | 1224 | | 1224 | 1224 | | 1224 | 1224 | | 1224 | null | | 1234 | null | | 1234 | 1234 | | 1234 | 1234 | | 1234 | 1234 | | 1234 | 1234 | | 1234 | 1234 | | 1234 | 1234 | | 1234 | 1234 | | 10000 | 10000 | | 10000 | null | | 10000 | 10000 | | null | null | | null | null | | null | null | | null | null | +---------+---------+ 65 rows selected (0.621 seconds) {code} Results returned by Postgres {code} postgres=# select c1, lead(c1,-1) over w from union_01 window w as (partition by c3 order by c1) order by c1; c1 | lead --------+-------- -36559 | -36559 -36559 | -36559 -36559 | -36559 -36559 | -36559 -36559 | -36559 -36559 | -36559 | -36559 -36559 | -36559 -36559 | -36559 -36559 | -36559 -36559 | -36559 -788 | -409 | -168 | -150 | -146 | -121 | -168 -104 | -104 -104 | -1 | -146 0 | -1 0 | 0 0 | 0 160 | 160 160 | 160 160 | 160 | 160 160 | 160 402 | 402 402 | 402 402 | -150 402 | 402 402 | 402 848 | 848 848 | -104 848 | 848 848 | 848 848 | 848 848 | 848 848 | 848 848 | 848 848 | 848 878 | 1224 | 1224 1224 | 1224 1224 | 1224 1224 | 1224 1224 | 1224 1224 | -36559 1224 | 1224 1234 | 1234 1234 | 1234 | 1234 1234 | 1234 1234 | 1234 1234 | 1234 1234 | 1234 1234 | 1234 10000 | 10000 10000 | 10000 10000 | 0 | | | 10000 | (65 rows) {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)