From derby-dev-return-99088-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Tue Nov 6 16:44:14 2012 Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 9AFDCD1E4 for ; Tue, 6 Nov 2012 16:44:14 +0000 (UTC) Received: (qmail 14619 invoked by uid 500); 6 Nov 2012 16:44:14 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 14584 invoked by uid 500); 6 Nov 2012 16:44:14 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 14531 invoked by uid 99); 6 Nov 2012 16:44:12 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Nov 2012 16:44:12 +0000 Date: Tue, 6 Nov 2012 16:44:12 +0000 (UTC) From: "Dag H. Wanvik (JIRA)" To: derby-dev@db.apache.org Message-ID: <203910497.75043.1352220252949.JavaMail.jiratomcat@arcas> In-Reply-To: <1503509585.57712.1350481323453.JavaMail.jiratomcat@arcas> Subject: [jira] [Commented] (DERBY-5954) NPE in SELECT involving subselects and windows functions MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-5954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13491597#comment-13491597 ] Dag H. Wanvik commented on DERBY-5954: -------------------------------------- I'll try to explain why I believe the changed code is correct. Sorry for answering late, I have been out for a while. What we are looking for here is any "" in the sense of SQL 2003 section 6.10 "Window Function" contained in a select list (syntax rule 3b). The meaning of the in-line window specification is described in Syntax Rule 7-9 of section 6.10. Pertinent quote (simplified): 7) Let SL be the that is obtained from SL by replacing OF by: OFT OVER WSN (where WSN is an equivalent synthesized window on the table expression TE) Rule 9 f) and g) describes how our inlined window is transformed to be appended to any existing windows for the table expression. Now, earlier, we found window functions *not* simply contained in the SL, i.e. inside a nested SELECT since the visitor went to deep. The inlined window specification properly belonged at the level of that nested SELECT's table expression. So, Bryans Criterion 1) is satisfied, in that the visitor previously visited *all* nodes contained in the select list. After the change we do not visit nodes *under* a simply contained (nested) SELECT statement. But that is OK, since any OF previosuly (erroneously) found there are not *simply contained in the outer SL. So 1) holds I believe. Now, as for 2) could we (still) be descending into any subquery we should not (we obviously did before)? That would need to be in a case where we had some legal syntax in the select list (SL) containing in-lined window specification *not* sinde a SELECT node. Can that happen? Well, besides SELECT we can have a VALUES subquery in a SELECT list. But we forbid window functions inside a VALUES subquery, cf. this line in RowResultSetNode#bindExpressions: SelectNode.checkNoWindowFunctions(resultColumns, "VALUES"); So, since we now forbid the visitor to descend into nested SELECTs and any nested VALUES are innocuous, we can not find any "wrong" in-lined window function. So 2) also holds. [1] A1 simply contains B1 if A1 contains B1 without an intervening instance of or an intervening instance of . Makes sense? > NPE in SELECT involving subselects and windows functions > -------------------------------------------------------- > > Key: DERBY-5954 > URL: https://issues.apache.org/jira/browse/DERBY-5954 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.9.1.0 > Reporter: Rick Hillegas > Assignee: Dag H. Wanvik > Attachments: derby-5954.diff, derby-5954-with-test.diff, derby-5954-with-test.stat > > > A user reports and I have verified an NPE on the following SELECT: > connect 'jdbc:derby:memory:db;create=true'; > create table blah ( a int ); > insert into blah values (1), (2), (3), (4), (5), (6), (7); > SELECT rn, (SELECT rn FROM (SELECT row_number() over() rn FROM blah ) as T2 > where T2.rn = T1.rn+1) rn2 > FROM (SELECT row_number() over() rn from blah) as T1; -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira