revitalkr opened a new pull request, #2357:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2357
…ntly applied to Snowflake) to MySQL and Redshift.
## Summary
This PR extends the existing `supports_left_associative_joins_without_parens
= false` behavior (currently applied to Snowflake) to MySQL and Redshift.
### Problem
The following query is currently accepted only for Snowflake and rejected
for other dialects:
SELECT DISTINCT p.product_id
FROM orders AS o
INNER JOIN customers AS c
INNER JOIN products AS p ON p.customer_id = c.customer_id
ON c.order_id = o.order_id;
However, this query executes successfully in both MySQL and Redshift.
### Solution
Set `supports_left_associative_joins_without_parens = false` for MySQL and
Redshift, enabling them to accept the same class of queries already supported
for Snowflake.
### Validation
Attached scripts demonstrate that:
- The query is accepted by both dialects
- Their behavior is compatible with the existing Snowflake parsing mode
### Tests
The existing Snowflake test has been moved to the main test suite and is now
applied to all dialects where `supports_left_associative_joins_without_parens =
false`.
### scripts
-- =========================================================
-- MySQL validation script
-- 1. Acceptance of deferred ON query
-- 2. Behavior consistent with Snowflake parsing mode
-- =========================================================
-- -------------------------
-- PART A: REAL QUERY
-- -------------------------
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;
CREATE TABLE orders (
order_id INT
);
CREATE TABLE customers (
customer_id INT,
order_id INT
);
CREATE TABLE products (
product_id INT,
customer_id INT
);
INSERT INTO orders VALUES (1), (2), (3);
INSERT INTO customers VALUES
(10,1),
(20,2),
(30,3);
INSERT INTO products VALUES
(100,10),
(200,20),
(300,30);
-- ORIGINAL
SELECT 'PART_A_ORIGINAL' AS src, p.product_id
FROM orders AS o
INNER JOIN customers AS c
INNER JOIN products AS p ON p.customer_id = c.customer_id
ON c.order_id = o.order_id
ORDER BY p.product_id;
-- CANONICAL (Snowflake-style)
SELECT 'PART_A_CANONICAL' AS src, p.product_id
FROM orders AS o
INNER JOIN (
customers AS c
INNER JOIN products AS p ON p.customer_id = c.customer_id
)
ON c.order_id = o.order_id
ORDER BY p.product_id;
-- -------------------------
-- PART B: WITNESS QUERY
-- Demonstrates observable associativity difference
-- -------------------------
DROP TABLE IF EXISTS w_orders;
DROP TABLE IF EXISTS w_customers;
DROP TABLE IF EXISTS w_products;
CREATE TABLE w_orders (
order_id INT
);
CREATE TABLE w_customers (
customer_id INT,
order_id INT
);
CREATE TABLE w_products (
product_id INT,
customer_id INT
);
INSERT INTO w_orders VALUES (1), (2);
INSERT INTO w_customers VALUES
(10,1),
(20,2);
INSERT INTO w_products VALUES
(100,10);
-- ORIGINAL
SELECT 'PART_B_ORIGINAL' AS src, o.order_id, c.customer_id, p.product_id
FROM w_orders AS o
LEFT JOIN w_customers AS c
INNER JOIN w_products AS p ON p.customer_id = c.customer_id
ON c.order_id = o.order_id
ORDER BY o.order_id, c.customer_id, p.product_id;
-- CANONICAL
SELECT 'PART_B_CANONICAL' AS src, o.order_id, c.customer_id, p.product_id
FROM w_orders AS o
LEFT JOIN (
w_customers AS c
INNER JOIN w_products AS p ON p.customer_id = c.customer_id
)
ON c.order_id = o.order_id
ORDER BY o.order_id, c.customer_id, p.product_id;
-- ALTERNATIVE (left-deep)
SELECT 'PART_B_LEFT_DEEP' AS src, o.order_id, c.customer_id, p.product_id
FROM (
w_orders AS o
LEFT JOIN w_customers AS c ON c.order_id = o.order_id
)
INNER JOIN w_products AS p ON p.customer_id = c.customer_id
ORDER BY o.order_id, c.customer_id, p.product_id;
-- =========================================================
-- Redshift validation script
-- 1. Acceptance of deferred ON query
-- 2. Behavior consistent with Snowflake parsing mode
-- =========================================================
-- -------------------------
-- PART A: REAL QUERY
-- -------------------------
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;
CREATE TEMP TABLE orders (
order_id INT
);
CREATE TEMP TABLE customers (
customer_id INT,
order_id INT
);
CREATE TEMP TABLE products (
product_id INT,
customer_id INT
);
INSERT INTO orders VALUES (1), (2), (3);
INSERT INTO customers VALUES
(10,1),
(20,2),
(30,3);
INSERT INTO products VALUES
(100,10),
(200,20),
(300,30);
-- ORIGINAL
SELECT 'PART_A_ORIGINAL' AS src, p.product_id
FROM orders AS o
INNER JOIN customers AS c
INNER JOIN products AS p ON p.customer_id = c.customer_id
ON c.order_id = o.order_id
ORDER BY p.product_id;
-- CANONICAL (Snowflake-style)
SELECT 'PART_A_CANONICAL' AS src, p.product_id
FROM orders AS o
INNER JOIN (
customers AS c
INNER JOIN products AS p ON p.customer_id = c.customer_id
)
ON c.order_id = o.order_id
ORDER BY p.product_id;
-- -------------------------
-- PART B: WITNESS QUERY
-- -------------------------
DROP TABLE IF EXISTS w_orders;
DROP TABLE IF EXISTS w_customers;
DROP TABLE IF EXISTS w_products;
CREATE TEMP TABLE w_orders (
order_id INT
);
CREATE TEMP TABLE w_customers (
customer_id INT,
order_id INT
);
CREATE TEMP TABLE w_products (
product_id INT,
customer_id INT
);
INSERT INTO w_orders VALUES (1), (2);
INSERT INTO w_customers VALUES
(10,1),
(20,2);
INSERT INTO w_products VALUES
(100,10);
-- ORIGINAL
SELECT 'PART_B_ORIGINAL' AS src, o.order_id, c.customer_id, p.product_id
FROM w_orders AS o
LEFT JOIN w_customers AS c
INNER JOIN w_products AS p ON p.customer_id = c.customer_id
ON c.order_id = o.order_id
ORDER BY o.order_id, c.customer_id, p.product_id;
-- CANONICAL
SELECT 'PART_B_CANONICAL' AS src, o.order_id, c.customer_id, p.product_id
FROM w_orders AS o
LEFT JOIN (
w_customers AS c
INNER JOIN w_products AS p ON p.customer_id = c.customer_id
)
ON c.order_id = o.order_id
ORDER BY o.order_id, c.customer_id, p.product_id;
-- ALTERNATIVE (left-deep)
SELECT 'PART_B_LEFT_DEEP' AS src, o.order_id, c.customer_id, p.product_id
FROM (
w_orders AS o
LEFT JOIN w_customers AS c ON c.order_id = o.order_id
)
INNER JOIN w_products AS p ON p.customer_id = c.customer_id
ORDER BY o.order_id, c.customer_id, p.product_id;
``
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
