duckdb_mysql
duckdb_mysql copied to clipboard
Decimal data type conversion issue with mysql_query function
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 â
ââââââââ´âââââââ