Support for LOWER() function (ERROR: at or near "(": syntax error)

[mtmacdonald]

Hi!

I hit a small syntax error when testing if Doltgres can running SQL migrations for our product. The scenario is:

Create a table (works)

CREATE TABLE IF NOT EXISTS items (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  metadata JSON,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Adding an index (with LOWER() function) (fails)

CREATE UNIQUE INDEX IF NOT EXISTS idx_items_title_lower ON items(lower(title));

-> ERROR: at or near "(": syntax error

Trying without the LOWER() function works. I'm not sure if Doltgres needs support for the function still, or if there's more going on here, but feedback would be appreciated.

This works:

CREATE UNIQUE INDEX IF NOT EXISTS idx_items_title_lower ON items(title);

I also saw error: expression index attribute is not yet supported in a similar script with this pattern (not sure if it's related, I can perhaps open a new ticket with steps to reproduce if it's not).

[Hydrocharged]

Syntax errors are parsing errors, so we need to see how our parser differs from Postgres in this case. My first thought is that we've registered LOWER as a keyword, when it should be either context-specific, or not a keyword. I'll take a look at this after my current item.

Definitely open a new issue for the attribute issue, as that's functionality that we've yet to add. All errors that contain not yet supported are ones that we've blocked out in the code, but haven't written the implementation for yet.

[zachmu]

A larger issue than the parsing error is that Dolt and Doltgres cannot currently index expressions on columns, only the raw column values. So even if we parse this ALTER TABLE command successfully, we don't actually implement the semantics you're looking for here. We can add that feature but it's more involved than the simple parser fix Daylon implemented above. Can you please file a separate issue for that feature?

[mtmacdonald]

A larger issue than the parsing error is that Dolt and Doltgres cannot currently index expressions on columns, only the raw column values. So even if we parse this ALTER TABLE command successfully, we don't actually implement the semantics you're looking for here. We can add that feature but it's more involved than the simple parser fix Daylon implemented above. Can you please file a separate issue for that feature?

Thanks - I opened #2250.

[mtmacdonald]

Syntax errors are parsing errors, so we need to see how our parser differs from Postgres in this case. My first thought is that we've registered LOWER as a keyword, when it should be either context-specific, or not a keyword. I'll take a look at this after my current item.

Definitely open a new issue for the attribute issue, as that's functionality that we've yet to add. All errors that contain not yet supported are ones that we've blocked out in the code, but haven't written the implementation for yet.

Thank you for merging a fix. I look forward to testing it when the next release is out. I will then re-test, and close this ticket.

I can also follow-up testing and submitting a ticket for error: expression index attribute is not yet supported when the release is ready.

[Hydrocharged]

We will have a new release soon. @zachmu is working on a high-priority bug fix that we're going to handle before getting a new release out, but I'll reply to this issue once it's out.