Return-Path: X-Original-To: apmail-drill-issues-archive@minotaur.apache.org Delivered-To: apmail-drill-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 3CAE510176 for ; Fri, 30 May 2014 18:11:29 +0000 (UTC) Received: (qmail 7719 invoked by uid 500); 30 May 2014 18:11:29 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 7695 invoked by uid 500); 30 May 2014 18:11:29 -0000 Mailing-List: contact issues-help@drill.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.incubator.apache.org Delivered-To: mailing list issues@drill.incubator.apache.org Received: (qmail 7686 invoked by uid 99); 30 May 2014 18:11:29 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 May 2014 18:11:29 +0000 X-ASF-Spam-Status: No, hits=-2000.7 required=5.0 tests=ALL_TRUSTED,LOTS_OF_MONEY,RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO mail.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with SMTP; Fri, 30 May 2014 18:11:27 +0000 Received: (qmail 7307 invoked by uid 99); 30 May 2014 18:11:02 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 May 2014 18:11:02 +0000 Date: Fri, 30 May 2014 18:11:01 +0000 (UTC) From: "Krystal (JIRA)" To: issues@drill.incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DRILL-867) tpcds queries 6, 8 and 9 fail to plan MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DRILL-867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14014034#comment-14014034 ] Krystal commented on DRILL-867: ------------------------------- Query 9: {code} select case when (select count(*) from store_sales ss where ss.ss_quantity between 1 and 20) > 30992 then (select avg(ss.ss_ext_sales_price) from store_sales ss where ss.ss_quantity between 1 and 20) else (select avg(ss.ss_net_paid) from store_sales ss where ss.ss_quantity between 1 and 20) end bucket1 , case when (select count(*) from store_sales ss where ss.ss_quantity between 21 and 40) > 25740 then (select avg(ss.ss_ext_sales_price) from store_sales ss where ss.ss_quantity between 21 and 40) else (select avg(ss.ss_net_paid) from store_sales ss where ss.ss_quantity between 21 and 40) end bucket2, case when (select count(*) from store_sales ss where ss.ss_quantity between 41 and 60) > 20311 then (select avg(ss.ss_ext_sales_price) from store_sales ss where ss.ss_quantity between 41 and 60) else (select avg(ss.ss_net_paid) from store_sales ss where ss.ss_quantity between 41 and 60) end bucket3, case when (select count(*) from store_sales ss where ss.ss_quantity between 61 and 80) > 21635 then (select avg(ss.ss_ext_sales_price) from store_sales ss where ss.ss_quantity between 61 and 80) else (select avg(ss.ss_net_paid) from store_sales ss where ss.ss_quantity between 61 and 80) end bucket4, case when (select count(*) from store_sales ss where ss.ss_quantity between 81 and 100) > 20532 then (select avg(ss.ss_ext_sales_price) from store_sales ss where ss.ss_quantity between 81 and 100) else (select avg(ss.ss_net_paid) from store_sales ss where ss.ss_quantity between 81 and 100) end bucket5 from reason where reason.r_reason_sk = 1 ; "message: ""Failure while parsing sql. < CannotPlanException:[ Node [rel#40624:Subset#192.PHYSICAL.SINGLETON([]).[]] could not be implemented; planner state: Root: rel#40624:Subset#192.PHYSICAL.SINGLETON([]).[] Original rel: AbstractConverter(subset=[rel#40624:Subset#192.PHYSICAL.SINGLETON([]).[]], convention=[PHYSICAL], DrillDistributionTraitDef=[SINGLETON([])], sort=[[]]): rowcount = 3.811189240726212E73, cumulative cost = {inf}, id = 40626 DrillScreenRel(subset=[rel#40623:Subset#192.LOGICAL.ANY([]).[]]): rowcount = 3.811189240726212E73, cumulative cost = {3.811189240726212E72 rows, 3.811189240726212E72 cpu, 0.0 io, 0.0 network}, id = 40622 DrillProjectRel(subset=[rel#40621:Subset#191.LOGICAL.ANY([]).[]], bucket1=[CASE(>($2, 30992), $3, $4)], bucket2=[CASE(>($5, 25740), $6, $7)], bucket3=[CASE(>($8, 20311), $9, $10)], bucket4=[CASE(>($11, 21635), $12, $13)], bucket5=[CASE(>($14, 20532), $15, $16)]): rowcount = 3.811189240726212E73, cumulative cost = {3.811189240726212E73 rows, 20.0 cpu, 0.0 io, 0.0 network}, id = 40620 DrillJoinRel(subset=[rel#40619:Subset#190.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 3.811189240726212E73, cumulative cost = {3.811189240726212E73 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40618 DrillJoinRel(subset=[rel#40612:Subset#187.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 5.292575959103254E68, cumulative cost = {5.292575959103254E68 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40611 DrillJoinRel(subset=[rel#40605:Subset#184.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 7.349768933945729E63, cumulative cost = {7.349768933945729E63 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40604 DrillJoinRel(subset=[rel#40597:Subset#180.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 1.020658065180541E59, cumulative cost = {1.020658065180541E59 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40596 DrillJoinRel(subset=[rel#40590:Subset#177.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 1.417381818912265E54, cumulative cost = {1.417381818912265E54 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40589 DrillJoinRel(subset=[rel#40583:Subset#174.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 1.9683097494827322E49, cumulative cost = {1.9683097494827322E49 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40582 DrillJoinRel(subset=[rel#40575:Subset#170.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 2.733380108460802E44, cumulative cost = {2.733380108460802E44 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40574 DrillJoinRel(subset=[rel#40568:Subset#167.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 3.7958287913234415E39, cumulative cost = {3.7958287913234415E39 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40567 DrillJoinRel(subset=[rel#40561:Subset#164.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 5.271244993859808E34, cumulative cost = {5.271244993859808E34 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40560 DrillJoinRel(subset=[rel#40553:Subset#160.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 7.320146748664156E29, cumulative cost = {7.320146748664156E29 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40552 DrillJoinRel(subset=[rel#40546:Subset#157.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 1.0165444498291428E25, cumulative cost = {1.0165444498291428E25 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40545 DrillJoinRel(subset=[rel#40539:Subset#154.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 1.411669265601829E20, cumulative cost = {1.411669265601829E20 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40538 DrillJoinRel(subset=[rel#40531:Subset#150.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 1.9603767604847498E15, cumulative cost = {1.9603767604847498E15 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40530 DrillJoinRel(subset=[rel#40524:Subset#147.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 2.7223636135552505E10, cumulative cost = {2.7223636135552505E10 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40523 DrillJoinRel(subset=[rel#40517:Subset#144.LOGICAL.ANY([]).[]], condition=[true], joinType=[left]): rowcount = 378053.025, cumulative cost = {378053.025 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40516 DrillFilterRel(subset=[rel#40508:Subset#139.LOGICAL.ANY([]).[]], condition=[=(CAST($1):INTEGER, 1)]): rowcount = 5.25, cumulative cost = {35.0 rows, 140.0 cpu, 0.0 io, 0.0 network}, id = 40507 DrillScanRel(subset=[rel#40506:Subset#138.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, reason]]): rowcount = 35.0, cumulative cost = {35.0 rows, 70.0 cpu, 0.0 io, 0.0 network}, id = 39732 DrillAggregateRel(subset=[rel#40515:Subset#143.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40514 DrillProjectRel(subset=[rel#40513:Subset#142.LOGICAL.ANY([]).[]], $f0=[0]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40512 DrillFilterRel(subset=[rel#40511:Subset#141.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 1), <=($1, 20))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40510 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40522:Subset#146.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40521 DrillProjectRel(subset=[rel#40520:Subset#145.LOGICAL.ANY([]).[]], ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40519 DrillFilterRel(subset=[rel#40511:Subset#141.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 1), <=($1, 20))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40510 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40529:Subset#149.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40528 DrillProjectRel(subset=[rel#40527:Subset#148.LOGICAL.ANY([]).[]], ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40526 DrillFilterRel(subset=[rel#40511:Subset#141.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 1), <=($1, 20))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40510 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40537:Subset#153.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40536 DrillProjectRel(subset=[rel#40535:Subset#152.LOGICAL.ANY([]).[]], $f0=[0]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40534 DrillFilterRel(subset=[rel#40533:Subset#151.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 21), <=($1, 40))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40532 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40544:Subset#156.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40543 DrillProjectRel(subset=[rel#40542:Subset#155.LOGICAL.ANY([]).[]], ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40541 DrillFilterRel(subset=[rel#40533:Subset#151.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 21), <=($1, 40))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40532 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40551:Subset#159.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40550 DrillProjectRel(subset=[rel#40549:Subset#158.LOGICAL.ANY([]).[]], ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40548 DrillFilterRel(subset=[rel#40533:Subset#151.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 21), <=($1, 40))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40532 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40559:Subset#163.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40558 DrillProjectRel(subset=[rel#40557:Subset#162.LOGICAL.ANY([]).[]], $f0=[0]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40556 DrillFilterRel(subset=[rel#40555:Subset#161.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 41), <=($1, 60))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40554 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40566:Subset#166.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40565 DrillProjectRel(subset=[rel#40564:Subset#165.LOGICAL.ANY([]).[]], ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40563 DrillFilterRel(subset=[rel#40555:Subset#161.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 41), <=($1, 60))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40554 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40573:Subset#169.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40572 DrillProjectRel(subset=[rel#40571:Subset#168.LOGICAL.ANY([]).[]], ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40570 DrillFilterRel(subset=[rel#40555:Subset#161.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 41), <=($1, 60))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40554 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40581:Subset#173.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40580 DrillProjectRel(subset=[rel#40579:Subset#172.LOGICAL.ANY([]).[]], $f0=[0]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40578 DrillFilterRel(subset=[rel#40577:Subset#171.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 61), <=($1, 80))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40576 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40588:Subset#176.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40587 DrillProjectRel(subset=[rel#40586:Subset#175.LOGICAL.ANY([]).[]], ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40585 DrillFilterRel(subset=[rel#40577:Subset#171.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 61), <=($1, 80))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40576 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40595:Subset#179.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40594 DrillProjectRel(subset=[rel#40593:Subset#178.LOGICAL.ANY([]).[]], ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40592 DrillFilterRel(subset=[rel#40577:Subset#171.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 61), <=($1, 80))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40576 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40603:Subset#183.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40602 DrillProjectRel(subset=[rel#40601:Subset#182.LOGICAL.ANY([]).[]], $f0=[0]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40600 DrillFilterRel(subset=[rel#40599:Subset#181.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 81), <=($1, 100))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40598 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40610:Subset#186.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40609 DrillProjectRel(subset=[rel#40608:Subset#185.LOGICAL.ANY([]).[]], ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40607 DrillFilterRel(subset=[rel#40599:Subset#181.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 81), <=($1, 100))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40598 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 DrillAggregateRel(subset=[rel#40617:Subset#189.LOGICAL.ANY([]).[]], group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40616 DrillProjectRel(subset=[rel#40615:Subset#188.LOGICAL.ANY([]).[]], ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40614 DrillFilterRel(subset=[rel#40599:Subset#181.LOGICAL.ANY([]).[]], condition=[AND(>=($1, 81), <=($1, 100))]): rowcount = 720101.0, cumulative cost = {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40598 DrillScanRel(subset=[rel#40509:Subset#140.LOGICAL.ANY([]).[]], table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 38829 " {code} > tpcds queries 6, 8 and 9 fail to plan > ------------------------------------- > > Key: DRILL-867 > URL: https://issues.apache.org/jira/browse/DRILL-867 > Project: Apache Drill > Issue Type: Bug > Components: SQL Parser > Reporter: Krystal > > git.commit.id.abbrev=e1e5ea0 > git.commit.time=29.05.2014 @ 15\:32\:29 PDT > query 6: > {code} > select * from (select a.ca_state state, count(*) cnt > from customer_address a > ,customer c > ,store_sales s > ,date_dim d > ,item i > where a.ca_address_sk = c.c_current_addr_sk > and c.c_customer_sk = s.ss_customer_sk > and s.ss_sold_date_sk = d.d_date_sk > and s.ss_item_sk = i.i_item_sk > and d.d_month_seq = > (select distinct (d.d_month_seq) > from date_dim d > where d.d_year = 1998 > and d.d_moy = 5 ) > and i.i_current_price > 1.2 * > (select avg(j.i_current_price) > from item j > where j.i_category = i.i_category) > group by a.ca_state > having count(*) >= 10 > order by cnt > ) limit 100; > {code} > query 7: -- This message was sent by Atlassian JIRA (v6.2#6252)