[CONPY-180] select distinct gives OperationalError, select distinctrow is ok Created: 2021-11-30  Updated: 2021-12-04  Resolved: 2021-12-04

Status: Closed
Project: MariaDB Connector/Python
Component/s: DBAPI 2.0
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: V H Lemoine Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 10

Python Version: 1.0.7

 Description   

Code

nowym = datetime.now().strftime('%Y%m')

sql = f"SELECT distinct clientID FROM zgx_forecasting WHERE date_format(STR_TO_DATE(concat(Jaar,'',Maand,'-01'),'%Y%m-%d'),'%Y%m')>='

{nowym}' AND Productiviteitscode IN ('1','3','5','7','9') order by clientID"
csr.execute(sql)

returns mariadb.OperationalError: Unknown MySQL error

Code
sql = f"SELECT distinctrow clientID FROM zgx_forecasting WHERE date_format(STR_TO_DATE(concat(Jaar,'',Maand,'-01'),'%Y%m-%d'),'%Y%m')>='{nowym}

' AND Productiviteitscode IN ('1','3','5','7','9') order by clientID"
csr.execute(sql)

returns 388 rows

Does python/connector support distinctrow only?

 Comments   
Comment by Georg Richter [ 2021-12-01 ]

There is no difference between the two statements since DISTINCTROW is a synonym for DISTINCT which will be substituted by parser/lexer of the database server.

The returned error "Unknown MySQL error" is a client and not a server error, which indicates that something either in Python Connector or in your application is wrong. However to analyze the problem we need the following information:

  • Python version (likely not 1.0.7 but 3.x)
  • Table definition
  • A short reproducible test case (python script).
Comment by V H Lemoine [ 2021-12-03 ]

Python version 3.9, mariadb python connector 1.0.7.

The problem is solved.

The statement was incorrectly formed it contained invalid invisible characters. So now distinct and distinctrow both are working.

You may close this issue.

Generated at Thu Feb 08 03:30:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.