Quoted column name bug?

[bluesgtrst]

I had a problem with this on Teiid 7.3 that I was hoping 7.6 would resolve.

But unfortunately it did not Anyone have a suggested workaround or quick fix ?

Teiid query:

select "__objecttype__" from SECCM.CMCOMMONVIEW LIMIT 10

Error Code:0 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:SECCM: Error Code:0 Message:'Lexical error: Lexical error at line 1, column 12.  Encountered: "_" (95), after : ""' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.__objecttype__ AS c_0 FROM (   select "__objecttype__", evttypecode, evtversion , evtcatcode , evtsysid , evtsysplex , evtutctod , evtsystod ,

Note that teiid view is defined with column as “__objecttype__” .

Ex.

<models>

    <model name="SECCM">

     <view name="CMCOMMONVIEW">

            <definition>

            select datamart_name, "__objecttype__", evttypecode, evtversion , evtcatcode , evtsysid , evtsysplex , evtutctod , evtsystod ,

            evtesmcode , evtpuuid , evtuserid , evtusername, evtjobname , evtsource , admtarget , Commandstr, oprcode  ,

            vio1code , vio2code , evttypedesc , evtcatdesc

            FROM (

                #foreach($t in $db.query("select DATAMART_NAME, MODEL_NAME from security_db.config WHERE TYPE = 'DM'"))

                    select u.datamart_name, u."__objecttype__", u.evttypecode, u.evtversion , u.evtcatcode , u.evtsysid , u.evtsysplex , u.evtutctod , u.evtsystod ,

                    u.evtesmcode , u.evtpuuid , u.evtuserid , u.evtusername, u.evtjobname , u.evtsource , u.admtarget , u.Commandstr, u.oprcode  ,

                    u.vio1code , u.vio2code , tp.evttypedesc , ct.evtcatdesc

                    FROM (

                    select '${t.DATAMART_NAME}' as DATAMART_NAME, 'SECCM.VIEWADMACCOUNT' as "__objecttype__", a.evttypecode , a.evtversion , a.evtcatcode , a.evtsysid ,

                    a.evtsysplex , a.evtutctod , a.evtsystod , a.evtesmcode , a.evtpuuid , a.evtuserid , a.evtusername, a.evtjobname ,

                    a.evtsource , a.admtarget , a.admacctstr as Commandstr, a.oprcode , a.vio1code , a.vio2code

                    from ${t.MODEL_NAME}.ADMACCOUNT a

Please see attached for showplan output.

Thanks,

Jeff

          ...

[shawkins]

Hi Jeff,

The query should have SELECT g_0."__objecttype__".  I think what is happening is that you must not have supportsSelectExpressions, so the planner is not using aliases.  If you can support select expressions, that would be a good fix/workaround.  Otherwise the full fix will need a patch.

Steve

[shawkins]

Just to follow-up, I logged and work https://issues.jboss.org/browse/TEIID-1929 in 7.7 so that when inline view support is enabled we'll always use generated column/table aliases.

Steve

[bluesgtrst]

Hi Steve,

Thanks, can you tell me where the "supportsSelectExpressions" property is defined. Couldn't find it in teiid doc.

[rareddy]

Considering you are working with a custom translator, it is defined on "ExecutionFactory" class. You can override in your implementation of this method.

[bluesgtrst]

Thanks yes we found it and it is already set.

So we will need to get the patch as soon as it is available.

Please advise.

[shawkins]

If it were set, I would expect the source query to already have column aliases - SELECT g_0.c_0 FROM (SELECT "__objecttype__" as c_0 ...

Since the debug entries looked imcomplete, I was assuming that this was a scenario where you had inline view support enabled, but not select expression (that would be the simpliest case).  Then issued a user query containing an inline view with valid aliases that were stripped out since supportSelectExpressions was not set.  However that must not be your scenario if supportSelectExpressions is already true. 

Can you provide a complete debug log - something that shows the relevant access node source query and/or a better description of who is generating the offending SQL - such as if your translator has custom SQL generation logic.

Steve

[markaddleman]

Hi Steve -

I thought we provided the whole plan but we'll try to regenerate it and post again. 

While this is a custom translator, it subclasses directly from JDBCExecutionFactory and supports everything except for row off.  We have a subclass of SQLConversionVisitor but it is limited to altering the LIMIT statement and JOIN syntax.  It doesn't touch anything in the column list.

I'm not sure what additional debug logs would be valuable here.  Simply turning on Teiid debug logging?

[shawkins]

In the attached log I see an access node with the source query 'SELECT g_0."__objecttype__" AS c_0 FROM SECCM.CMCOMMONVIEW AS g_0 LIMIT 10', then the exception with a query that has an inlineview, but does not have the __objecttype__ column properly aliased.  There's nothing in between.  Anything that shows what formed that invalid query would be useful.

Steve