[FEATURE] BETWEEN SYMMETRIC not supported

[tomershay]

Relevant PG docs: https://www.postgresql.org/docs/current/functions-comparison.html

Steps to reproduce:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    total_price NUMERIC,
    discount_price NUMERIC
);

INSERT INTO orders (total_price, discount_price) VALUES
      (120, 80),     -- 100 is between these (SYMMETRIC: yes)
      (150, 200),    -- 100 is NOT between these
      (90, 110),     -- 100 is between these
      (50, 50),      -- 100 is NOT between
      (130, 70);     -- 100 is between these (SYMMETRIC: yes)

SELECT *
FROM orders
WHERE 100 BETWEEN SYMMETRIC total_price AND discount_price;

The parsing error:

[manticore-projects]

Greetings.

SYMMETRIC is not supported.
I would not mind, when you send a PR but I myself have very little interest in those not standard compliant exotic syntax (pardon for being blunt).

[manticore-projects]

I don't even understand why anyone would use those syntax which are not even a bit portable to any other RDBMS. I thought Oracle would be terrible r/ portability.

[tomershay]

Thanks for the quick feedback. Yes, it seems PG is a pioneer in implementing it, but this feature does seem to be a part of the SQL standard (page 321), though as you mentioned most other RDBMS didn't implement it just yet.

[manticore-projects]

You got me good and you have a strong point now.
I will add it over the weekend. Thanks for pointing it out.

[manticore-projects]

I have shown it on my own website: https://manticore-projects.com/SQL2016Parser/syntax.html#between-predicate-part-2

[manticore-projects]

Done.
Please note: JSQLParser 5.4-SNAPSHOT uses (for a first time!) JavaCC-8 Snapshot for generating the parser. Do let me know if anything unexpected happens.

Thanks and cheers!

[tomershay]

@manticore-projects Thanks for the swift turnaround!
I’ve just tested an upgrade to the latest commit and everything is working perfectly.