From derby-user-return-15953-apmail-db-derby-user-archive=db.apache.org@db.apache.org Sun May 17 23:35:36 2015 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 876A010BC3 for ; Sun, 17 May 2015 23:35:36 +0000 (UTC) Received: (qmail 90043 invoked by uid 500); 17 May 2015 23:35:35 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 90001 invoked by uid 500); 17 May 2015 23:35:35 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 89991 invoked by uid 99); 17 May 2015 23:35:35 -0000 Received: from Unknown (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 17 May 2015 23:35:35 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 44BB81A2E2B for ; Sun, 17 May 2015 23:35:35 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -0.121 X-Spam-Level: X-Spam-Status: No, score=-0.121 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id ZxNByxQ27Z2P for ; Sun, 17 May 2015 23:35:34 +0000 (UTC) Received: from mail-pa0-f50.google.com (mail-pa0-f50.google.com [209.85.220.50]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id 87C1120659 for ; Sun, 17 May 2015 23:35:34 +0000 (UTC) Received: by pacwv17 with SMTP id wv17so125354769pac.2 for ; Sun, 17 May 2015 16:35:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; bh=syWx6wNy+nmXbhaoDt+j/eHpP0CSjJCzofIeabOz1DM=; b=IglKmg2UTnhVrsYf+ESpWMd31K7Vkhsw7ajISa6TN8xwhHxZ1/8UwV3STBNbZdaM6K 9Bnkqw73npW7sR225J5qYvEa7VSWM+PgngSiJEnI9l10vroUgW+VJd6WwMA2ryY5lui6 1pKzMM7tCuBDtO8asanOMx4+guS1QePH1qlGA7tdgthuPbJNj/vJpR0P09JnRPI7wTIO mfDKKjGfuuB+sWEmWM4B0NSgbTyMpQ4pJc6zaa15KhGU4CKTzVQBtl0ZfPnlBCyCClfC ml0XPPr59xc3ELGx0YluOsNJqR/5TLngJPBIoACaP6WpfBp8EsNTF4CUVf878OiyVTW+ Tiiw== X-Received: by 10.70.90.100 with SMTP id bv4mr39011449pdb.53.1431905734050; Sun, 17 May 2015 16:35:34 -0700 (PDT) Received: from Richards-MacBook-Pro-2.local ([2601:9:3480:baa:ec11:1f68:47d9:4c9b]) by mx.google.com with ESMTPSA id af1sm8096786pad.34.2015.05.17.16.35.33 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sun, 17 May 2015 16:35:33 -0700 (PDT) Message-ID: <555925C3.8070502@gmail.com> Date: Sun, 17 May 2015 16:35:31 -0700 From: Rick Hillegas User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US; rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11 MIME-Version: 1.0 To: Derby Discussion Subject: Re: can't get warning 01003 in nested select in update References: <5557535C.8080202@gmail.com> In-Reply-To: <5557535C.8080202@gmail.com> Content-Type: text/plain; charset=gb18030; format=flowed Content-Transfer-Encoding: 7bit On 5/16/15 7:25 AM, Bryan Pendleton wrote: >> 3, when the select in an update statement, can't get the warning 01003. >> ij(CONNECTION1)> UPDATE APP.EMP T1 SET SALARY = ( SELECT AVG ( T2 . >> SALARY ) FRO >> M APP.EMP T2 ) WHERE WORKDEPT = 'D21' AND SALARY < ( SELECT AVG ( T3 >> . SALARY ) >> FROM APP.EMP T3 ); >> 7 rows inserted/updated/deleted > > The lack of the warning seems incorrect to me. But I'm not sure if this > behavior is governed by the SQL Standards or not. > > thanks, > > bryan > > > Nulls are ignored when computing aggregates, but they should cause the statement to raise a warning, as specified in the 2011 SQL Standard, part 2, section 10.9 (aggregate function), General Rule 6a. I don't see anything in the Standard which absolves the UPDATE statement of its responsibility to report a warning encountered during the processing of an aggregate. So, I agree. This looks like a bug to me. Please feel free to log a bug. Thanks, -Rick