francis0407 commented on issue #23783: [SPARK-26854][SQL] Support ANY/SOME subquery URL: https://github.com/apache/spark/pull/23783#issuecomment-466739389 I test several related expressions in PostgreSQL, MySQL, Oracle and SQL Server. Here are the results: ### PostgreSQL PostgreSQL is the **only one** that supports such syntax:" (x, y) > ANY (query)". For example: ```SQL SELECT * FROM t1 WHERE (t1a, t1b) > ANY (SELECT t2a, t2b FROM t2); ``` PostgreSQL explains it as: ``` Nested Loop Semi Join (cost=0.00..3195.44 rows=173 width=128) Join Filter: (ROW((t1.t1a)::text, t1.t1b) > ROW((t2.t2a)::text, t2.t2b)) -> Seq Scan on t1 (cost=0.00..15.20 rows=520 width=128) -> Materialize (cost=0.00..17.80 rows=520 width=84) -> Seq Scan on t2 (cost=0.00..15.20 rows=520 width=84) ``` Also I run a few tests about `ROW(a, b)< ROW(c, d)`: ```SQL SELECT (1, 2) < (2, 3); -- true SELECT (1, 2) < (2, null); -- true SELECT (1, 2) < (null, 3); -- null SELECT (2, 3) < (1, null); -- false SELECT (1, 2) = (1, 2); -- true SELECT (1, 3) = (1, 2); -- false SELECT (1, 2) = (1, null); -- null ``` From these simple cases, I got such a conclusion: 1. `(a1, a2, ...) = (b1, b2, ...)` means `(a1 = b1) AND (a2 = b2) AND ...` 2. `(a1, a2, ...) < (b1, b2, ...)` means `a1 < b1` ### MySQL MySQL can execute expressions like `ROW(a, b) < ROW(c, d)`, and behaves the same as PostgreSQL. ### Oracle & SQL Server Both of them don't support such expression ( or they have some specific syntax?)
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
