Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-1167 Improve join capabilities
  3. PHOENIX-945

Support correlated subqueries in comparison without ANY/SOME/ALL

    XMLWordPrintableJSON

Details

    • Sub-task
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 3.0.0, 4.0.0
    • 4.2.0, 3.2.0
    • None
    • None

    Description

      Example:
      SELECT employee_number, name
      FROM employees AS Bob
      WHERE salary > (
      SELECT AVG(salary)
      FROM employees
      WHERE department = Bob.department);

      Basically we can optimize these queries into join queries, like:
      SELECT employees.employee_number, employees.name
      FROM employees INNER JOIN
      (SELECT department, AVG(salary) AS department_average
      FROM employees
      GROUP BY department) AS temp ON employees.department = temp.department
      WHERE employees.salary > temp.department_average;

      Attachments

        1. 945.patch
          18 kB
          Wei Xue
        2. 945-2.patch
          23 kB
          Wei Xue

        Activity

          People

            maryannxue Wei Xue
            maryannxue Wei Xue
            Votes:
            4 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 336h
                336h
                Remaining:
                Remaining Estimate - 336h
                336h
                Logged:
                Time Spent - Not Specified
                Not Specified