Opened 15 years ago

Closed 15 years ago

Last modified 15 years ago

#9862 closed (fixed)

Non-standard SQL generated in column definition for nullable columns in create table DDL

Reported by: Ambrish Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords:
Cc: gabor@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Hi,

While doing backend testing for new DB2 adapter (I am developing it), I found that in CREATE TABLE DDL generation, it contains some non-standard SQL syntax.

Here is one example of model.

class User(models.Model):
    first_name = models.CharField(max_length=20, null=False)
    last_name = models.CharField(max_length=20, null=True)

    class Meta:
        db_table = "temp_user"

For the column last_name, the SQL that gets generated is LAST_NAME VARCHAR(20) NULL. The trailing NULL is not a standard SQL. This can be validated by the SQL validator tool - http://developer.mimer.com/validator/parser200x/index.tml Needless to say, this will fail in DB2 and so too in Oracle.

I find only one method in the docs which deals with the table DDL generation BaseDatabaseCreation.sql_create_model.

Regards,
Ambrish Bhargava

Attachments (1)

9862.diff (1.3 KB ) - added by Erin Kelly 15 years ago.

Download all attachments as: .zip

Change History (9)

comment:1 by Malcolm Tredinnick, 15 years ago

Triage Stage: UnreviewedAccepted

No database on earth implements the same subset of SQL, so references to the standard, with all its optional bits and non-standard extensions in implementation, isn't too helpful either way. However, if a database doesn't support something, we should make sure it's not required. Practice always trumps theory.

In this case, the solution is probably just to not include the NULL. NOT NULL is permitted, so we include that for null=False fields and use the fact that columns should be nullable by default.

Ambrish: it's only NULL that is causing a problem on db2, right? NOT NULL is fine?

We still need to check that this works sufficiently on all the other backends (including getting some confirmation from Ramiro Morales about MS SQL). If there's an inconsistency in requirements, it will need an addition of another BaseDatabaseFeature function.

comment:2 by Ambrish, 15 years ago

Hi,


it's only NULL that is causing a problem on db2, right? NOT NULL is fine?


With DB2, only NULL is causing a problem and with NOT NULL there are no issues.

Regards,
Ambrish Bhargava

comment:3 by Gábor Farkas, 15 years ago

Cc: gabor@… added

by Erin Kelly, 15 years ago

Attachment: 9862.diff added

comment:4 by Erin Kelly, 15 years ago

Component: UncategorizedDatabase layer (models, ORM)
Has patch: set

comment:5 by Ambrish, 15 years ago

Thanks Ian...

Regards,
Ambrish Bhargava

comment:6 by Malcolm Tredinnick, 15 years ago

Owner: changed from nobody to Malcolm Tredinnick
Status: newassigned

comment:7 by Malcolm Tredinnick, 15 years ago

Resolution: fixed
Status: assignedclosed

(In [9703]) Fixed #9862 -- For better SQL portability, don't specify "NULL" on nullable
columns when creating tables. Patch from Ian Kelly.

Columns are NULL by default, so we only need to use "NOT NULL" when we want
non-default behaviour.

comment:8 by Malcolm Tredinnick, 15 years ago

(In [9704]) [1.0.X] Fixed #9862 -- For better SQL portability, don't specify "NULL" on nullable columns when creating tables. Patch from Ian Kelly.

Columns are NULL by default, so we only need to use "NOT NULL" when we want
non-default behaviour.

Backport of r9703 from trunk.

Note: See TracTickets for help on using tickets.
Back to Top