[TYPO3-dev] DBAL suggesion

[marcel.gsteiger at milprog.ch]

Martin Kutschker schrieb:
> marcel.gsteiger at milprog.ch schrieb:
> 
>> Hi all
>>
>> I have posted the following suggestion on the users list, but probably 
>> dev would be more appropriate:
> 
> 
> Or even better file a bug on bugs.typo3.org for project "tx_dbal".
> 
>> To my understanding, inserting NULL in a single value based INSERT 
>> statement is nonsense anyway.
> 
> 
> Why? Setting a column to NULL is perfectly valid.
> 
> Masi
you are right, but consider:

<PostgreSQL Manual>
A column can be assigned a default value. When a new row is created and 
no values are specified for some of the columns, the columns will be 
filled with their respective default values. A data manipulation command 
can also request explicitly that a column be set to its default value, 
without having to know what that value is. (...)

If no default value is declared explicitly, the default value is the 
null value. This usually makes sense because a null value can be 
considered to represent unknown data.
</PostgreSQL Manual>

Now when NULL represents unknown data, so why specify a column in an 
insert statement but then say 'i don't know what to insert' by 
specifying NULL (i.e., unknown data)? One could easily leave out this 
column altogether in the INSERT statement. But then, of course, the 
database inserts the DEFAULT value instead (which might be different 
from NULL and not what you wanted).

I can't imagine a case where I would want to specify a DEFAULT value for 
a column but allow that same column to be NULL at the same time. In 
other words, if a INSERT statement does not mention that column, it 
would then get the default value, but only when the INSERT contains the 
column it would be possible to put in the NULL value. To avoid this 
situation, all my columns that have a DEFAULT specified are NOT NULL. In 
other words, I am using DEFAULT only as a means to specify something 
else than NULL as the default value. Personally I would consider any 
other use of DEFAULT a questionable practice.

The tables causing these errors have all columns NOT NULL, so it would 
be illegal to specify NULL in the inserts. But obviously something weird 
is going on in the DBAL, I don't really understand everything yet. When 
I replace "null" by "default" the caching/indexing mechanism ceases to 
work. I think this has something to do with the native vs. non-native 
access paths, together with the logic used to store BLOBs. That SQL 
errors always happen when BLOBS that are declared NOT NULL should 
receive NULL. I will try to dive further into the issue to find out 
what's going on here.

Regards
--Marcel