Skip to content

Postgresol dialect does not produce proper syntax for regex operator #952

@elgow

Description

@elgow

Describe the bug
The query where('col', 'regex', 'expr') produces SQL WHERE col REGEX 'expr' which is wrong and causes the error

QueryException: syntax error at or near "REGEXP"

It should produce WHERE col ~ 'expr'.

The code to fix would be

/src/masoniteorm/query/grammars/PostgresGrammar.py

    def where_regexp_string(self):
        return "{keyword} {column} ~ {value}"

    def where_not_regexp_string(self):
        return "{keyword} {column} !~ {value}"

To Reproduce
Steps to reproduce the behavior:
Add the following missing tests to the test suite

    def test_where(self):
        builder = self.get_builder()
        builder.where("name", "regexp", "Joe")
        sql = getattr(
            self, inspect.currentframe().f_code.co_name.replace("test_", "")
        )()
        self.assertEqual(builder.to_sql(), sql)

    def test_where(self):
        builder = self.get_builder()
        builder.where("name", "not regexp", "Joe")
        sql = getattr(
            self, inspect.currentframe().f_code.co_name.replace("test_", "")
        )()
        self.assertEqual(builder.to_sql(), sql)

Expected behavior
Should produce correct SQL syntax for Postgres using the ~ operator. The correct test data should be the following in

src/masoniteorm/query/grammars/PostgresGrammar.py

    def where_regexp(self):
        """
        builder = self.get_builder()
        builder.where("name", "regexp", "Joe").to_sql()
        """
        return """SELECT * FROM "users" WHERE "users"."name" ~ 'Joe'"""

    def where_not_regexp(self):
        """
        builder = self.get_builder()
        builder.where("name", "not regexp", "Joe").to_sql()
        """
        return """SELECT * FROM "users" WHERE "users"."name" !~ 'Joe'"""

Screenshots or code snippets

Desktop (please complete the following information):

  • OS: Mac M2
  • Version Sequoia 15.5

What database are you using?

  • Type: Postgres
  • Version 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
  • Masonite ORM 3.0.0

Additional context
Any other steps you are doing or any other related information that will help us debug the problem please put here.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugAn existing feature is not working as intended

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions