Escaping single quotes for BigQuery SQL target

[syko]

What happened?

Bigquery escapes single quotes with a backslash \ but prql outputs two single quotes ''.

Otherwise it seems highly compatible with bigquery except for this one small thing.

PRQL input

prql target:sql.bigquery

from foo
select {
  a = "a'b"
}

SQL output

SELECT
  'a''b' AS a
FROM
  foo

-- Generated by PRQL compiler version:0.13.2 (https://prql-lang.org)

Expected SQL output

SELECT
  'a\'b' AS a
FROM
  foo

MVCE confirmation

• [x] Minimal example
• [x] New issue

Anything else?

No response

[eitsupi]

There seems to be a problem with the handling of strings enclosed in single quotes, regardless of the target.

from foo
select {
  a = "a'b",
  b = "a\"b",
  c = 'a\'b',
  d = "a'b",
  e = 'a"b',
}

SELECT
  'a''b' AS a,
  'a"b' AS b,
  'a''b' AS c,
  'a''b' AS d,
  'a"b' AS e
FROM
  foo

-- Generated by PRQL compiler version:0.13.2 (https://prql-lang.org)

[kgutwin]

@eitsupi I think the quoting behavior you posted is correct for many dialects of SQL -- a pair of single quotes '' is the way to embed a single quote in a single-quoted string. Your example does work perfectly fine in the PRQL Playground. However, for BigQuery, and possibly for a few other dialects as well, the backslash-escaped quote \' is required instead.

I looked into this issue briefly, and PRQL uses the Rust sqlparser library to generate its SQL output, by building an AST and then using .to_string() to generate the SQL. Unfortunately, while sqlparser does have support for backslash-escape single quotes as a dialect option when parsing inbound SQL, I couldn't grasp how it would work to get it to generate such output from a string literal AST node. I got as far as tracking down Value.SingleQuotedString before the trail went cold.

[eitsupi]

Thank you for pointing that out! My bad.

The datafusion folks are helpful and it would be worthwhile to create an issue there.

[graup]

Hi! I also noticed this issue while working on https://github.com/apache/datafusion-sqlparser-rs/pull/1679

That PR doesn't fix this issue but I'm going to work on it next.

[PrettyWood]

Thank you so much @graup