6 Replies Latest reply on Aug 31, 2012 10:35 AM by markaddleman

    Correct behavior?  ORDER BY in inline view

    markaddleman

      My query is

      SELECT * FROM (SELECT * FROM demodata."user" WHERE sysid = 'sysid 0' ORDER BY userid DESC) AS CHORUS_B
      

       

      After optimization, it looks like the ORDER BY in the inline view is dropped.  I don't know if this is correct behavior or not but it is undesirable   Plan is attached

       

      This is against the latest Teiid 7.7.x build

        • 1. Re: Correct behavior?  ORDER BY in inline view
          rareddy

          This is expected by design. The order of the rows produced from a inline views does not really affect the projected rows on the top are generated. This also creates unnecessary sort operation and thus dropped.

           

          Ramesh..

          • 2. Re: Correct behavior?  ORDER BY in inline view
            markaddleman

            Alright.  Our query generator uses the Teiid language objects and visitor facilities, so it's not just a big deal for us to "lift" the inner ORDER BY to the outter most query

            • 3. Re: Correct behavior?  ORDER BY in inline view
              shawkins

              We are like SQL Server in this regard, which is a strict interpretation of the idea that ordering is a cursor operation and your inline view is selecting from a derived table, which implies no ordering.  If you apply a limit that will leave the order by in place - but does not fully guarentee that the result will be sorted as you expect.

              • 4. Re: Correct behavior?  ORDER BY in inline view
                markaddleman

                That makes sense.  I was wondering under what theory makes this correct behavior.  I can see how the cursor theory of order applies.  What other clauses behave like this?  I can see how LIMIT might.

                 

                My bigger question is, would you entertain a jira to put tthe planner in a different mode? I would want the inline view to be considered a result set over which additional operations apply.  Put another way, I want full composibility over SELECT expressions.  Again, our application generates queries and will be making heavy use of inline views.  While we could teach our query generator to lift ORDER BY out of inner queries to the putter most one, I'm concerned that other clauses are more difficult to lift (like LIMIT)

                • 5. Re: Correct behavior?  ORDER BY in inline view
                  shawkins

                  > What other clauses behave like this?  I can see how LIMIT might.

                   

                  When taken together we treat ORDER BY ... LIMIT as an operation that must be performed in place.  Otherwise if we are in an inline view then just ORDER BY can be discarded and LIMIT will be enforced - but the rows you get back are not guarenteed.  LIMIT can also take a NON_STRICT hint https://docs.jboss.org/author/display/TEIID/LIMIT+Clause which mimics a planning behavior that people want sometimes, which is give me at most x rows and push the LIMIT as far a possible.

                   

                  A hint or other mode to force ORDER BY would effectively be the same as ORDER BY ... LIMIT 214748367.  So there's negligible value in a hint, but you could log a JIRA to have the planner optionally implicitly add the LIMIT if a view has an ORDER BY.

                  • 6. Re: Correct behavior?  ORDER BY in inline view
                    markaddleman

                    Ah, that makes a great deal of sense.  We can easily generate a LIMIT clause in our query generator and, like you say, we'll get the behavior we want.  No need for a jira.  Thanks