From issues-return-26355-apmail-kylin-issues-archive=kylin.apache.org@kylin.apache.org Fri Apr 26 09:34:03 2019 Return-Path: X-Original-To: apmail-kylin-issues-archive@minotaur.apache.org Delivered-To: apmail-kylin-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by minotaur.apache.org (Postfix) with SMTP id AA794181BC for ; Fri, 26 Apr 2019 09:34:03 +0000 (UTC) Received: (qmail 70632 invoked by uid 500); 26 Apr 2019 09:34:02 -0000 Delivered-To: apmail-kylin-issues-archive@kylin.apache.org Received: (qmail 70579 invoked by uid 500); 26 Apr 2019 09:34:01 -0000 Mailing-List: contact issues-help@kylin.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@kylin.apache.org Delivered-To: mailing list issues@kylin.apache.org Received: (qmail 70447 invoked by uid 99); 26 Apr 2019 09:34:01 -0000 Received: from mailrelay1-us-west.apache.org (HELO mailrelay1-us-west.apache.org) (209.188.14.139) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 26 Apr 2019 09:34:01 +0000 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 C3E6DE2B30 for ; Fri, 26 Apr 2019 09:34: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 3299725815 for ; Fri, 26 Apr 2019 09:34:00 +0000 (UTC) Date: Fri, 26 Apr 2019 09:34:00 +0000 (UTC) From: "hejian (JIRA)" To: issues@kylin.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Comment Edited] (KYLIN-3967) sum along with case expression does not work in query 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/KYLIN-3967?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1682= 6803#comment-16826803 ]=20 hejian edited comment on KYLIN-3967 at 4/26/19 9:33 AM: -------------------------------------------------------- {code:java} sum(case when derived_col=3D'1' then measure_col else 0 end){code} *derived_col*=C2=A0 is from lookup table and=C2=A0*measure_col* is a=C2=A0p= redefine=C2=A0measure. I think this case should be supported, because case expression is base on f= urther aggregations, and the way that derived column works as well? was (Author: hejian999): {code:java} sum(case when derived_col=3D'1' then measure_col else 0 end){code} *derived_col*=C2=A0 is from lookup table and=C2=A0*measure_col* is=C2=A0**= =C2=A0a=C2=A0**=C2=A0predefine=C2=A0**=C2=A0measure. I think this case should be supported, because case expression is base on f= urther aggregations, and the way that derived column works as well? > sum along with case expression does not work in query > ----------------------------------------------------- > > Key: KYLIN-3967 > URL: https://issues.apache.org/jira/browse/KYLIN-3967 > Project: Kylin > Issue Type: Bug > Components: Query Engine > Reporter: Gladson Vas > Priority: Blocker > Attachments: notworkingcase.jpg, workingcase.jpg > > > When i try to run a query with a sum case expression combination, > eg: select sum(case when col1<0 then 0 else col1 end ) from table=C2=A0 > i get the following error: > No realization found for OLAPContext, CUBE_UNMATCHED_AGGREGATION[Function= Desc [expression=3DSUM, parameter=3DCASE(<($8, 0), 0, $8), returnType=3Dnul= l]], rel#36838:OLAPTableScan.OLAP.[](table=3D[DEFAULT, table],ctx=3D,fields= =3D[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2= 0, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, = 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57,= 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76= , 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 9= 5, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106]) while executing SQL:= "select sum (case when=C2=A0col1 <0 then 0 else=C2=A0col1 end ) from=C2=A0= table LIMIT 50000" > =C2=A0 > is there any way to support this sum case expression in the query engine? > Also I get the same error when the sum operation is done on a column deri= ved from a case expression in a subquery. > eg: select sum(a.col1) from (select case when col1<0 then 0 else col1 end= as col1 from table) a > Thanks, > Gladson > =C2=A0 -- This message was sent by Atlassian JIRA (v7.6.3#76005)