Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-316

SqlRun: Match output regardless of order if ORDER BY not present

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.9.0-incubating
    • Component/s: None
    • Labels:
      None

      Description

      If ORDER BY is not present, a compliant implementation may return rows in any order. SqlRun should detect this, output the rows in the original order, not the actual order.

      This feature makes it much easier to handle unsorted data. The previous workaround, adding an ORDER BY clause, may trigger a different execution path and mask bugs. And it was common to forget an ORDER BY and have to go back to add the ORDER BY and correct the data some weeks later.

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        Close issues resolved in release 0.9.0-incubating (2014-08-25).

        Show
        julianhyde Julian Hyde added a comment - Close issues resolved in release 0.9.0-incubating (2014-08-25).
        Hide
        julianhyde Julian Hyde added a comment -

        We need to design the syntax bearing in mind that 95% of the code will be SQL and results. Comments and control structures are rarer, so can be less natural. SQL code should not require any special escaping.

        TCL or similar could work, provided it has an unobtrusive syntax for multi-line strings. E.g.

        sql [
          select *
          from emps
        ]
        expect [
        EMPNO | NAME
        ===== | ====
           10 | Fred
           20 | Bill
        ]
        if false [
        sql [
        select unimplememented_feature(1) from dept
        ]
        output [
        don't know yet
        ]
        ]
        
        Show
        julianhyde Julian Hyde added a comment - We need to design the syntax bearing in mind that 95% of the code will be SQL and results. Comments and control structures are rarer, so can be less natural. SQL code should not require any special escaping. TCL or similar could work, provided it has an unobtrusive syntax for multi-line strings. E.g. sql [ select * from emps ] expect [ EMPNO | NAME ===== | ==== 10 | Fred 20 | Bill ] if false [ sql [ select unimplememented_feature(1) from dept ] output [ don't know yet ] ]
        Hide
        vladimirsitnikov Vladimir Sitnikov added a comment -

        Not sure of the language yet.
        I guess even TCL would do better if we are to build our own control structures.

        I do not like "!" since it is strongly associated with negation: C, C++, Java, JavaScript, != in SQL, etc.
        Something positive would be better: \

        I recommend '!if (false) {',

        I was not able to get it working. I tried to uncommend one-by-one, but the whole thing started moving for unknown reason. I've spent an hour in debugger and gave up.
        One more reason to give up was lack of good tests since postgresql supports very few window framing clauses.

        Suggestions for something more intuitive?

        What do you think if I ask IDE support as one of the requirements?
        At least some syntax highlighting/bracket-matching.

        I hope I am the only one struggling with SqlRun syntax, but as you take some dose of MPS you'll see that IDE is vital when creating a new language.

        Show
        vladimirsitnikov Vladimir Sitnikov added a comment - Not sure of the language yet. I guess even TCL would do better if we are to build our own control structures. I do not like "!" since it is strongly associated with negation: C, C++, Java, JavaScript, != in SQL, etc. Something positive would be better: \ I recommend '!if (false) {', I was not able to get it working. I tried to uncommend one-by-one, but the whole thing started moving for unknown reason. I've spent an hour in debugger and gave up. One more reason to give up was lack of good tests since postgresql supports very few window framing clauses. Suggestions for something more intuitive? What do you think if I ask IDE support as one of the requirements? At least some syntax highlighting/bracket-matching. I hope I am the only one struggling with SqlRun syntax, but as you take some dose of MPS you'll see that IDE is vital when creating a new language.
        Hide
        julianhyde Julian Hyde added a comment -

        What syntax do you suggest?

        You can comment lines using # but to comment out sections I recommend '!if (false) {', because it allows merges on the commented section.

        The '!ok' command is peculiar to SqlRun. It is necessary because SqlRun needs to parse the output, in a limited way, when doing unordered matching.

        Also, I'd like to be able to add more see sections - not just output, but assertions, like 'this stmt gives an error matching xyx', or 'the plan is xyz'. Each of those would have its own postfix command. '!ok' is the only postfix command right now. Suggestions for something more intuitive?

        Show
        julianhyde Julian Hyde added a comment - What syntax do you suggest? You can comment lines using # but to comment out sections I recommend '!if (false) {', because it allows merges on the commented section. The '!ok' command is peculiar to SqlRun. It is necessary because SqlRun needs to parse the output, in a limited way, when doing unordered matching. Also, I'd like to be able to add more see sections - not just output, but assertions, like 'this stmt gives an error matching xyx', or 'the plan is xyz'. Each of those would have its own postfix command. '!ok' is the only postfix command right now. Suggestions for something more intuitive?
        Hide
        vladimirsitnikov Vladimir Sitnikov added a comment -

        Any chance we use some more standard syntax for SqlRun?

        As I implemented window aggregates, I did try adding SqlRun tests, but I failed. I even failed to un-comment already existing tests.
        The current syntax itself is obscure, and it is hard to debug.

        Show
        vladimirsitnikov Vladimir Sitnikov added a comment - Any chance we use some more standard syntax for SqlRun? As I implemented window aggregates, I did try adding SqlRun tests, but I failed. I even failed to un-comment already existing tests. The current syntax itself is obscure, and it is hard to debug.
        Show
        julianhyde Julian Hyde added a comment - Fixed in https://git-wip-us.apache.org/repos/asf?p=incubator-optiq.git;a=commit;h=8a91027c157e8c34ee1944faabb1353ec866d72a .

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            julianhyde Julian Hyde
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development