duckdb_mysql icon indicating copy to clipboard operation
duckdb_mysql copied to clipboard

Decimal data type conversion issue with mysql_query function

Open cmartin1968 opened this issue 9 months ago • 1 comments

I ran across some odd behavior using the mysql extension with duckdb version 0.10.2. I'm querying a mysql table with a DECIMAL data type and creating a Polars dataframe. If I query the table directly I get the expected result. If however I use the mysql_query function (e.g. sql passthrough), the values for the DECIMAL type are silently converted to NULL values. Is this a bug or perhaps a limitation in the features of this function? This happens when converting to Polars and Pandas.

To reproduce:

In MySQL (MariaDB):

`CREATE TABLE tmp.mysql_table (col1 int, col2 decimal(5,2))
ENGINE=InnoDB
DEFAULT CHARSET=latin1
COLLATE=latin1_swedish_ci;

insert into tmp.mysql_table values (1,1.11), (2,2.22), (3,3.33);`

In Python:

`conn = duckdb.connect(database=':memory:', read_only=False)
conn.execute("ATTACH 'host={} user={} password={} port=3306 database={}' AS sdb (TYPE mysql_scanner)".format(sourceserver,dbuser,dbpw,sourcedb))

query="select col1, col2 from mysql_table"

df1=conn.execute("select * from mysql_query('sdb', 'select col1, col2 from mysql_table')").pl()
print(df1)

df2=conn.execute('select col1, col2 from sdb.mysql_table').pl()
print(df2)`

Results:
shape: (3, 2)
┌──────┬──────┐
│ col1 ┆ col2 │
│ ---  ┆ ---  │
│ i32  ┆ f64  │
╞══════╪══════╡
│ 1    ┆ null │
│ 2    ┆ null │
│ 3    ┆ null │
└──────┴──────┘
shape: (3, 2)
┌──────┬──────┐
│ col1 ┆ col2 │
│ ---  ┆ ---  │
│ i32  ┆ f64  │
╞══════╪══════╡
│ 1    ┆ 1.11 │
│ 2    ┆ 2.22 │
│ 3    ┆ 3.33 │
└──────┴──────┘ 

cmartin1968 avatar May 03 '24 13:05 cmartin1968