Unexpected warning for computed column when migrating with MariaDB

Describe the bug

Warning is emitted when generating migration for model with computed column.

Expected behavior

No warning emitted.

To Reproduce

Code

# app.py

import os

import flask
import flask_sqlalchemy as fsa
import flask_migrate as fm
import sqlalchemy as sa


app = flask.Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    f'mariadb+pymysql://{os.environ['DB_USER']}:{os.environ['DB_PASSWORD']}'
    '@localhost:{os.environ['DB_PORT']}/{os.environ['DB_NAME']}'
)

db = fsa.SQLAlchemy(app)
migrate = fm.Migrate(app, db)

class RecordWithComputed(db.Model):
    id = sa.Column(sa.Integer, primary_key=True)
    created_at = sa.Column(sa.DateTime, nullable=False)
    deleted_at = sa.Column(sa.DateTime)

    is_active_record = sa.Column(sa.Boolean, sa.Computed('CASE WHEN deleted_at IS NULL THEN TRUE ELSE NULL END'))

Steps to reproduce

1. Create and apply first migration.
2. Try creating second migration.

Error

Output for first migration

flask db migrate -m "first migration"
INFO  [alembic.runtime.migration] Context impl MariaDBImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'record_with_computed'
  Generating /Users/eganma/junk/computed-column-warning/migrations/versions/15d57df5db98_first_migration.py ...  done

Output for second migration (no changes made to app.py)

flask db migrate -m "second migration"
INFO  [alembic.runtime.migration] Context impl MariaDBImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
/Users/eganma/Library/Caches/pypoetry/virtualenvs/computed-column-warning-swI5pdsd-py3.12/lib/python3.12/site-packages/alembic/autogenerate/compare.py:1034: UserWarning: Computed default on record_with_computed.is_active_record cannot be modified
  util.warn("Computed default on %s.%s cannot be modified" % (tname, cname))
INFO  [alembic.env] No changes in schema detected.

Strings being compared at compare.py:1029

rendered_metadata_default = 'casewhendeleted_atisnullthentrueelsenullend'
rendered_conn_default = 'casewhendeleted_atisnullthen1elsenullend'

Versions.

• OS: macOS
• Python: 3.12.0
• Alembic: 1.14.1
• SQLAlchemy: 2.0.38
• Database: MariaDB 10.11.6
• DBAPI: pymysql 1.1.1

Additional context

Doesn't look like this is causing problems for me at this time, but previous reports I found (#1151, #1462) were focused on postgres. Issue seems to stem from TRUE being reported as the boolean equivalent 1 when comparing the model against the schema.

[zzzeek]

did you try changing your model to use "1" instead of "TRUE"?

@CaselIT any suggestions?

[CaselIT]

not really. this is surprising though, since mysql/mariadb should return the sql that was executed when a table is reflected, contrary to postgresql where it generates the sql from its internal representation.

The fact that true becomes 1 seems to indicate that it's not really always the case

I did. That does get rid of the warning, and alembic reports a clean "no changes in schema detected" as expected.

Again, this is not a stopping me from doing anything. Just thought it would be useful to let you know.

[CaselIT]

if true reliably becomes 1 I guess it could be added as a normalization rule.

Re-ran the migration to compare what is emitted with echo=True with what show create table ... returns.

Migration file

"""asdf

Revision ID: 4f0b24eb20f6
Revises: 
Create Date: 2025-02-11 15:04:16.599130

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '4f0b24eb20f6'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('record_with_computed',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('deleted_at', sa.DateTime(), nullable=True),
    sa.Column('is_active_record', sa.Boolean(), sa.Computed('CASE WHEN deleted_at IS NULL THEN TRUE ELSE NULL END', ), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('record_with_computed')
    # ### end Alembic commands ###

SQLAlchemy

flask db upgrade
INFO  [sqlalchemy.engine.Engine] SELECT DATABASE()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] SELECT @@sql_mode
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] SELECT @@lower_case_table_names
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [alembic.runtime.migration] Context impl MariaDBImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] DESCRIBE `migrations`.`alembic_version`
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] DESCRIBE `migrations`.`alembic_version`
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL, 
        CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO  [alembic.runtime.migration] Running upgrade  -> 4f0b24eb20f6, asdf
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE record_with_computed (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        created_at DATETIME NOT NULL, 
        deleted_at DATETIME, 
        is_active_record BOOL GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN TRUE ELSE NULL END), 
        PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00005s] {}
INFO  [sqlalchemy.engine.Engine] INSERT INTO alembic_version (version_num) VALUES ('4f0b24eb20f6') RETURNING alembic_version.version_num
INFO  [sqlalchemy.engine.Engine] [generated in 0.00008s] {}
INFO  [sqlalchemy.engine.Engine] COMMIT

MariaDB

CREATE TABLE `record_with_computed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `is_active_record` tinyint(1) GENERATED ALWAYS AS (case when `deleted_at` is null then 1 else NULL end) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci