Repository: crate/crate-python

Issue:
SQLParseException[line 4:8: no viable alternative at input 'LIMIT -']
### Problem
```
sqlalchemy.exc.ProgrammingError: (crate.client.exceptions.ProgrammingError) SQLParseException[line 4:8: no viable alternative at input 'LIMIT -']
[SQL: SELECT testdrive.id, testdrive.name, testdrive.age, testdrive.gender
FROM testdrive
WHERE testdrive.name = ?
 LIMIT -1 OFFSET ?]
[parameters: ('John Doe', 0)]
(Background on this error at: https://sqlalche.me/e/14/f405)
```

### Reproduction
Enable the _Query multiple records_ code of [`cratedb-dataset-demo.py`](https://gist.github.com/amotl/eb4f6b221e81484a445bcc3417de719d) and run it.

### Reason
SQLAlchemy's [`compiler.py:limit_clause`](https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_4_41/lib/sqlalchemy/sql/compiler.py#L3749-L3757) implementation:
```python
    def limit_clause(self, select, **kw):
        text = ""
        if select._limit_clause is not None:
            text += "\n LIMIT " + self.process(select._limit_clause, **kw)
        if select._offset_clause is not None:
            if select._limit_clause is None:
                text += "\n LIMIT -1"
            text += " OFFSET " + self.process(select._offset_clause, **kw)
        return text
```


Discussion:

Patch:
diff --git a/CHANGES.txt b/CHANGES.txt
index 5e99d18..6d080ad 100644
--- a/CHANGES.txt
+++ b/CHANGES.txt
@@ -7,6 +7,10 @@ Unreleased
 - Added a generic data type converter to the ``Cursor`` object, for converting
   fetched data from CrateDB data types to Python data types.
 
+- Fixed generating appropriate syntax for OFFSET/LIMIT clauses. It was possible
+  that SQL statement clauses like ``LIMIT -1`` could have been generated. Both
+  PostgreSQL and CrateDB only accept ``LIMIT ALL`` instead.
+
 
 2022/10/10 0.27.2
 =================
diff --git a/src/crate/client/sqlalchemy/compiler.py b/src/crate/client/sqlalchemy/compiler.py
index d5c29e8..22a2049 100644
--- a/src/crate/client/sqlalchemy/compiler.py
+++ b/src/crate/client/sqlalchemy/compiler.py
@@ -23,6 +23,7 @@ import string
 from collections import defaultdict
 
 import sqlalchemy as sa  # lgtm[py/import-and-import-from]
+from sqlalchemy.dialects.postgresql.base import PGCompiler
 from sqlalchemy.sql import compiler, crud, selectable  # lgtm[py/import-and-import-from]
 from .types import MutableDict
 from .sa_version import SA_VERSION, SA_1_4
@@ -289,6 +290,12 @@ class CrateCompiler(compiler.SQLCompiler):
 
         return text
 
+    def limit_clause(self, select, **kw):
+        """
+        Generate OFFSET / LIMIT clause, PostgreSQL-compatible.
+        """
+        return PGCompiler.limit_clause(self, select, **kw)
+
     def _get_crud_params(compiler, stmt, **kw):
         """ extract values from crud parameters
         taken from SQLAlchemy's crud module (since 1.0.x) and


Test patch:
diff --git a/src/crate/client/sqlalchemy/tests/compiler_test.py b/src/crate/client/sqlalchemy/tests/compiler_test.py
index e1ab2a6..62157d5 100644
--- a/src/crate/client/sqlalchemy/tests/compiler_test.py
+++ b/src/crate/client/sqlalchemy/tests/compiler_test.py
@@ -34,8 +34,8 @@ class SqlAlchemyCompilerTest(TestCase):
     def setUp(self):
         self.crate_engine = sa.create_engine('crate://')
         self.sqlite_engine = sa.create_engine('sqlite://')
-        metadata = sa.MetaData()
-        self.mytable = sa.Table('mytable', metadata,
+        self.metadata = sa.MetaData()
+        self.mytable = sa.Table('mytable', self.metadata,
                                 sa.Column('name', sa.String),
                                 sa.Column('data', Craty))
 
@@ -69,3 +69,30 @@ class SqlAlchemyCompilerTest(TestCase):
         )
 
         self.assertFalse(hasattr(clauseelement, '_crate_specific'))
+
+    def test_select_with_offset(self):
+        """
+        Verify the `CrateCompiler.limit_clause` method, with offset.
+        """
+        self.metadata.bind = self.crate_engine
+        selectable = self.mytable.select().offset(5)
+        statement = str(selectable.compile())
+        self.assertEqual(statement, "SELECT mytable.name, mytable.data \nFROM mytable\n LIMIT ALL OFFSET ?")
+
+    def test_select_with_limit(self):
+        """
+        Verify the `CrateCompiler.limit_clause` method, with limit.
+        """
+        self.metadata.bind = self.crate_engine
+        selectable = self.mytable.select().limit(42)
+        statement = str(selectable.compile())
+        self.assertEqual(statement, "SELECT mytable.name, mytable.data \nFROM mytable \n LIMIT ?")
+
+    def test_select_with_offset_and_limit(self):
+        """
+        Verify the `CrateCompiler.limit_clause` method, with offset and limit.
+        """
+        self.metadata.bind = self.crate_engine
+        selectable = self.mytable.select().offset(5).limit(42)
+        statement = str(selectable.compile())
+        self.assertEqual(statement, "SELECT mytable.name, mytable.data \nFROM mytable \n LIMIT ? OFFSET ?")