[teiid-issues] [JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query

[Don Krapohl (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13186137#comment-13186137 ] 

Don Krapohl edited comment on TEIID-4113 at 4/4/16 3:01 PM:
------------------------------------------------------------

Thanks for the code.  Here's my un-obfuscated version with my scenario:

{{    public void testExecImpalaTimeStampWithAgg() throws Exception {
        ImpalaExecutionFactory ief = new ImpalaExecutionFactory();
        ief.setDatabaseVersion("2.0");
        ief.start();

        DefaultCapabilitiesFinder finder = new DefaultCapabilitiesFinder(CapabilitiesConverter.convertCapabilities(ief));
        TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign table faatd (num_items_net long, " +
                "pub_comm_base bigdecimal, orderid string(255), trans_date_key String(255), advertiser_key long); " +
                "create foreign table dim_date (trans_date_key string(255), trans_year string(4));"
                + " create view AATD (advertiser_key long, trans_date_key string(255), trans_year string(4), " +
                "num_items_net long, pub_comm_base bigdecimal, orderid string(255)) " +
                "as (select advertiser_key, f.trans_date_key, trans_year, num_items_net, pub_comm_base, " +
                "orderid from faatd f inner join dim_date d on f.trans_date_key=d.trans_date_key)", "x", "y");
        HardcodedDataManager dataMgr = new HardcodedDataManager();
        dataMgr.setMustRegisterCommands(false);
        List<?>[] expected = new List<?>[] {  };

        dataMgr.addData("SELECT g_0.trans_date_key, convert((SUM(g_0.num_items_net) / " +
                "convert(COUNT(DISTINCT CASE WHEN g_0.pub_comm_base >= 0 THEN g_0.orderid END), long)), double), " +
                "g_0.orderid FROM y.faatd AS g_0 WHERE g_0.advertiser_key = 111 " +
                "GROUP BY g_0.trans_date_key, g_0.orderid", //$NON-NLS-1$
                expected);

        String query="SELECT faatd.trans_date_key, cast( sum( num_items_net ) / " +
                "count( distinct case when pub_comm_base >= 0 then pub_comm_base end ) as double ) as some_alias, " +
                "PARSETIMESTAMP(orderid, 'yyyy-MM-dd') as somedate " +
                " FROM faatd WHERE advertiser_key=111 GROUP BY faatd.trans_date_key, orderid";
         doProcess(tm, query, finder, dataMgr, expected, DEBUG);
    }}}


was (Author: don.krapohl):
Thanks for the code.  Here's my un-obfuscated version with my scenario:

    public void testExecImpalaTimeStampWithAgg() throws Exception {
        ImpalaExecutionFactory ief = new ImpalaExecutionFactory();
        ief.setDatabaseVersion("2.0");
        ief.start();

        DefaultCapabilitiesFinder finder = new DefaultCapabilitiesFinder(CapabilitiesConverter.convertCapabilities(ief));
        TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign table faatd (num_items_net long, " +
                "pub_comm_base bigdecimal, orderid string(255), trans_date_key String(255), advertiser_key long); " +
                "create foreign table dim_date (trans_date_key string(255), trans_year string(4));"
                + " create view AATD (advertiser_key long, trans_date_key string(255), trans_year string(4), " +
                "num_items_net long, pub_comm_base bigdecimal, orderid string(255)) " +
                "as (select advertiser_key, f.trans_date_key, trans_year, num_items_net, pub_comm_base, " +
                "orderid from faatd f inner join dim_date d on f.trans_date_key=d.trans_date_key)", "x", "y");
        HardcodedDataManager dataMgr = new HardcodedDataManager();
        dataMgr.setMustRegisterCommands(false);
        List<?>[] expected = new List<?>[] {  };

        dataMgr.addData("SELECT g_0.trans_date_key, convert((SUM(g_0.num_items_net) / " +
                "convert(COUNT(DISTINCT CASE WHEN g_0.pub_comm_base >= 0 THEN g_0.orderid END), long)), double), " +
                "g_0.orderid FROM y.faatd AS g_0 WHERE g_0.advertiser_key = 111 " +
                "GROUP BY g_0.trans_date_key, g_0.orderid", //$NON-NLS-1$
                expected);

        String query="SELECT faatd.trans_date_key, cast( sum( num_items_net ) / " +
                "count( distinct case when pub_comm_base >= 0 then pub_comm_base end ) as double ) as some_alias, " +
                "PARSETIMESTAMP(orderid, 'yyyy-MM-dd') as somedate " +
                " FROM faatd WHERE advertiser_key=111 GROUP BY faatd.trans_date_key, orderid";
         doProcess(tm, query, finder, dataMgr, expected, DEBUG);
    }

> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
>                 Key: TEIID-4113
>                 URL: https://issues.jboss.org/browse/TEIID-4113
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.13.3
>         Environment: Ubuntu Trusty
>            Reporter: Don Krapohl
>            Assignee: Steven Hawkins
>              Labels: Impala_Translator
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly.  Example is  as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //  Teiid query
> SELECT the_attribute,
> cast(
>   sum(
>     first_metric
>   ) 
>   / 
>   count(
>     distinct case when  second_metric >= 0 then second_metric end
>   ) as double
> )  as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable 
> WHERE a_filter_val=111 
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0, 
> cast(
>   (
>     anon_grp0.agg0 
>     / 
>     cast(
>     anon_grp0.agg1 AS bigint
>     )
>   ) AS double
> ) AS c_1, 
> g_0.another_attribute AS c_2 
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.another_attribute



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)