From 5347be31c98a3627c2466f1891b7c0e80bf29ed9 Mon Sep 17 00:00:00 2001 From: indivar Date: Mon, 16 Oct 2023 14:53:04 -0400 Subject: [PATCH] fix(#10056): keep nullable as true for mariadb generated columns mariaDB does not support setting NOT NULL for generated column ref: https://mariadb.com/kb/en/generated-columns/#statement-support added a check in `get_column_specification` for mariadb, checking if user has not specified nullable set it as True for computed column, but if user has explicitly set as False raise a compile error. added testcase for same ### Description ### Checklist This pull request is: - [x] A short code fix - please include the issue number, and create an issue if none exists, which must include a complete example of the issue. one line code fixes without an issue and demonstration will not be accepted. - Please include: `Fixes: #` in the commit message - please include tests. one line code fixes without tests will not be accepted. **Have a nice day!** Closes: #10354 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/10354 Pull-request-sha: 68035e0f77884f361bdcad0514ea731814cb1a34 Change-Id: I00188cf899c0a2efe759e06e510243fbdb1a6dcf --- doc/build/changelog/unreleased_20/10056.rst | 8 ++++ lib/sqlalchemy/dialects/mysql/base.py | 9 +++- test/dialect/mysql/test_query.py | 50 +++++++++++++++++++++ 3 files changed, 66 insertions(+), 1 deletion(-) create mode 100644 doc/build/changelog/unreleased_20/10056.rst diff --git a/doc/build/changelog/unreleased_20/10056.rst b/doc/build/changelog/unreleased_20/10056.rst new file mode 100644 index 0000000000..d801e98eac --- /dev/null +++ b/doc/build/changelog/unreleased_20/10056.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: bug, sql + :tickets: 10056 + + mariaDB does not support setting NOT NULL for generated column + ref: https://mariadb.com/kb/en/generated-columns/#statement-support + added a check in `get_column_specification` for mariadb, checking + if user has not specified nullable set it as True for computed column. diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index d3f2a3ff87..465f3597cb 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1063,6 +1063,7 @@ from ...sql import roles from ...sql import sqltypes from ...sql import util as sql_util from ...sql.compiler import InsertmanyvaluesSentinelOpts +from ...sql.schema import SchemaConst from ...types import BINARY from ...types import BLOB from ...types import BOOLEAN @@ -1071,6 +1072,7 @@ from ...types import UUID from ...types import VARBINARY from ...util import topological + SET_RE = re.compile( r"\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\w", re.I | re.UNICODE ) @@ -1766,7 +1768,12 @@ class MySQLCompiler(compiler.SQLCompiler): class MySQLDDLCompiler(compiler.DDLCompiler): def get_column_specification(self, column, **kw): """Builds column DDL.""" - + if ( + self.dialect.is_mariadb is True + and column.computed is not None + and column._user_defined_nullable is SchemaConst.NULL_UNSPECIFIED + ): + column.nullable = True colspec = [ self.preparer.format_column(column), self.dialect.type_compiler_instance.process( diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py index 921b5c52ba..9cbc38378f 100644 --- a/test/dialect/mysql/test_query.py +++ b/test/dialect/mysql/test_query.py @@ -4,14 +4,20 @@ from sqlalchemy import any_ from sqlalchemy import Boolean from sqlalchemy import cast from sqlalchemy import Column +from sqlalchemy import Computed +from sqlalchemy import exc from sqlalchemy import false from sqlalchemy import ForeignKey from sqlalchemy import Integer +from sqlalchemy import MetaData from sqlalchemy import or_ +from sqlalchemy import schema from sqlalchemy import select from sqlalchemy import String from sqlalchemy import Table from sqlalchemy import true +from sqlalchemy.testing import assert_raises +from sqlalchemy.testing import combinations from sqlalchemy.testing import eq_ from sqlalchemy.testing import expect_warnings from sqlalchemy.testing import fixtures @@ -255,3 +261,47 @@ class AnyAllTest(fixtures.TablesTest): stmt = select(4 == any_(select(stuff.c.value).scalar_subquery())) is_(connection.execute(stmt).scalar(), True) + + +class ComputedTest(fixtures.TestBase): + __only_on__ = "mysql >= 5.7", "mariadb" + __backend__ = True + + @combinations( + (True), + (False), + (None), + ("unset"), + argnames="nullable", + ) + def test_column_computed_for_nullable(self, connection, nullable): + """test #10056 + + we want to make sure that nullable is always set to True for computed + column as it is not supported for mariaDB + ref: https://mariadb.com/kb/en/generated-columns/#statement-support + + """ + m = MetaData() + kwargs = {"nullable": nullable} if nullable != "unset" else {} + t = Table( + "t", + m, + Column("x", Integer), + Column("y", Integer, Computed("x + 2"), **kwargs), + ) + if connection.engine.dialect.name == "mariadb" and nullable in ( + False, + None, + ): + assert_raises( + exc.ProgrammingError, + connection.execute, + schema.CreateTable(t), + ) + # If assertion happens table won't be created so + # return from test + return + # Create and then drop table + connection.execute(schema.CreateTable(t)) + connection.execute(schema.DropTable(t)) -- 2.47.2