Uploaded image for project: 'Solr'
  1. Solr
  2. SOLR-10085

SQL result set fields should be ordered by the field list

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 6.3
    • Fix Version/s: None
    • Component/s: faceting
    • Security Level: Public (Default Security Level. Issues are Public)
    • Labels:
    • Environment:

      Windows 8.1, Java 8

      Description

      I'm trying out the Streaming Expressions in Solr 6.3.0.

      Currently, I'm facing the issue of not being able to get the fields in the result-set to be displayed in the same order as what I put in the query.

      For example, when I execute this query:

      http://localhost:8983/solr/collection1/stream?expr=facet(collection1,
      q=":",
      buckets="id,cost,quantity",
      bucketSorts="cost desc",
      bucketSizeLimit=100,
      sum(cost),
      sum(quantity),
      min(cost),
      min(quantity),
      max(cost),
      max(quantity),
      avg(cost),
      avg(quantity),
      count)&indent=true

      I get the following in the result-set.

      {
      "result-set":{"docs":[

      { "min(quantity)":12.21, "avg(quantity)":12.21, "sum(cost)":256.33, "max(cost)":256.33, "count(*)":1, "min(cost)":256.33, "cost":256.33, "avg(cost)":256.33, "quantity":12.21, "id":"000001", "sum(quantity)":12.21, "max(quantity)":12.21}

      ,

      { "EOF":true, "RESPONSE_TIME":359}

      ]}}

      The fields are displayed randomly all over the place, instead of the order sum, min, max, avg as in the query. This may cause confusion to user who look at the output. Possible improvement to display the fields in the result-set in the same order as the query

      1. SOLR-10085.patch
        8 kB
        Joel Bernstein
      2. SOLR-10085.patch
        4 kB
        Joel Bernstein
      3. SOLR-10085.patch
        4 kB
        Joel Bernstein

        Activity

        Hide
        dpgove Dennis Gove added a comment -

        There's no guarantee of field order in the tuple. Fields can be added to the tuple at any point in the stream (consider a select after a rollup after a facet after a search). There's no way to know that the user wants a particular order of the fields in the tuple.

        Beyond that, order of fields in json is not guaranteed. So while the stream handler could try to re-order the fields in the tuple, there is no guarantee that the serializer (or the client's deserializer) will honor that.

        From RFC 7159 -The JavaScript Object Notation (JSON) Data Interchange Format: An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array.

        Show
        dpgove Dennis Gove added a comment - There's no guarantee of field order in the tuple. Fields can be added to the tuple at any point in the stream (consider a select after a rollup after a facet after a search). There's no way to know that the user wants a particular order of the fields in the tuple. Beyond that, order of fields in json is not guaranteed. So while the stream handler could try to re-order the fields in the tuple, there is no guarantee that the serializer (or the client's deserializer) will honor that. From RFC 7159 -The JavaScript Object Notation (JSON) Data Interchange Format: An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array.
        Hide
        joel.bernstein Joel Bernstein added a comment - - edited

        I'm wondering if we can enforce field ordering by using a LinkedHashMap in the Tuples returned by the SelectStream. But we would also probably need to use a LinkedHashMap in the SolrStream as well to maintain the order on parallell requests. I believe Kevin Risden did some work on this at one point for the JDBC driver, but it appears that it wasn't committed.

        Show
        joel.bernstein Joel Bernstein added a comment - - edited I'm wondering if we can enforce field ordering by using a LinkedHashMap in the Tuples returned by the SelectStream. But we would also probably need to use a LinkedHashMap in the SolrStream as well to maintain the order on parallell requests. I believe Kevin Risden did some work on this at one point for the JDBC driver, but it appears that it wasn't committed.
        Hide
        risdenk Kevin Risden added a comment -

        I tried to go down that road of LinkedHashMap for the JDBC driverbut it just seemed no right. You can replace the HashMap implementation behind the scenes but that doesn't guarantee that fields will be inserted in the right order. For the JDBC driver ended up returning a list of fields and the order they should be in from the select. They are presented correctly on the JDBC side when requested by name or position.

        Show
        risdenk Kevin Risden added a comment - I tried to go down that road of LinkedHashMap for the JDBC driverbut it just seemed no right. You can replace the HashMap implementation behind the scenes but that doesn't guarantee that fields will be inserted in the right order. For the JDBC driver ended up returning a list of fields and the order they should be in from the select. They are presented correctly on the JDBC side when requested by name or position.
        Hide
        risdenk Kevin Risden added a comment -

        One thought I had but haven't tested is does the fl parameter allow for specifying the order of fields? Is it possible that fl would work with the streaming expression?

        Show
        risdenk Kevin Risden added a comment - One thought I had but haven't tested is does the fl parameter allow for specifying the order of fields? Is it possible that fl would work with the streaming expression?
        Hide
        joel.bernstein Joel Bernstein added a comment -

        With the Apache Calcite release having the fields out of order on direct requests to /sql is more of an issue. This is because by default the aggregate fields are referred to like this: $EXPR1. The 1 is the column number in the field list. So having aggregate fields out of order makes it very difficult to just read the results from /sql without using field aliases.

        I think that we can maintain the field order for responses from /sql by making some small changes. These changes would only effect the output from /sql, not /stream or /graph.

        The basic approach would be to make instance variables for the field list and alias list in the Tuple class. Then change the Tuple.writeMap method to iterate the field list when it writes out the fields. If the field list is null, it will simply iterate the internal map as it does now.

        The field list in the Tuple class could be populated inside of the SQLHandler. In the StreamHandler this field list would not be populated.

        I'll take a crack at implementing this an change the name of this ticket to be about SQL instead of streaming.

        Show
        joel.bernstein Joel Bernstein added a comment - With the Apache Calcite release having the fields out of order on direct requests to /sql is more of an issue. This is because by default the aggregate fields are referred to like this: $EXPR1. The 1 is the column number in the field list. So having aggregate fields out of order makes it very difficult to just read the results from /sql without using field aliases. I think that we can maintain the field order for responses from /sql by making some small changes. These changes would only effect the output from /sql, not /stream or /graph. The basic approach would be to make instance variables for the field list and alias list in the Tuple class. Then change the Tuple.writeMap method to iterate the field list when it writes out the fields. If the field list is null, it will simply iterate the internal map as it does now. The field list in the Tuple class could be populated inside of the SQLHandler. In the StreamHandler this field list would not be populated. I'll take a crack at implementing this an change the name of this ticket to be about SQL instead of streaming.
        Hide
        joel.bernstein Joel Bernstein added a comment - - edited

        Updated the ticket to reflect the focus on SQL output only. The work done in this ticket will make it possible though to maintain order in streaming as well. We can open another ticket to discuss the streaming implementation.

        Show
        joel.bernstein Joel Bernstein added a comment - - edited Updated the ticket to reflect the focus on SQL output only. The work done in this ticket will make it possible though to maintain order in streaming as well. We can open another ticket to discuss the streaming implementation.
        Hide
        joel.bernstein Joel Bernstein added a comment -

        tests are passing with the latest patch.

        Show
        joel.bernstein Joel Bernstein added a comment - tests are passing with the latest patch.
        Hide
        joel.bernstein Joel Bernstein added a comment -

        Added a simple test for field order

        Show
        joel.bernstein Joel Bernstein added a comment - Added a simple test for field order
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit b46e09c79f849d9211b3de235788bbf32d7aa84b in lucene-solr's branch refs/heads/master from Joel Bernstein
        [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=b46e09c ]

        SOLR-10085: SQL result set fields should be ordered by the field list

        Show
        jira-bot ASF subversion and git services added a comment - Commit b46e09c79f849d9211b3de235788bbf32d7aa84b in lucene-solr's branch refs/heads/master from Joel Bernstein [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=b46e09c ] SOLR-10085 : SQL result set fields should be ordered by the field list
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1bf01577748da82a394f3c64a2af68d5d8ae4ac2 in lucene-solr's branch refs/heads/branch_6x from Joel Bernstein
        [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=1bf0157 ]

        SOLR-10085: SQL result set fields should be ordered by the field list

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1bf01577748da82a394f3c64a2af68d5d8ae4ac2 in lucene-solr's branch refs/heads/branch_6x from Joel Bernstein [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=1bf0157 ] SOLR-10085 : SQL result set fields should be ordered by the field list
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 09373aaa0875b8ae2bb795d5dfafbdb1450546cc in lucene-solr's branch refs/heads/branch_6x from Christine Poerschke
        [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=09373aa ]

        SOLR-10046: remove CHANGES.txt entry

        (Reverses unintentional add alongside SOLR-10085 and SOLR-10254 CHANGES.txt update.)

        Show
        jira-bot ASF subversion and git services added a comment - Commit 09373aaa0875b8ae2bb795d5dfafbdb1450546cc in lucene-solr's branch refs/heads/branch_6x from Christine Poerschke [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=09373aa ] SOLR-10046 : remove CHANGES.txt entry (Reverses unintentional add alongside SOLR-10085 and SOLR-10254 CHANGES.txt update.)
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 4d3e94befcb5ea361ceff1fcff1bdc3e6166fdf1 in lucene-solr's branch refs/heads/branch_6_5 from Christine Poerschke
        [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=4d3e94b ]

        SOLR-10046: remove CHANGES.txt entry

        (Reverses unintentional add alongside SOLR-10085 and SOLR-10254 CHANGES.txt update.)

        Show
        jira-bot ASF subversion and git services added a comment - Commit 4d3e94befcb5ea361ceff1fcff1bdc3e6166fdf1 in lucene-solr's branch refs/heads/branch_6_5 from Christine Poerschke [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=4d3e94b ] SOLR-10046 : remove CHANGES.txt entry (Reverses unintentional add alongside SOLR-10085 and SOLR-10254 CHANGES.txt update.)

          People

          • Assignee:
            joel.bernstein Joel Bernstein
            Reporter:
            edwinyeozl Yeo Zheng Lin
          • Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:

              Development