From bc9e742b646f8dcd3e8a35c2ecda96bebed87d3c Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 18 Dec 2009 20:09:14 +0000
Subject: [PATCH] - mysql:  a column of type TIMESTAMP now defaults to NULL if
 "nullable=False" is not passed to Column(), and no default is present. This
 is now consistent with all other types, and in the case of TIMESTAMP
 explictly renders "NULL" due to MySQL's "switching" of default nullability
 for TIMESTAMP columns. [ticket:1539]

---
 CHANGES                               |  7 +++++
 lib/sqlalchemy/dialects/mysql/base.py |  8 ++++--
 test/dialect/test_mysql.py            | 37 ++++++++++++++++++++++++---
 3 files changed, 46 insertions(+), 6 deletions(-)

diff --git a/CHANGES b/CHANGES
index 154c22a29a..fcd7a1163e 100644
--- a/CHANGES
+++ b/CHANGES
@@ -580,6 +580,13 @@ CHANGES
     - ENUM now subclasses the new generic Enum type, and also handles
       unicode values implicitly, if the given labelnames are unicode
       objects.
+    
+    - a column of type TIMESTAMP now defaults to NULL if
+      "nullable=False" is not passed to Column(), and no default
+      is present. This is now consistent with all other types, 
+      and in the case of TIMESTAMP explictly renders "NULL" 
+      due to MySQL's "switching" of default nullability
+      for TIMESTAMP columns. [ticket:1539]
       
 - oracle
     - unit tests pass 100% with cx_oracle !
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 01f8b13a7a..e0610ec7eb 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1318,10 +1318,14 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
         default = self.get_column_default_string(column)
         if default is not None:
             colspec.append('DEFAULT ' + default)
-
-        if not column.nullable:
+        
+        is_timestamp = isinstance(column.type, sqltypes.TIMESTAMP)
+        if not column.nullable and not is_timestamp:
             colspec.append('NOT NULL')
 
+        elif column.nullable and is_timestamp and default is None:
+            colspec.append('NULL')
+
         if column.primary_key and column.autoincrement:
             try:
                 first = [c for c in column.table.primary_key.columns
diff --git a/test/dialect/test_mysql.py b/test/dialect/test_mysql.py
index 000bb131bf..4a41977253 100644
--- a/test/dialect/test_mysql.py
+++ b/test/dialect/test_mysql.py
@@ -12,7 +12,7 @@ from sqlalchemy.dialects.mysql import base as mysql
 from sqlalchemy.test.testing import eq_
 from sqlalchemy.test import *
 from sqlalchemy.test.engines import utf8_engine
-
+import datetime
 
 class TypesTest(TestBase, AssertsExecutionResults, AssertsCompiledSQL):
     "Test MySQL column types"
@@ -417,9 +417,9 @@ class TypesTest(TestBase, AssertsExecutionResults, AssertsCompiledSQL):
         try:
             columns = [
                 ([TIMESTAMP],
-                 'TIMESTAMP'),
+                 'TIMESTAMP NULL'),
                 ([mysql.MSTimeStamp],
-                 'TIMESTAMP'),
+                 'TIMESTAMP NULL'),
                 ([mysql.MSTimeStamp,
                   DefaultClause(sql.text('CURRENT_TIMESTAMP'))],
                  "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
@@ -451,6 +451,35 @@ class TypesTest(TestBase, AssertsExecutionResults, AssertsCompiledSQL):
         finally:
             meta.drop_all()
 
+    def test_timestamp_nullable(self):
+        meta = MetaData(testing.db)
+        ts_table = Table('mysql_timestamp', meta,
+                            Column('t1', TIMESTAMP),
+                            Column('t2', TIMESTAMP, nullable=False),
+                    )
+        meta.create_all()
+        try:
+            # there's a slight assumption here that this test can
+            # complete within the scope of a single second.
+            # if needed, can break out the eq_() just to check for
+            # timestamps that are within a few seconds of "now" 
+            # using timedelta.
+            
+            now = testing.db.execute("select now()").scalar()
+            
+            # TIMESTAMP without NULL inserts current time when passed
+            # NULL.  when not passed, generates 0000-00-00 quite
+            # annoyingly.
+            ts_table.insert().execute({'t1':now, 't2':None})
+            ts_table.insert().execute({'t1':None, 't2':None})
+            
+            eq_(
+                ts_table.select().execute().fetchall(),
+                [(now, now), (None, now)]
+            )
+        finally:
+            meta.drop_all()
+            
     def test_year(self):
         """Exercise YEAR."""
 
@@ -711,7 +740,7 @@ class ReflectionTest(TestBase, AssertsExecutionResults):
             Column('c2', String(10), DefaultClause('0')),
             Column('c3', String(10), DefaultClause('abc')),
             Column('c4', TIMESTAMP, DefaultClause('2009-04-05 12:00:00')),
-            Column('c5', TIMESTAMP, ),
+            Column('c5', TIMESTAMP),
             
         )
 
-- 
2.47.3