Cannot create AliasSymbol wrapping AliasSymbol

[jchidiac]

when i execute the following query, i get this error :

            org.teiid.jdbc.TeiidSQLException: Cannot create AliasSymbol wrapping AliasSymbol

It's related to the Field Expression with 2 fields having the same name

Query :

SELECT

      CASE

        WHEN NVL(Request.IMPACT_ID, 0)   = 2 THEN 'Critical'

        WHEN NVL(Request.IMPACT_ID, 0)   = 6 THEN 'Planning'

        WHEN NVL(Request.IMPACT_ID, 0)   = 3 THEN 'High'

        WHEN NVL(Request.IMPACT_ID, 0)   = 5 THEN 'Low'

        ELSE NULL

      END AS Request_Attribute461,

       Groups."TITLE" AS Groups_TITLE,

       Request_Catalog."TITLE" AS Request_Catalog_TITLE,

       COUNT(Request."REQUEST_ID") AS Request_REQUEST_ID1

  FROM ServiceDesk.Request Request

       LEFT OUTER JOIN ServiceDesk.Groups Groups ON Request.OWNING_GROUP_ID = Groups.GROUP_ID

       LEFT OUTER JOIN ServiceDesk.Request_Catalog Request_Catalog ON Request.REQUEST_CATALOG_ID = Request_Catalog.REQUEST_CATALOG_ID

GROUP BY

  CASE

    WHEN NVL(Request.IMPACT_ID, 0)   = 2 THEN 'Critical'

    WHEN NVL(Request.IMPACT_ID, 0)   = 6 THEN 'Planning'

    WHEN NVL(Request.IMPACT_ID, 0)   = 3 THEN 'High'

    WHEN NVL(Request.IMPACT_ID, 0)   = 5 THEN 'Low'

    ELSE NULL

  END,

  Groups."TITLE",

  Request_Catalog."TITLE"

ORDER BY

  CASE

    WHEN NVL(Request.IMPACT_ID, 0)   = 2 THEN 'Critical'

    WHEN NVL(Request.IMPACT_ID, 0)   = 6 THEN 'Planning'

    WHEN NVL(Request.IMPACT_ID, 0)   = 3 THEN 'High'

    WHEN NVL(Request.IMPACT_ID, 0)   = 5 THEN 'Low'

    ELSE NULL

  END ASC,

  Groups."TITLE" ASC,

  Request_Catalog."TITLE" DESC

any idea how to resolve it ?

[shawkins]

It would help to add more context - What version are you on?  Can you provide a reproducing vdb/ddl?

Steve

[jchidiac]

Teiid 8.5 and JDBC Driver 8.5

my vdb is added  pragmatically via admin API

[shawkins]

Can you provide representative DDL or the VDB itself?  If not that's ok, it will just take me a little longer to reproduce what you're seeing - but if there are views, strange translator capabilities, etc. involved then I may not easily guess at what is happening.

Steve

[jchidiac]

Its simple to reproduce, no views, standard mysql5 translator

Hint : the error :

ERROR [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue6859) caU8stCGl+8r TEIID30019 Unexpected exception for request caU8stCGl+8r.0: java.lang.AssertionError: Cannot create AliasSymbol wrapping AliasSymbol

  at org.teiid.core.util.Assertion.failed(Assertion.java:73) [teiid-common-core-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.symbol.AliasSymbol.setSymbol(AliasSymbol.java:76) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.visitor.ExpressionMappingVisitor.visit(ExpressionMappingVisitor.java:159) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.symbol.AliasSymbol.acceptVisitor(AliasSymbol.java:90) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:54) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.preVisitVisitor(PreOrPostOrderNavigator.java:54) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:77) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.symbol.AliasSymbol.acceptVisitor(AliasSymbol.java:90) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:297) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.lang.OrderByItem.acceptVisitor(OrderByItem.java:87) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.AbstractNavigator.visitNodes(AbstractNavigator.java:76) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:291) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.lang.OrderBy.acceptVisitor(OrderBy.java:136) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.doVisit(PreOrPostOrderNavigator.java:669) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.sql.visitor.ExpressionMappingVisitor.mapExpressions(ExpressionMappingVisitor.java:394) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.rewriteGroupByAsView(RuleCollapseSource.java:741) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:266) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.execute(RuleCollapseSource.java:95) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:606) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:255) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:409) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.5.0.Final.jar:8.5.0.Final]

  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0]

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0]

  at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0]

[shawkins]

Ok, this is enough to reproduce.  I'll log an issue and provide a fix.  As for a workaround it looks related to a specific set of capabilities, can you try just using the MySQL translator instead?

Steve

[jchidiac]

hello,

Thx for your answer.

It works with Mysql translator.

What's the difference between Mysql5 and mysql translator

and if i change the translator to MYSQL in my application, any risky in my application ?

[shawkins]

> What's the difference between Mysql5 and mysql translator

Mostly mysql5 adds capabilities, which are not available in older mysql instances.  In this case the problem is arising from mysql5 having support for inline views but not for complex expressions in a group by.  The compensation logic is throwing an exception in this case, which I'll address shortly.

> and if i change the translator to MYSQL in my application, any risky in my application ?

There's not much risk other than some queries may not be pushed down as fully as with mysql5.

[shawkins]

I should add that it looks like the mysql5 translator should be marked as supporting expressions in the group by, so you can do that as a workaround as well - create a translator that is an extension of the MySQL5ExecutionFactory with supportsFunctionsInGroupBy returning true.  That will also be in the resolution of this issue.

[shawkins]

[TEIID-2691] Grouping expression with view fails when grouping columns have the same name - JBoss Issue Tracker will be in 8.6 alpha2