7 Replies Latest reply on Jan 4, 2011 9:27 PM by shawkins

    StackOverflowError for a simple Select * query over a simple view and SQL Server

    ventuc
    ventuc Jan 4, 2011 10:40 AM

      Hi,

      I'm new to Teiid but I think I've found a problem in the query processor of Teiid.

       

      I'm testing Teiid 7.2 (final) on JBoss AS 5.1. I have created a simple VDB with 2 models, in teiid designer 7.1. The first (Northwind.xmi) is a source model which connects to SQL Server 2000 trough JDBC (Jtds 1.2.5), and imports all tables from the Notrhwind database (the sample database in SQL server 2000). The other model (logical_northwind.xmi) is a simple view model which includes all the objects of the source model. It doesn't apply any transformation to the data from the source model. The VDB includes these two models, but only the view model is visible.

       

      I've found that a simple query over the view, like SELECT * FROM "logical_northwind"."Northwind.dbo.Categories" fails with a StackOverflowError. And this is the same with all the tables in the view. After some trials, I renamed the table in the view model from "Categories" to "Cat", and now the query works. So it seems that the query fails if the view model uses the same table name used by the source model. Can it be a bug in the query processor, or am I doing something wrong?

       

      In the attachments there are the log files:

      - command.log: contains the messages of the COMMAND_LOG, in case of a query which fails (it is on the Employees tables, but is the same with all the tables)

      - command_ok.log:  contains the messages of the COMMAND_LOG, in case of a query which works (it is on the renamed table)

      - server.log: the general log which includes the StackOverflowError. It includes several queries, but the only one which works is at the top of the file. All the following failed.

       

      By reading the log files, the problem seems to be something like an infinite loop in the query optimizer. Or maybe is this issue related to https://issues.jboss.org/browse/TEIID-912 ??

       

      If there are workarounds, please let me know. By now, the only thing I can do is to rename all the tables in the view...

       

      Thanks in advance,

      Claudio

        • 1. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
          ventuc
          ventuc Jan 4, 2011 10:45 AM (in response to ventuc)

          I attach also the two models and the VDB. I have done all with designer 7.1 on eclipse 3.6.1

           

          Thanks!

            • Actions
          • 2. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
            shawkins
            shawkins Jan 4, 2011 11:45 AM (in response to ventuc)

            Claudio,

             

            I'll look into this.  It is definately a bug as we never expect a stack overflow.

             

            Steve

              • Actions
            • 3. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
              ventuc
              ventuc Jan 4, 2011 1:39 PM (in response to shawkins)

              Steven Hawkins ha scritto:

               

              Claudio,

               

              I'll look into this.  It is definately a bug as we never expect a stack overflow.

               

              Steve

               

              Thanks Steven!

               

              For the sake of simplicity, I've limited the source model to only one table, the table "Categories". After some attempts I've found that the query works fine not only if the table in the view has a different name with respect to that of the source. It is sufficient that the schema or the catalog has a different name. In particular the query works by changing the name of the catalog/schema/table either in the source model or in the view model. To change the name of an object in the source model is sufficient to change the "Name" property, and leave the "Name In Source" property as it is.

               

              So now there are more workarounds.. the problem is that changing the name of the schema/catalog force you to manually update all the transformations from the source model to the view model (perhaps is there a more convenient way to do that in Teiid Designer?).

               

              Lastly, I tried other ways for getting it to work, and I discovered that the following actions don't improve the situation:

              • simply changing the names of the tables/schema/catalog from uppercase to lowercase (or viceversa). If the table in the view is uppercase, and that in the source is lowercase, the query will continue to not work
              • adding some simple transformations to the columns of the tables. For example applying a UCASE() to a string column in the transformation of the Categories table, but nothing changes.
              • the presence of LOB fields doesn't interfere with the query (I thought that maybe the optimizer did something wrong while trying some optimization to better manage these type of fields). Removing them from the tables doesn't resolve the problem.

               

              I hope this can be useful... thanks!

               

              Claudio

                • Actions
              • 4. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
                rareddy
                rareddy Jan 4, 2011 1:58 PM (in response to ventuc)

                Claudio,

                 

                Typically Teiid errors with "ambiguous" tables name if the table name supplied is not fully qualified and VDB has more than one table with the same name, where name = catalog.schema.tablename. Names are not case sensitive, and "Name in Source" represent the name with which it is called against the source, where as simple 'name' is logical name. If in case "name in source" is missing then logical name is used as the name for query execution.

                 

                I see in your original query that you used SELECT * FROM "logical_northwind"."Northwind.dbo.Categories", where you did use fully qualified name, the question is why that did not resolve correctly, certainly the stack overflow is an issue.

                 

                Ramesh..

                  • Actions
                • 5. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
                  shawkins
                  shawkins Jan 4, 2011 2:10 PM (in response to rareddy)

                  https://issues.jboss.org/browse/TEIID-1418 covers this issue.  You turned out to have the right combination of metadata and load order to trigger the stackoverflow.  The workaround is to have unique suffixes for your model names - e.g. Northwind and Northwind_logical.

                   

                  The fix will be in 7.3 shortly.

                   

                  Steve

                  1 of 1 people found this helpful
                    • Actions
                  • 6. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
                    ventuc
                    ventuc Jan 4, 2011 5:21 PM (in response to shawkins)

                    Thanks, but I think there's something more...

                     

                    I have built a VDB identical to the one before, but now the models are named "p_Northwind.xmi" (the source model), and "l_Northwind.xmi" (the view model). The query SELECT * FROM "l_Northwind"."Northwind.dbo.Categories" works perfectly, and as you can note, the suffix of the two models is the same. Also the names of all the objects in the view model are the same of that in the source model.

                     

                    I've tried also to create a new source model which imports the fully qualified names for the "Name in Source" properties, but it doesn't help.

                     

                    Anyway, for now I will use the workaround suggested by Steven.

                     

                    thanks,

                    Claudio

                      • Actions
                    • 7. Re: StackOverflowError for a simple Select * query over a simple view and SQL Server
                      shawkins
                      shawkins Jan 4, 2011 9:27 PM (in response to ventuc)

                      Claudio,

                       

                      You are not guarenteed to have a problem even if the suffixes are the same.  That is what I meant by your vdb having the right load order of index files to cause the issue.  Changing the model names, even with the same suffix, probably made it so that the physical loaded last.  In any case having unique suffixes is sufficient to prevent the issue from happening.

                       

                      Steve

                        • Actions