[
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:
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;
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.
> 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)
