olavloite / spanner-jdbc

JDBC Driver for Google Cloud Spanner
MIT License
38 stars 10 forks source link

JSQL parser error confused by regex in query #133

Open O1O1O1O opened 5 years ago

O1O1O1O commented 5 years ago

I found another case where the parser blows up because of parens in the query. This time it is a regexp_contains value provided via a query parameter. The same query runs fine with the GCP Spanner console:

SELECT city, sum(pageviews) AS count FROM ipapi_domain_geo_url_pageviews WHERE client_id=1000004 AND domain='foo.com' AND date >= 2018-06-30 AND date <= 2018-06-30 AND NOT REGEXP_CONTAINS(url, '^https?://(.*careers)$') GROUP BY city ORDER BY count DESC

Driver jsql parser complains about

Caused by: nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
    at line 6, column 38.

Was expecting one of:

    "&&"
    ";"
    "AND"
    "CONNECT"
    "EXCEPT"
    "FOR"
    "GROUP"
    "HAVING"
    "INTERSECT"
    "MINUS"
    "ORDER"
    "START"
    "UNION"
    <EOF>

For now I have worked around by removing parens from my regexp and using multiple AND conditions to match multiple patterns where necessary instead of folding them into the regexp.