4paradigm / 4paradigm/OpenMLDB
Parameteried insert statement execute fail when we have `?` in string
- Ngôn ngữ chính
- C++
- Star
- 1.7k
- Fork
- 331
- Merge trung bình
- 12 ngày 12 giờ
- Pull request đã merge (30 ngày)
- 1
Mô tả
Issue tracker is **ONLY** used for reporting bugs. New features should be discussed on our discussion
## Expected Behavior
## Current Behavior
```sql
Traceback (most recent call last):
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
File "/opt/rh/rh-python38/root/usr/local/lib/python3.8/site-packages/sqlalchemy_openmldb/openmldbapi/_openmldbapi.py", line 223, in execute
raise DatabaseError("parameters is not enough")
sqlalchemy_openmldb.openmldbapi._openmldbapi.DatabaseError: parameters is not enough
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "rtidb_client_test.py", line 49, in test_basic
connection.execute(insert2, ({"col4":"anhui", "col5":2}));
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1003, in execute
return self._execute_text(object_, multiparams, params)
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1172, in _execute_text
ret = self._execute_context(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
util.raise_(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
File "/opt/rh/rh-python38/root/usr/local/lib/python3.8/site-packages/sqlalchemy_openmldb/openmldbapi/_openmldbapi.py", line 223, in execute
raise DatabaseError("parameters is not enough")
sqlalchemy.exc.DatabaseError: (sqlalchemy_openmldb.openmldbapi._openmldbapi.DatabaseError) parameters is not enough
[SQL: insert into tsql1010 values(1001, '2020-12-26', 'hefei?', ?, ?);]
[parameters: {'col4': 'anhui', 'col5': 2}]
(Background on this error at: http://sqlalche.me/e/13/4xp6)
```
## Possible Solution
Fix the preprocess logic when execute SQL text in _openmldbapi.py.
## Steps to Reproduce
- build openmldb and sqlalchemy_openmldb
```
cmake -DSQL_PYSDK_ENABLE=ON -DSQL_JAVASDK_ENABLE=OFF -DTESTING_ENABLE=OFF ..
make sqlalchemy_openmldb openmldb
```
- add python sdk case :
```python
def test_basic(self):
engine = db.create_engine('openmldb:///db_test?zk=127.0.0.1:6181&zkPath=/onebox')
connection = engine.connect()
try:
connection.execute("create database db_test;")
except Exception as e:
pass
try:
connection.execute("drop table tsql1010;")
except Exception as e:
pass
time.sleep(2)
connection.execute(ddl)
insert2 = "insert into tsql1010 values(1001, '2020-12-26', 'hefei?', ?, ?);" # anhui 2
connection.execute(insert2, ({"col4":"anhui", "col5":2}));
```
- execute test
```
sh steps/fesql_test_python.sh
```
## Context (Environment)
## Detailed Description
## Possible Implementation
Hướng dẫn đóng góp
Hướng nghiên cứu
The bug is in sqlalchemy_openmldb/openmldbapi/_openmldb.py where SQL text preprocessing incorrectly counts '?' inside string literals as parameter placeholders. Start by examining the execute method in that file, trace how parameters are matched. Write a test that reproduces the error with a string containing '?', then adjust the preprocessing logic to ignore '?' within quotes. Verify by running the provided test steps.
Do mô hình lập chỉ mục viết ra từ nội dung của issue.
Đánh giá
- Công nghệ
- python, sql, sqlalchemy
- Lĩnh vực
- backend, databases
- Loại issue
- Lỗi
- Độ khó
- 3/5
- Thời gian dự kiến
- 1-2 ngày
- Mức độ hoạt động
- Đình trệ
- Độ rõ ràng
- Khá rõ ràng
- Mức phù hợp với người mới
- 45/100