4paradigm / 4paradigm/OpenMLDB

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

Ouverte
#285 3 commentaires 0 réactions 1 personne assignée Assignée à @tobegit3hub Voir sur GitHub
bug python
Langage dominant
C++
Étoiles
1.7k
Forks
331
Merge moyen
12 j 12 h
PR mergées (30 j)
1

Description

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

Guide de contribution

Ouvrir le guide de contribution

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
python, sql, sqlalchemy
Domaine
backend, databases
Type d'issue
Bug
Difficulté
3/5
Temps estimé
1-2 jours
Activité
À l'abandon
Clarté
Plutôt claire
Accessibilité débutants
45/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.