DDC-525: STI Fields null vs not null behaviour #654

New Issue

admin · 2026-01-22T12:45:43+01:00

admin commented

2026-01-22 12:45:43 +01:00

Originally created by @doctrinebot on GitHub (Apr 16, 2010).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user @beberlei:

What is the expected behaviour for fields on STI sub-classes?

Should they always be DEFAULT NULL?
Should the EntityPersister always write a default value to them?

A user reported on IRC that he had issues with Sqlite inserting null values (failing) vs Mysql which inserted empty strings.

Originally created by @doctrinebot on GitHub (Apr 16, 2010). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user @beberlei: What is the expected behaviour for fields on STI sub-classes? - Should they always be DEFAULT NULL? - Should the EntityPersister always write a default value to them? A user reported on IRC that he had issues with Sqlite inserting null values (failing) vs Mysql which inserted empty strings.

admin added the Bug label 2026-01-22 12:45:43 +01:00

admin closed this issue

2026-01-22 12:45:43 +01:00

admin commented

2026-01-22 12:45:44 +01:00

@doctrinebot commented on GitHub (Apr 16, 2010):

Comment created by @beberlei:

beberlei; final note before i move to other issues: I found that MySQL puts empty strings in columns that are not specified in the column list of the query, vs sqlite that puts nulls into non-specified columns. That explains a lot of it..

@doctrinebot commented on GitHub (Apr 16, 2010): Comment created by @beberlei: <mluiten> beberlei; final note before i move to other issues: I found that MySQL puts empty strings in columns that are not specified in the column list of the query, vs sqlite that puts nulls into non-specified columns. That explains a lot of it..

admin commented

2026-01-22 12:45:44 +01:00

@doctrinebot commented on GitHub (Apr 16, 2010):

Comment created by mluiten:

Said user reporting in :-)

Found this extremely annoying mostly due to inconsistent behavior between different drivers. I just found that MySQL inserts empty strings ('') into columns not specified in the INSERT query, while SQLite inserts NULLs. I'd say SQLite is technically correct..

E.g. a superclass Collector (id, name) which has a subclass SpecialCollector, with the column 'subdomain' added (nullable=false).

CREATE TABLE Collector (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name VARCHAR(255) NOT NULL, subdomain VARCHAR(255) NOT NULL)
// Mysql
INSERT INTO Collector (id, name) VALUES (1, 'foo'); // Works fine... subdomain value in new row = '' (empty string)
INSERT INTO Collector(id, name, subdomain) VALUES (1, 'foo', NULL); // Error, subdomain may not be NULL
// SQLite
INSERT INTO Collector (id, name) VALUES (1, 'foo'); // Error, subdomain may not be NULL
INSERT INTO Collector(id, name, subdomain) VALUES (1, 'foo', NULL); // Error, subdomain may not be NULL

When creating a subclass without the subdomain field, Doctrine creates an insert query without subdomain and thus works fine. SQLite however, gives an error.

Possible solutions would be to include every column of subclasses to the query and hard-set to empty string, or to imply NOT NULL on all columns of subclasses during table creation, and check for NULL in the DBAL instead. But I'll let the big boys figure that out ;-)

@doctrinebot commented on GitHub (Apr 16, 2010): Comment created by mluiten: Said user reporting in :-) Found this extremely annoying mostly due to inconsistent behavior between different drivers. I just found that MySQL inserts empty strings ('') into columns not specified in the INSERT query, while SQLite inserts NULLs. I'd say SQLite is technically correct.. E.g. a superclass Collector (id, name) which has a subclass SpecialCollector, with the column 'subdomain' added (nullable=false). ``` CREATE TABLE Collector (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name VARCHAR(255) NOT NULL, subdomain VARCHAR(255) NOT NULL) // Mysql INSERT INTO Collector (id, name) VALUES (1, 'foo'); // Works fine... subdomain value in new row = '' (empty string) INSERT INTO Collector(id, name, subdomain) VALUES (1, 'foo', NULL); // Error, subdomain may not be NULL // SQLite INSERT INTO Collector (id, name) VALUES (1, 'foo'); // Error, subdomain may not be NULL INSERT INTO Collector(id, name, subdomain) VALUES (1, 'foo', NULL); // Error, subdomain may not be NULL ``` When creating a subclass without the subdomain field, Doctrine creates an insert query without subdomain and thus works fine. SQLite however, gives an error. Possible solutions would be to include every column of subclasses to the query and hard-set to empty string, or to imply NOT NULL on all columns of subclasses during table creation, and check for NULL in the DBAL instead. But I'll let the big boys figure that out ;-)

admin commented

2026-01-22 12:45:45 +01:00

@doctrinebot commented on GitHub (Apr 16, 2010):

Comment created by romanb:

Columns mapped by child classes in single table inheritance must be nullable.

The behavior of mysql looks weird to me, too.

@doctrinebot commented on GitHub (Apr 16, 2010): Comment created by romanb: Columns mapped by child classes in single table inheritance must be nullable. The behavior of mysql looks weird to me, too.

admin commented

2026-01-22 12:45:47 +01:00

@doctrinebot commented on GitHub (Apr 26, 2010):

Comment created by romanb:

So do we have an issue here, assuming columns mapped by child classes in STI must be nullable? If yes what is the problem?

@doctrinebot commented on GitHub (Apr 26, 2010): Comment created by romanb: So do we have an issue here, assuming columns mapped by child classes in STI must be nullable? If yes what is the problem?

admin commented

2026-01-22 12:45:47 +01:00

@doctrinebot commented on GitHub (Sep 27, 2010):

Comment created by @beberlei:

The problem is that currently you had to do nullable=true on all the fields in chield entities of STI.

Now SchemaTool doe this automatically.

@doctrinebot commented on GitHub (Sep 27, 2010): Comment created by @beberlei: The problem is that currently you had to do nullable=true on all the fields in chield entities of STI. Now SchemaTool doe this automatically.

admin commented

2026-01-22 12:45:47 +01:00

@doctrinebot commented on GitHub (Sep 27, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Sep 27, 2010): Issue was closed with resolution "Fixed"

admin referenced this issue

2026-01-22 16:00:15 +01:00

[PR #654] [CLOSED] Ilike #8510

admin referenced this issue

2026-01-22 16:00:16 +01:00

[PR #654] Ilike #8515

Sign in to join this conversation.