Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 4252A200CE5 for ; Sun, 23 Jul 2017 03:12:04 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 40D6C164703; Sun, 23 Jul 2017 01:12:04 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 86B47164702 for ; Sun, 23 Jul 2017 03:12:03 +0200 (CEST) Received: (qmail 25645 invoked by uid 500); 23 Jul 2017 01:12:02 -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 25626 invoked by uid 99); 23 Jul 2017 01:12:02 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 23 Jul 2017 01:12:02 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 1DF79C0C05 for ; Sun, 23 Jul 2017 01:12:02 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -99.202 X-Spam-Level: X-Spam-Status: No, score=-99.202 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, RP_MATCHES_RCVD=-0.001, SPF_PASS=-0.001, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id EQQOelim7XBL for ; Sun, 23 Jul 2017 01:12:01 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id D71295F3BF for ; Sun, 23 Jul 2017 01:12:00 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 6A9A2E0630 for ; Sun, 23 Jul 2017 01:12:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 23CDA21EE2 for ; Sun, 23 Jul 2017 01:12:00 +0000 (UTC) Date: Sun, 23 Jul 2017 01:12:00 +0000 (UTC) From: "Rick Hillegas (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References:

Subject: [jira] [Commented] (DERBY-6953) Support Standard syntax for retrieving INSERTed key values MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Sun, 23 Jul 2017 01:12:04 -0000 [ https://issues.apache.org/jira/browse/DERBY-6953?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16097472#comment-16097472 ] Rick Hillegas commented on DERBY-6953: -------------------------------------- Thanks for that reference, Lukas. The grammar stanzas you cited are from the 2016 Standard, part 2, section 7.6 . According to GR 5b, for an INSERT, with NEW, the contents of the are defined by section 15.10 (Effect of inserting tables into base tables). It appears to be the set of all inserted rows, including all of their columns. My reading of this language is that the user would phrase an insert statement like this: {noformat} select keyCol from new table ( insert into t(a, b, c) select (x, y, z) from s ) ; {noformat} See, for instance, https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_selectvaluesmerge.html I think that there is a fair amount of language to build here in order to wire this into Derby's access control language and trigger mechanism. However, the solution is attractive. For best performance, we would want to push the projection (keyCol in the example above) down into the so that the engine only collects the desired column (keyCol) as the engine processes the insert. Thanks, -Rick > Support Standard syntax for retrieving INSERTed key values > ------------------------------------------------------------------------------------ > > Key: DERBY-6953 > URL: https://issues.apache.org/jira/browse/DERBY-6953 > Project: Derby > Issue Type: Improvement > Components: SQL > Affects Versions: 10.13.1.1 > Reporter: Lukas Eder > > The SQL standard supports an interesting syntax that can be used as a

: > {code} > ::= > TABLE > ::= > > | > | > | > ::= > FINAL > | NEW > | OLD > {code} > This is currently supported by DB2. Databases like Firebird, Oracle (in PL/SQL), PostgreSQL support an alternative syntax through the RETURNING keyword that can be appended to . SQL Server has an OUTPUT keyword that can be placed in the middle of a . > These statements are incredibly useful to retrieve generated ID values but also trigger-generated values after a DML operation for an arbitrary number of inserted / updated / deleted / merged rows. > It would allow people to bypass the many problems that are currently still open related to Statement.getGeneratedKeys(). Quite likely, if these clauses were made available, Statement.getGeneratedKeys() could be implemented by patching the user-defined SQL to be wrapped with a clause. -- This message was sent by Atlassian JIRA (v6.4.14#64029)