[ https://issues.apache.org/jira/browse/ASTERIXDB-2913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael J. Carey updated ASTERIXDB-2913: ---------------------------------------- Description: Consider the following query using the Don Chamberlin book data: {quote}{\{USE DonCData; }} \{{ WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM orders),}} {{ 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 = 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. 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. (Picture 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 FROM orders),}} {{ 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 = 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. 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. (Picture 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: > {quote}{\{USE DonCData; }} > \{{ WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM > orders),}} > {{ 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 = 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. > 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. (Picture > 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.) -- This message was sent by Atlassian Jira (v8.3.4#803005)