- 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]

3 files changed, 46 insertions, 6 deletions

diff --git a/CHANGES b/CHANGES
index 154c22a29..fcd7a1163 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 01f8b13a7..e0610ec7e 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 000bb131b..4a4197725 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),
             
         )