Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Escaping single quotes for BigQuery SQL target #5099

Open
2 tasks done
syko opened this issue Jan 20, 2025 · 5 comments
Open
2 tasks done

Escaping single quotes for BigQuery SQL target #5099

syko opened this issue Jan 20, 2025 · 5 comments
Labels
bug Invalid compiler output or panic compiler parser

Comments

@syko
Copy link

syko commented Jan 20, 2025

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

  • Minimal example
  • New issue

Anything else?

No response

@syko syko added the bug Invalid compiler output or panic label Jan 20, 2025
@eitsupi
Copy link
Member

eitsupi commented Jan 24, 2025

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)

@eitsupi eitsupi changed the title Escaping quotes in bigquery Escaping single quotes Jan 24, 2025
@kgutwin
Copy link
Collaborator

kgutwin commented Jan 24, 2025

@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
Copy link
Member

eitsupi commented Jan 24, 2025

Thank you for pointing that out! My bad.

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

@eitsupi eitsupi changed the title Escaping single quotes Escaping single quotes for bigquery SQL target Jan 24, 2025
@vanillajonathan vanillajonathan changed the title Escaping single quotes for bigquery SQL target Escaping single quotes for BigQuery SQL target Jan 25, 2025
@graup
Copy link

graup commented Jan 30, 2025

Hi! I also noticed this issue while working on apache/datafusion-sqlparser-rs#1679

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

@PrettyWood
Copy link
Collaborator

Thank you so much @graup

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Invalid compiler output or panic compiler parser
Projects
None yet
Development

No branches or pull requests

5 participants