8 Replies Latest reply on Feb 8, 2012 11:16 AM by Steven Hawkins

    Quoted column name bug?

    Jeff Hayes Newbie

      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

                ...

        • 1. Re: Quoted column name bug?
          Steven Hawkins Master

          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

          • 2. Re: Quoted column name bug?
            Steven Hawkins Master

            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

            • 3. Re: Quoted column name bug?
              Jeff Hayes Newbie

              Hi Steve,

               

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

              • 4. Re: Quoted column name bug?
                Ramesh Reddy Master

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

                • 5. Re: Quoted column name bug?
                  Jeff Hayes Newbie

                  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.

                  • 6. Re: Quoted column name bug?
                    Steven Hawkins Master

                    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

                    • 7. Re: Quoted column name bug?
                      Mark Addleman Master

                      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?

                      • 8. Re: Quoted column name bug?
                        Steven Hawkins Master

                        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