Support Oracle's SQL dialect

[Barney-x1]

I did a double take when I saw the list of supported (/to-be-supported) SQL dialects.

A quick search for "database market share" will take you to multiple sources which confirm that not only is Oracle the most-used database, it has 100+ times the user base of some of the to-be-supported databases.

Am I missing something?

[max-sixty]

We inherit this from sqlparser-rs, this isn't meant as a normative statement!

Note that the generic dialect will work for most things; this is only needed for dialect-specific SQL.

Please feel free to open an issue there if Oracle would be helpful.

Thanks @Barney-x1

[Barney-x1]

I don't really understand the dependency. Surely sqlparser-rs consumes SQL, whereas PRQL generates it?

[max-sixty]

sqlparser-rs also creates it! We convert into their ast at https://github.com/prql/prql/blob/0.2.6/prql-compiler/src/sql/translator.rs#L52 and then it writes it out. Does that make sense?

[aljazerzen]

@Barney-x1 I'm not too familiar with Oracle dialect. Can you outline the key differences?
Which quotes does it use for strings? Does it use TOP or LIMIT?

Because code produced by prql-compiler uses just a subset of SQL, it is very likely that it will work out of the box, if Oracle's SQL is compliant with ISO/IEC 9075.

Just to be clear, is the dialect called PL/SQL or is it just an extension?

[max-sixty]

I'll close this for the moment, given it relies on a feature upstream. Feel free to reopen if there's something we can do from here.

To reiterate, this is not a normative statement about Oracle. And because the vast majority of PL/SQL overlaps with standard SQL, it's very possible to use PRQL with an Oracle database already.

[ftsfranklin]

Name:

• I think it's referred to as Oracle Database SQL. But it's not like it's a widely-used name, and it's very confusing because Oracle also supports other databases and SQLs for them.
• PL/SQL is the procedural language for Oracle Database, which is an extension of the SQL language, but is not the SQL language.

Differences I've identified so far:

• Can't use AS for table alias.
  • Table/query alias (FROM): AS is not allowed.
  • Column alias (SELECT): AS is optional.
  • CTE (WITH): AS is mandatory.
• No leading underscore in names.
  "Nonquoted identifiers must begin with an alphabetic character from your database character set."
  https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Database-Object-Names-and-Qualifiers.html
• Can't use LIMIT n.
  • Old syntax: WHERE ROWNUM < n (need to wrap the query if it has WHERE or ORDER)
  • Since 12c: FETCH FIRST n ROWS ONLY (like sql.mssql)
• Can't do a bare like SELECT 1 AS x. Requires FROM dual.
  • Until version 23.
• CONCAT takes exactly two args (until version 23).
  • E.g. This fails because it passes three args:
    • filter (text.contains 'needle' haystack)
    • AND haystack LIKE CONCAT('%', 'needle', '%')

Differences to check:

• Keywords that need to be explicitly quoted.
• String syntax.
• Quote string literals?
• Timestamp literals?
• Conversions?

---

P.S.: The disallowed leading underscores are used by PRQL for temporary names.

Oddly, the home page example turns income = total - transaction_fees into total - 0.8 AS _expr_0, instead of using the specified name income.

Reproduction:

from invoices
derive income = total - 0.8
filter income > 1
aggregate { sum_income = sum income }

Output:

WITH table_0 AS (
  SELECT
    total - 0.8 _expr_0
  FROM
    invoices
)
SELECT
  COALESCE(SUM(_expr_0), 0) sum_income
FROM
  table_0
WHERE
  _expr_0 > 1

---

P.S.: text.contains takes a pattern rather than a literal, which is surprising because then there's no reason to use CONCAT.

• The documentation doesn't specify if it's a substring or a pattern.
• Doesn't escape:
  • filter (text.contains '%ne_dle' haystack)
  • AND haystack LIKE CONCAT('%', '%ne_dle', '%')
• It SHOULD just do LIKE '%needle%', right?
  • Or if it's meant to be use INSTR(haystack, 'needle') > 0.

What's the intended behavior?