From notifications-return-158234-archive-asf-public=cust-asf.ponee.io@asterixdb.apache.org Mon Jun 14 04:12:15 2021 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mxout1-he-de.apache.org (mxout1-he-de.apache.org [95.216.194.37]) by mx-eu-01.ponee.io (Postfix) with ESMTPS id 41201180648 for ; Mon, 14 Jun 2021 06:12:15 +0200 (CEST) Received: from mail.apache.org (mailroute1-lw-us.apache.org [207.244.88.153]) by mxout1-he-de.apache.org (ASF Mail Server at mxout1-he-de.apache.org) with SMTP id A32045FF7D for ; Mon, 14 Jun 2021 04:12:14 +0000 (UTC) Received: (qmail 39539 invoked by uid 500); 14 Jun 2021 04:12:13 -0000 Mailing-List: contact notifications-help@asterixdb.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@asterixdb.apache.org Delivered-To: mailing list notifications@asterixdb.apache.org Received: (qmail 39509 invoked by uid 99); 14 Jun 2021 04:12:11 -0000 Received: from spamproc1-he-de.apache.org (HELO spamproc1-he-de.apache.org) (116.203.196.100) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 Jun 2021 04:12:11 +0000 Received: from localhost (localhost [127.0.0.1]) by spamproc1-he-de.apache.org (ASF Mail Server at spamproc1-he-de.apache.org) with ESMTP id 377DF1FF499 for ; Mon, 14 Jun 2021 04:12:11 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamproc1-he-de.apache.org X-Spam-Flag: NO X-Spam-Score: -110.508 X-Spam-Level: X-Spam-Status: No, score=-110.508 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, RCVD_IN_DNSWL_BLOCKED=0.001, RCVD_IN_HOSTKARMA_W=-2.5, RCVD_IN_MSPIKE_H3=0.001, RCVD_IN_MSPIKE_WL=0.001, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WELCOMELIST=-0.01, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-he-de.apache.org ([116.203.227.195]) by localhost (spamproc1-he-de.apache.org [116.203.196.100]) (amavisd-new, port 10024) with ESMTP id GtR--S-8eeeE for ; Mon, 14 Jun 2021 04:12:10 +0000 (UTC) Received-SPF: Pass (mailfrom) identity=mailfrom; client-ip=3.227.148.255; helo=mxout1-ec2-va.apache.org; envelope-from=jira@apache.org; receiver= Received: from mxout1-ec2-va.apache.org (mxout1-ec2-va.apache.org [3.227.148.255]) by mx1-he-de.apache.org (ASF Mail Server at mx1-he-de.apache.org) with ESMTPS id 9C4AF7FFC2 for ; Mon, 14 Jun 2021 04:12:10 +0000 (UTC) Received: from mail.apache.org (mailroute1-lw-us.apache.org [207.244.88.153]) by mxout1-ec2-va.apache.org (ASF Mail Server at mxout1-ec2-va.apache.org) with SMTP id 911164003E for ; Mon, 14 Jun 2021 04:12:09 +0000 (UTC) Received: (qmail 38206 invoked by uid 99); 14 Jun 2021 04:12:01 -0000 Received: from mailrelay1-he-de.apache.org (HELO mailrelay1-he-de.apache.org) (116.203.21.61) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 Jun 2021 04:12:01 +0000 Received: from jira2-he-de.apache.org (jira2-he-de.apache.org [168.119.33.54]) by mailrelay1-he-de.apache.org (ASF Mail Server at mailrelay1-he-de.apache.org) with ESMTPS id 2E2EE3E8AA for ; Mon, 14 Jun 2021 04:12:00 +0000 (UTC) Received: from jira2-he-de.apache.org (localhost.localdomain [127.0.0.1]) by jira2-he-de.apache.org (ASF Mail Server at jira2-he-de.apache.org) with ESMTP id 12CFFC804D4 for ; Mon, 14 Jun 2021 04:12:00 +0000 (UTC) Date: Mon, 14 Jun 2021 04:12:00 +0000 (UTC) From: "Michael J. Carey (Jira)" To: notifications@asterixdb.incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (ASTERIXDB-2913) Need "break up to make up" rewrite rule for unnested data MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/ASTERIXDB-2913?page=3Dcom.atla= ssian.jira.plugin.system.issuetabpanels:all-tabpanel ] Michael J. Carey updated ASTERIXDB-2913: ---------------------------------------- Description:=20 Consider the following query using the Don Chamberlin book data: =C2=A0 _USE DonCData;_=20 _WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM or= ders),_ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0_flat_items AS (SELECT o.orderno, = i.* FROM orders o, o.items i)_ _SELECT fo.*, fi.* FROM flat_orders fo, flat_items fi_ _WHERE fo.orderno =3D fi.orderno;_ Right now this query leads to two scans of orders - one for flat_orders and= one for flat_items - followed by a join of the two. It seems like it should be possible to rewrite (under the hood) this query = as simply: _USE DonCData;_=20 _SELECT o.orderno, o.custid, o.order_date, o.ship_date, i.*_ _FROM orders o, o.items i;_ This capability could prove super-helpful in our future BI story.=C2=A0 (Pi= cture two flat views being exported for JDBC - it would be nice to be able = to piece them back together for efficient querying under the hood.) was: Consider the following query using the Don Chamberlin book data: {quote}{\{USE DonCData; }} \{{ WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FRO= M orders),}} {{=C2=A0 =C2=A0 =C2=A0flat_items AS (SELECT o.orderno, i.* FROM orders o, = o.items i)}} {{ SELECT fo.*, fi.* FROM flat_orders fo, flat_items fi}} \{{ WHERE fo.orderno =3D fi.orderno;}}{quote} Right now this query leads to two scans of orders - one for flat_orders and= one for flat_items - followed by a join of the two.=C2=A0 It seems like it should be possible to rewrite the query as simply: {quote}{{USE DonCData; }} {{ SELECT o.orderno, o.custid, o.order_date, o.ship_date, i.*}} {{ FROM orders o, o.items i;}}{quote} This capability could prove super-helpful in our future BI story.=C2=A0 (Pi= cture two flat views being exported for JDBC - it would be nice to be able = to piece them back together for efficient querying under the hood.) > Need "break up to make up" rewrite rule for unnested data > --------------------------------------------------------- > > Key: ASTERIXDB-2913 > URL: https://issues.apache.org/jira/browse/ASTERIXDB-2913 > Project: Apache AsterixDB > Issue Type: Bug > Reporter: Michael J. Carey > Priority: Major > > Consider the following query using the Don Chamberlin book data: > =C2=A0 > _USE DonCData;_=20 > _WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM = orders),_ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0_flat_items AS (SELECT o.orderno= , i.* FROM orders o, o.items i)_ > _SELECT fo.*, fi.* FROM flat_orders fo, flat_items fi_ > _WHERE fo.orderno =3D fi.orderno;_ > Right now this query leads to two scans of orders - one for flat_orders a= nd one for flat_items - followed by a join of the two. > It seems like it should be possible to rewrite (under the hood) this quer= y as simply: > _USE DonCData;_=20 > _SELECT o.orderno, o.custid, o.order_date, o.ship_date, i.*_ > _FROM orders o, o.items i;_ > This capability could prove super-helpful in our future BI story.=C2=A0 (= Picture two flat views being exported for JDBC - it would be nice to be abl= e to piece them back together for efficient querying under the hood.) -- This message was sent by Atlassian Jira (v8.3.4#803005)