11 Replies Latest reply on Apr 20, 2012 12:34 PM by gamvi01

    Warnings are lost during insertion to temp tables.

    gamvi01

      Hi

       

      We are using materialized results sets. statement.getWarnings are null when we do a select from that temp table.

       

      Here is the detailed usecase :

       

      1) User does a select on particular object (here in our case - query was raised agains a translator which adds warnings to executionCntext using http://docs.jboss.org/teiid/6.2/apidocs/org/teiid/connector/api/ExecutionContext.html#addWarning%28java.lang.Exception%29)

      2) We then make inserts to temp table with the query passed as below

       

                insert into #temptable select * from actualTable

      3) To return the data we do  select * from #tempTable.

       

      During the above process , warnings are lost.

       

       

       

       

      seelct * from actuaTable  - statements.getWarnings are returned . Here actual tabel is the table created in the translator    

       

      select * from tempTable = statements.getwarnings are null.    Temptable is created as mentioned above.

       

       

      How can we preserve the warnings ? It should be handled at Teiid ?

        • 1. Re: Warnings are lost during insertion to temp tables.
          shawkins

          Vineela,

           

          Warnings are associated with a particular ExecutionContext/Request - not with the data.  Once the temp table is created it is an independent representation.  Are you looking to establish some form of data provenance?  Perhaps there are additional columns that should be associated with the data? 

           

          Steve

          • 2. Re: Warnings are lost during insertion to temp tables.
            gamvi01

            Yes warnings are associated with an execution context .why do temp tables have different execution context ? My expectation is when the data is inserted into the temp table with the query that adds warnings to the execution context , i expect to retrieve those warnings when i do a select from that temp table.

             

             

            There arent any additional columns that are associated with the data. Warnings are added to execution context and those warnings are retrieved from statement.

            • 3. Re: Warnings are lost during insertion to temp tables.
              shawkins

              Vineela,

               

              Every source query has a different ExecutionContext with a shared CommandContext from the user query.  There is no expecation that warnings from one ExecutionContext would be assoicated later with a temp table accessed via a different query (which would also be a different CommandContext).  Your expecation only makes sense in the usecase of using warnings as some form of additional metadata or data provenance, but that is not something that is the intent of warnings or a built-in feature.

               

              Steve

              • 4. Re: Warnings are lost during insertion to temp tables.
                markaddleman

                Hi, Steve -

                So statement "INSERT INTO #t SELECT * FROM t" has two seperate execution contexts?  One for the SELECT and the other for the INSERT?  If so, what do you think about collecting all fo the SQL warnings from execution contexts into the command context?

                 

                A bit more on our use case:  We're using SQL warnings as a side-band channel of information about result sets from custom translators.  For example, if a user requests too much data, the custom translator (or data source) may decide to cut off the results at some hardcoded limit.  We use SQL warnings as the channel to deliver the message from the custom translator to the user.

                 

                Another option:  Our code could issue three sets of statements: 

                1. CREATE TABLE #t
                2. SELECT * FROM t
                3. INSERT INTO #t

                We could capture the SQL warnings from #2 and report them to the user after completing the insert operation.  This approach is less convenient because we have to explicitly issue the CREATE TABLE and handle all of the column types.

                 

                Do you think this approach would work:

                1. INSERT INTO #t SELECT * FROM t LIMIT 0
                2. SELET * FROM t
                3. INSERT INTO #t

                The first step would conveniently create the temp table for us and then we could separately fill it with data while capturing the SQL warnings.  Thoughts?

                • 5. Re: Warnings are lost during insertion to temp tables.
                  shawkins

                  If executed as a user query, "INSERT INTO #t SELECT * FROM t" will have 1 CommandContext and 1 ExecutionContext per translator execution.  No ExecutionContext is created or needed for the tempory table access.  Warnings are collected from the ExecutionContexts into the request (not explicitly into the CommandContext) and are returned with the results.

                   

                  Option 2 above works but of course adds overhead.  You'd want to use PreparedStatement batching to perform the insert.  However there may be something I'm missing here, since if you can capture the warnings from an earlier statement, the you should just capture the warnings from issuing "INSERT INTO #t SELECT * FROM t" before issuing "SELECT * FROM #t"

                   

                  Steve

                  • 6. Re: Warnings are lost during insertion to temp tables.
                    markaddleman

                    We're double checking our test case. 

                    • 7. Re: Warnings are lost during insertion to temp tables.
                      gamvi01

                      Steve,

                       

                      we took the route of

                      1. INSERT INTO #t SELECT * FROM t LIMIT 0
                      2. SELET * FROM t

                       

                      If warnings are not returned from 1 then we are executing the query again and getting the warning messages. Do you know why we are not able to see the warnings for few queries ?

                      Executing a query twice is a performance hit , hence wanted to make sure we get the messages from statement 1 in all cases. Let me kow what details you need.

                       

                       

                      Thanks

                      Vineela

                      • 8. Re: Warnings are lost during insertion to temp tables.
                        shawkins

                        Vineela,

                         

                        Statement 1 will not generate any warnings as a source query will not be issued.  It simply has the side effect of creating a temporary table.

                        Statement 2 may return warnings, but again if you are simply pulling that data to the client and putting it into #t it probably makes more sense to combine this with statement 1.

                         

                        Steve

                        • 9. Re: Warnings are lost during insertion to temp tables.
                          gamvi01

                          Warnings i mean , i was referring to statement.getWarnings.

                           

                          In your previous reply you mentioned If executed as a user query, "INSERT INTO #t SELECT * FROM t" will have 1 CommandContext and 1 ExecutionContext per translator execution.  No ExecutionContext is created or needed for the tempory table access.  Warnings are collected from the ExecutionContexts into the request (not explicitly into the CommandContext) and are returned with the results.  This is happening for few queries but for few queries this is not the case , hence would like to know to get that fixed.

                           

                          will insert into #t(a,b,c) value(1,2,3) would work ?

                          • 10. Re: Warnings are lost during insertion to temp tables.
                            shawkins

                            Vineela,

                             

                            We must be talking past each other.  I'll try to be clearer.  My assumtion is that the access to t returns some warning via the ExecutionContext.  Thus a statement such as "INSERT INTO #t SELECT * FROM t LIMIT 0" or "insert into #t(a,b,c) value(1,2,3) would work" will not have any warnings associated with as no access is being made to t.  A statement such as "INSERT INTO #t SELECT * FROM t" or "SELECT * FROM t" can have warnings that accumulate and will be returned to the client.  In the case of the update statement all of the warnings will be delivered as part of the row count result.  For a simple SELECT warnings will be delivered to the client as each result batch is sent.  Only once all the results have been received can you be assured that the client has received all of the warnings.  Warnings are also not stored as part of cached results so if you are accessing a materialized view or a result set cache entry for your user query you will not receive any warnings that may have been associated with the original query.  Does that help clarify things?

                             

                            Steve

                            • 11. Re: Warnings are lost during insertion to temp tables.
                              gamvi01

                              Thanks Steve it clarifies things.

                               

                              We are doing "INSERT INTO #t SELECT * FROM t" or "SELECT * FROM t" .    "INSERT INTO #t SELECT * FROM t" is returning warnings in few scenarios and not returning warnings in few scenarios.