SQL compile (parse) error when slicing an array.

[virtuallyunknown]

Greetings, thanks for developing the playground!

I've found a weird issue, here is a minimal reproducible example. The error is on line 6 and this code in particular:

  .select(({ fn }) =>
    sql`(${fn.agg('array_agg', ['test'])})[:5]`.as('test')
  )
  // compile: Error: Parse error: Unexpected ":5] as "te" at line 1 column 28

For some reason, trying to slice an array using the colon symbol between square brackets causes this to error out. There are no issues with this code for example:

  .select(({ fn }) =>
    sql`(${fn.agg('array_agg', ['test'])})`.as('test')
  )

I should conclude by mentioning 2 things.

1. I found that switching between postgres and mysql on the playground will error out, however if you use sqlite it will not.
2. When running this locally on my backend, kysely does not throw any errors, and if I log the query it will produce valid SQL which I can run against my database without issues.

EDIT: Also tried using parens, but that did not work either. playground link

[wirekang]

It seems sql-formatter can't parse [:] syntax. I updated the playground to show sql without formatting when there is a formatting error. Check out playground links you wrote.

[virtuallyunknown]

It seems sql-formatter can't parse [:] syntax. I updated the playground to show sql without formatting when there is a formatting error. Check out playground links you wrote.

Thanks.

I did check both of my example links, and the outputted SQL appears to be valid. By valid I mean it can be copy-pasted an run against a Postgres database and it won't output any errors.

Example with Postgres 15

However, I also noticed that if I change the Postgres version on db-fiddle.com to anything below 9.6, it will give this error when you run the query.

Example with Postgres 9.5

Query Error: error: syntax error at or near ":"

So this could be related to the issue here.

[wirekang]

It's just a matter of formatting text. Your text is completely valid as postgres sql. Let's wait the response: sql-formatter-org/sql-formatter#624

[wirekang]

The error fixed but with weird spaces. sql-formatter-org/sql-formatter#489