Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18844

Implement EXCEPT ALL and INTERSECT ALL operations

Details

    Description

      SQL Standard allows to use EXCEPT ALL and INTERSECT ALL as set operations.
      Currently MariaDB Server does not support them.

      The goal of this task is to support EXCEPT ALL and INTERSECT ALL
      1. at syntax level - allow to use operators EXCEPT ALL and INTERSECT ALL in query expression body
      2. at execution level - implement these operations employing temporary tables
      (the implementation could use the idea similar to that used for the existing implementation of the INTERSECT operation).

      Attachments

        Issue Links

          Activity

            Waynest Ruihang Xia added a comment -

            This week I try to refine my code. Include deciding the routine of processing a record in advance rather than do it every time before execution or reduce the number of loops etc.
            These can be done in more than one ways, I first use a callback function but it seems not to match our expectation. Then I put different circumstances into different classes which is more convenient in this problem.
            What's more, by adding assertions to make it runs more restricted, some corner cases we did not concern before can be found. Like some function will not be called in the way I thought or some variables are set incorrectly but don't affect in many situations.

            Waynest Ruihang Xia added a comment - This week I try to refine my code. Include deciding the routine of processing a record in advance rather than do it every time before execution or reduce the number of loops etc. These can be done in more than one ways, I first use a callback function but it seems not to match our expectation. Then I put different circumstances into different classes which is more convenient in this problem. What's more, by adding assertions to make it runs more restricted, some corner cases we did not concern before can be found. Like some function will not be called in the way I thought or some variables are set incorrectly but don't affect in many situations.
            Waynest Ruihang Xia added a comment -

            In past days I change the code with my mentor. Run the test with a new option "--ps-protocol" and make it passed. Remove a callback function in send_data() and some dead code to make the code more clear. And changed some logits may lead to error or make the program run slower like unfolding duplicate records as soon as possible or adding a flag to prevent from optimizing twice. The behavior of SHOW CREATE VIEW also has changed. Now it will give the raw query rather than the query after optimized. There still has some problem in my code and I will continue work with it.

            Waynest Ruihang Xia added a comment - In past days I change the code with my mentor. Run the test with a new option "--ps-protocol" and make it passed. Remove a callback function in send_data() and some dead code to make the code more clear. And changed some logits may lead to error or make the program run slower like unfolding duplicate records as soon as possible or adding a flag to prevent from optimizing twice. The behavior of SHOW CREATE VIEW also has changed. Now it will give the raw query rather than the query after optimized. There still has some problem in my code and I will continue work with it.
            Waynest Ruihang Xia added a comment -

            This week I changed the way two extra fields are used. Make it more natural. The extra field closest to the data will now be used first.
            For SHOW CREATE VIEW to give the original query instead of the query is optimized, we use it to save the query and restore it when the view is created. However, this is not intuitive and will incur some additional overhead. So now it will skip the run optimization phase to create the view, the same as clause PREPARE ... FROM ...
            The optimization phase of the trunk operation involves only the same level of operation. But we can do more. Now it will run optimizations through the lex tree in a top-down manner, not only based on nodes at the same level, but also on superiors that might affect them.
            Added some new test cases that use set operations with limited resources (heap table size and temporary table size). In this case, some conversion is required.

            Waynest Ruihang Xia added a comment - This week I changed the way two extra fields are used. Make it more natural. The extra field closest to the data will now be used first. For SHOW CREATE VIEW to give the original query instead of the query is optimized, we use it to save the query and restore it when the view is created. However, this is not intuitive and will incur some additional overhead. So now it will skip the run optimization phase to create the view, the same as clause PREPARE ... FROM ... The optimization phase of the trunk operation involves only the same level of operation. But we can do more. Now it will run optimizations through the lex tree in a top-down manner, not only based on nodes at the same level, but also on superiors that might affect them. Added some new test cases that use set operations with limited resources (heap table size and temporary table size). In this case, some conversion is required.

            I reviewed your patch, there is 2 small thing you have to fix:
            1) in 2 places you remove SELECT but leave error, like here :
            select 1 as a from dual union all select 1 from dual;
            --error ER_PARSE_ERROR
            -select 1 from dual intersect all select 1 from dual;
            please remove the error also (it is in 2 tests)

            2) you made this public:
            uint curr_step, prev_step, curr_sel;
            enum sub_select_type step;

            you do not need it public, maximum protected, please remove this hack.

            sanja Oleksandr Byelkin added a comment - I reviewed your patch, there is 2 small thing you have to fix: 1) in 2 places you remove SELECT but leave error, like here : select 1 as a from dual union all select 1 from dual; --error ER_PARSE_ERROR -select 1 from dual intersect all select 1 from dual; please remove the error also (it is in 2 tests) 2) you made this public: uint curr_step, prev_step, curr_sel; enum sub_select_type step; you do not need it public, maximum protected, please remove this hack.

            The patch for the task was merged into 10.5

            igor Igor Babaev (Inactive) added a comment - The patch for the task was merged into 10.5

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.