search

exasol / sqlalchemy-exasol

SQLAlchemy dialect for EXASOL
https://exasol.github.io/sqlalchemy-exasol/
Other
34 stars 28 forks source link

🐞 Prepared statements send the wrong types as parameters to the server #341

Closed Nicoretti closed 1 year ago

Nicoretti commented 1 year ago

Summary

Prepared statements using the websocket based dialect seem to not send the correct types required/expected by the server.

Reproducing the Issue

Reproducible: always

  1. Remove the @ISSUE341 marker from the tests in test/sqlalchemy/test_suite.py

  2. Run the SQLA compliance test suit with the websocket based dialect as target pytest --log-debug=DEBUG --dropfirst --db exasol-websocket test/integration/sqlalchemy

  3. Have a look at the following tests

    • test/integration/sqlalchemy/test_suite.py::CastTypeDecoratorTest_exasol+exasol_driver_websocket_dbapi2::test_special_type - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::ExistsTest_exasol+exasol_driver_websocket_dbapi2::test_select_exists - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::ExistsTest_exasol+exasol_driver_websocket_dbapi2::test_select_exists_false - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_insert_from_select - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_insert_from_select_with_defaults - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_as_decimal - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_as_float - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
    • test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_custom_scale - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)

Expected Behaviour

Statements (test cases) get executed successfully.

Actual Behaviour

Statements (test cases) fail due to invalid types for the parameters are sent to the server.

e.g.:

getString: JSON value is not a string. (Session: 1766587743101452288)

Root Cause

Not a 100% identified yet

Context

This is a snippet of the client/server conversation for test case

127.0.0.1:57568 -> 127.0.0.1:8888 {"command": "execute", "sqlText": "CREATE TABLE t (\n\tx VARCHAR(50)\n)"}

127.0.0.1:8888 -> 127.0.0.1:57568 {"status":"ok","responseData":{"results":[{"resultType":"rowCount","rowCount":0}],"numResults":1}}

127.0.0.1:57568 -> 127.0.0.1:8888 {"command": "createPreparedStatement", "sqlText": "INSERT INTO t (x) VALUES (CAST(? AS VARCHAR(50)))"}

127.0.0.1:8888 -> 127.0.0.1:57568 {"status":"ok","responseData":{"statementHandle":7,"parameterData":{"numColumns":1,"columns":[{"name":"","dataType":{"type":"VARCHAR","size":2000000,"characterSet":"UTF8"}}]},"results":[{"resultType":"rowCount","rowCount":0}],

127.0.0.1:57568 -> 127.0.0.1:8888 {"command": "executePreparedStatement", "statementHandle": 7, "numColumns": 1, "numRows": 3, "columns": [{"name": "", "dataType": {"type": "VARCHAR", "size": 2000000, "characterSet": "UTF8"}}], "data": [[1, 2, 3]]}

127.0.0.1:8888 -> 127.0.0.1:57568 {"status":"error","exception":{"text":"getString: JSON value is not a string. (Session: 1765399198642536448)","sqlCode":"00000"}}

127.0.0.1:57568 -> 127.0.0.1:8888 {"command": "closePreparedStatement", "statementHandle": 7}

127.0.0.1:8888 -> 127.0.0.1:57568 {"status":"ok"}

...



#### Related Issues (optional)
* #342
Nicoretti commented 1 year ago

consider exposing/adding the debug feature of pyexasol, see here.