4paradigm / 4paradigm/OpenMLDB

Parameteried insert statement execute fail when we have `?` in string

Đang mở
#285 3 bình luận 0 reaction 1 người được giao Được giao cho @tobegit3hub Xem trên GitHub
bug python
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

Mở 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

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.