SeaQL / sea-orm

🐚 An async & dynamic ORM for Rust
https://www.sea-ql.org/SeaORM/
Apache License 2.0
6.56k stars 459 forks source link

.money_len type creates invalid SQL #2190

Closed pedrettin closed 2 months ago

pedrettin commented 2 months ago

Description

When using the .money_len type in the creation of a column on a table i get a sql error:

Execution Error: error returned from database: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'money(7, 2) NOT NULL )

Looking further into mysql data types, i dont think a MONEY type even exists? maybe that is a postgres specific feature. Additionally, I am not sure that money_len would make much sense in the context of postgres either, as it would be identical to decimal_len.

Steps to Reproduce

  1. Create a migration for a new table
  2. Add a column with type .money_len
  3. Run it programmatically using Migrator::up(&db, None).await

Expected Behavior

It should create a column that can store prices up to 99999.99

Actual Behavior

It breaks with an error indicating bad sql syntax: Execution Error: error returned from database: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'money(7, 2) NOT NULL )

Reproduces How Often

Always

Workarounds

I have been using decimal_len instead which seems to work fine.

Reproducible Example

use sea_orm_migration::prelude::*;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .create_table(
                Table::create()
                    .table(Listing::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(Listing::Id)
                            .big_unsigned()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(
                        ColumnDef::new(Listing::Name)
                            .string()
                            .not_null()
                            .unique_key(),
                    )
                    .col(
                        ColumnDef::new(Listing::Price)
                            .money_len(7, 2) // This allows for values up to 99999.99
                            .not_null(),
                    )
                    .to_owned(),
            )
            .await
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .drop_table(Table::drop().table(Listing::Table).to_owned())
            .await
    }
}

#[derive(DeriveIden)]
enum Listing {
    Table,
    Id,
    Name,
    Price,
}

Versions

sea-orm version 0.12, mysql version 8.0

tyt2y3 commented 2 months ago

Money is now mapped to decimal in MySQL in 1.0-rc. Thank you for raising