Canner
MYSQL 'YEAR' type doesn't map to a Wren data type
I'm using the MySQ sample data set called "Sakila" to play with WrenAI.
Wren doesn't have a mapping for the "YEAR" type and so gives the internal encoding when creating queries
eg it prints -42 for the year 2006; 2006 mod 256 is 214, and 214 as a signed byte is -42
To Reproduce
- Install the container https://github.com/sakiladb/mysql
- You will need to shell in to the container to change the password, since there is an incompatibility between the encryption libraries used between Wren and this container. Use this command in the container:
ALTER USER 'sakila'@'%' IDENTIFIED WITH mysql_native_password BY 's4k1l4';- In the launcher create a new model with a MySQL data source.
- Use these values:
- Host: host.docker.internal
- Port: 55000
- User: sakila
- Password: s4k1l4
- Database: sakila
- Use the default mappings
Issue this query: "List all film names and their year of release"
You'll get all the film names but the year will be shown as -42
Expected behavior The correct year, 2006
Desktop (please complete the following information):
- OS: Mac M3 Pro
- Browser : Firefox
Wren AI Information
- Version: 0.19.1
Additional context Add any other context about the problem here.
I can provide logs upon request if relevant.
_transform_column_type in wren-engine/ibis-server/app/model/metadata/mssql.py shows where the mapping for 'YEAR' would be, and it's tempting to add one in and see what happens. However I can't help thinking that this ought to be mappable in the MDL layer. I tried to add a calculated field to perform the mapping but that wasn't possible.
After a few attempts, I did actually get the AI to make the mapping itself!! Not practical but oh boy that's very cool!
After a few attempts, I did actually get the AI to make the mapping itself!! Not practical but oh boy that's very cool!
@davedawkins interesting! Would you like to share how did you fix this issue?
I wouldn't say it's fixed, I used that prompt from the screenshot to tell the AI to make the mapping. It's not a good use of AI resource I'd say :)
Had a similar issue, my "fix" was to insert a similar kind of prompt in the column description. This way, the user does not have to mention it every time they want to query.
@davedawkins @Gautam-J you could try our "instructions" functionality
FYI https://docs.getwren.ai/oss/guide/knowledge/instructions
@davedawkins @Gautam-J you could try our "instructions" functionality
FYI https://docs.getwren.ai/oss/guide/knowledge/instructions
I guess this is what I was looking for, but I was trying to edit the field metadata, or calculate a derived field.
Thank you.
Had a similar issue, my "fix" was to insert a similar kind of prompt in the column description. This way, the user does not have to mention it every time they want to query.
That is brilliant.