Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-45957

is it possible parametrize ORDER BY field ASC|DESC NULLS FIRST|LAST

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 7.1.0
    • 7.1.0
    • query
    • 1

    Description

      https://forums.couchbase.com/t/n1ql-sort-by-via-place-holder/30285

      Place holders are not allowed in ORDER BY expression, direction, nulls placement.

      If one used ORDER BY $1 DESC. Place holders are constants. This becomes ORDER BY constant DESC. This NO OP because ORDER BY constant across results (duplicates).

      These are needed for Optimizer plan purpose, use index order.

      Indirectly we allow dynamic fields in ORDER BY fields (i.e. can be place holders). In that case no index order will be used, covering is not possible (i.e. all index pushdowns are off).

      SELECT *
      FROM default AS d
      WHERE …
      ORDER BY d.[$1] DESC NULLS LAST OFFSET $2 LIMIT $3;

      $1 must be string of field in the document, if not string it raises error.

      Is it possible DESC, ASC, NULLS LAST|FIRST (at least ASC, DESC) can be place holders. These can't use index order, Optimizer really doesn't depend on these values.

      FYI: How Dynamic fields works? ex : d.[d.b]

       After dot(.) array brackets starts it is dynamic field
               It evaluates expression with in the array brackets. It must be evaluated to string.
               Then string is consider as filed name and retrieve the field name value
                In the following example
                   first document           d.[d.b] ====> d.["a"] ===> d.`a`  ===> 10
                   second document     d.[d.b] ====> d.["c"] ===> d.`c`  ===> 20
       
      SELECT d.[d.b] AS val FROM [{"a":10, "b":"a", "c": 100}, {"a":100, "b":"c", "c":20}] AS d;
       "results": [
          {
              "val": 10
          },
          {
              "val": 20
          }
          ]
      

      Normally ORDER BY you need sort same fields across query results. you can use named/query parameter as dynamic field.

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          Build couchbase-server-7.1.0-1095 contains query commit 44dd531 with commit message:
          MB-45957 Order direction and null position parameterised.

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1095 contains query commit 44dd531 with commit message: MB-45957 Order direction and null position parameterised.

          Build couchbase-server-7.1.0-1095 contains query-ee commit 15c4481 with commit message:
          MB-45957 Order direction and null position parameterised.

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1095 contains query-ee commit 15c4481 with commit message: MB-45957 Order direction and null position parameterised.
          Donald.haggart Donald Haggart added a comment - - edited

          To verify prepare a statement with the order-by direction and/or nulls position parameterised and confirm correct changes when executing with different parameter values.

          For the order by direction, parameter values should be "asc" or "desc" (case insensitive).

          For the nulls position, parameter values should be "first" or "last" (case insensitive).

          e.g.

           

          insert into default (key,value) values("t1",{"a":1}),("t2",{"a":2}),("t3",{"a":null});
          prepare v from select * from default order by a $o nulls $n;
          execute v using {"o":"desc","n":"last"};
          execute v using {"o":"desc","n":"first"};
          execute v using {"o":"asc","n":"first"};
          execute v using {"o":"asc","n":"last"};
          

           

          If a parameter is invalid or absent, the query should still run but warnings will be produced; if missing, order will default to ASC and nulls position to FIRST with ASC order and LAST with DESC order.

          Donald.haggart Donald Haggart added a comment - - edited To verify prepare a statement with the order-by direction and/or nulls position parameterised and confirm correct changes when executing with different parameter values. For the order by direction, parameter values should be "asc" or "desc" (case insensitive). For the nulls position, parameter values should be "first" or "last" (case insensitive). e.g.   insert into default (key,value) values("t1",{"a":1}),("t2",{"a":2}),("t3",{"a":null}); prepare v from select * from default order by a $o nulls $n; execute v using {"o":"desc","n":"last"}; execute v using {"o":"desc","n":"first"}; execute v using {"o":"asc","n":"first"}; execute v using {"o":"asc","n":"last"};   If a parameter is invalid or absent, the query should still run but warnings will be produced; if missing, order will default to ASC and nulls position to FIRST with ASC order and LAST with DESC order.

          Build couchbase-server-7.1.0-1099 contains query commit 4151f26 with commit message:
          MB-45957 Finesse error reporting and defaults.

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1099 contains query commit 4151f26 with commit message: MB-45957 Finesse error reporting and defaults.

          Build couchbase-server-7.1.0-1099 contains query commit dd689a1 with commit message:
          MB-45957 Revise fix to address issues.

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1099 contains query commit dd689a1 with commit message: MB-45957 Revise fix to address issues.

          Verified (and added test cases) with 7.1.0-2475

          pierre.regazzoni Pierre Regazzoni added a comment - Verified (and added test cases) with 7.1.0-2475

          People

            pierre.regazzoni Pierre Regazzoni
            Sitaram.Vemulapalli Sitaram Vemulapalli
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty