Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-2194

order by should not require all PK fields with = constraint

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.5.0
    • Fix Version/s: 4.6.0, 4.5.2
    • Component/s: None
    • Labels:
    • Environment:

      linux

      Description

      Here is a table:
      CREATE TABLE IF NOT EXISTS FEEDS.STUFF
      (
      STUFF CHAR(15) NOT NULL,
      NONSENSE CHAR(15) NOT NULL
      CONSTRAINT PK PRIMARY KEY
      (
      STUFF,
      NONSENSE

      )
      ) VERSIONS=1,MULTI_TENANT=TRUE,REPLICATION_SCOPE=1

      Here is a query:
      explain SELECT * FROM feeds.stuff
      where stuff = ' '
      and nonsense > ' '
      order by nonsense

      Here is the plan:
      CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN
      SERVER FILTER BY FIRST KEY ONLY
      SERVER TOP 100 ROWS SORTED BY [NONSE
      CLIENT MERGE SORT

      If I change to ORDER BY STUFF, NONSENSE I get:
      CLIENT 1-CHUNK SERIAL 1-WAY RANGE SCAN O
      SERVER FILTER BY FIRST KEY ONLY AND
      SERVER 100 ROW LIMIT
      CLIENT 100 ROW LIMIT

      Since the leading constraint is =, ORDER BY will be unaffected by it, so ORDER BY should not need the leading constraint; it should only require the columns whose values would vary (which, since they are ordered by the key, should (and do) result in the client side sort being optimized out.) Having to include the leading = constraints in the ORDER BY clause is very counter-intuitive.

        Attachments

        1. PHOENIX-2194-tests2.patch
          16 kB
          Jan Fernando
        2. PHOENIX-2194-tests.patch
          9 kB
          Jan Fernando
        3. PHOENIX-2194.patch
          17 kB
          James R. Taylor
        4. PHOENIX-2194_v6.patch
          56 kB
          James R. Taylor
        5. PHOENIX-2194_v5.patch
          53 kB
          James R. Taylor
        6. PHOENIX-2194_v4.patch
          31 kB
          James R. Taylor
        7. PHOENIX-2194_v3.patch
          26 kB
          James R. Taylor
        8. PHOENIX-2194_v2.patch
          20 kB
          James R. Taylor
        9. PHOENIX-2194_master.patch
          84 kB
          James R. Taylor
        10. PHOENIX-2194_master.patch
          83 kB
          James R. Taylor

          Issue Links

            Activity

              People

              • Assignee:
                jamestaylor James R. Taylor
                Reporter:
                ghoren@salesforce.com Gary Horen
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: