Description

ScalarSubquery usage

Assumptions: a, b are fields of table outer, c, d are fields of table inner, e, f are fields of table nested
Uncorrelated scalar subquery in Select

• source = outer | eval m = [ source = inner | stats max(c) ] | fields m, a
• source = outer | eval m = [ source = inner | stats max(c) ] + b | fields m, a

Uncorrelated scalar subquery in Select and Where

• source = outer | where a > [ source = inner | stats min(c) ] | eval m = [ source = inner | stats max(c) ] | fields m, a

Correlated scalar subquery in Select

• source = outer | eval m = [ source = inner | where outer.b = inner.d | stats max(c) ] | fields m, a
• source = outer | eval m = [ source = inner | where b = d | stats max(c) ] | fields m, a
• source = outer | eval m = [ source = inner | where outer.b > inner.d | stats max(c) ] | fields m, a

Correlated scalar subquery in Where

• source = outer | where a = [ source = inner | where outer.b = inner.d | stats max(c) ]
• source = outer | where a = [ source = inner | where b = d | stats max(c) ]
• source = outer | where [ source = inner | where outer.b = inner.d OR inner.d = 1 | stats count() ] > 0 | fields a

Nested scalar subquery

• source = outer | where a = [ source = inner | stats max(c) | sort c ] OR b = [ source = inner | where c = 1 | stats min(d) | sort d ]
• source = outer | where a = [ source = inner | where c = [ source = nested | stats max(e) by f | sort f ] | stats max(d) by c | sort c | head 1 ]

SQL Migration examples with Scalar-Subquery PPL:
Example 1

SELECT *
FROM   outer
WHERE  a = (SELECT   max(c)
            FROM     inner1
            WHERE c = (SELECT   max(e)
                       FROM     inner2
                       GROUP BY f
                       ORDER BY f
                       )
            GROUP BY c
            ORDER BY c
            LIMIT 1)

Rewritten by PPL ScalarSubquery query:

source = spark_catalog.default.outer
| where a = [
    source = spark_catalog.default.inner1
    | where c = [
        source = spark_catalog.default.inner2
        | stats max(e) by f
        | sort f
      ]
    | stats max(d) by c
    | sort c
    | head 1
  ]

Example 2

SELECT * FROM outer
WHERE  a = (SELECT max(c)
            FROM   inner
            ORDER BY c)
OR     b = (SELECT min(d)
            FROM   inner
            WHERE  c = 1
            ORDER BY d)

Rewritten by PPL ScalarSubquery query:

source = spark_catalog.default.outer
| where a = [
    source = spark_catalog.default.inner | stats max(c) | sort c
  ] OR b = [
    source = spark_catalog.default.inner | where c = 1 | stats min(d) | sort d
  ]

Issues Resolved

Resolve #712 as a sub-task of #661

Check List

• Updated documentation (ppl-spark-integration/README.md)
• Implemented unit tests
• Implemented tests for combination with other commands
• Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.