Database smallint NULL fields gets initialized with 0

[hmb]

Consider the following DCA segment:

    'year' => array
    (
      'label'                   => &$GLOBALS['TL_LANG']['tl_something']['year'],
      'default'                 => null,
      'exclude'                 => true,
      'sorting'                 => true,
      'inputType'               => 'text',
      'eval'                    => array('maxlength'=>4, 'minlength'=>4, 'rgxp'=>'digit', 
                                         'feEditable'=>true, 'feViewable'=>true, 
                                         'feGroup'=>'contact', 'tl_class'=>'w50'),
      'sql'                     => "smallint(5) unsigned NULL"
    ),

The db-field shall contain either NULL or a 4 digit year, e.g. 2013. When saving the record and entering nothing, essentially leaving the field blank, Contao stores a literal 0 in the field. When reopening the record it contains the 0 and may not be saved again, because of the 4 digit restriction.

Programmatically 0 and NULL is something very different and should not be replaced or set equal.

[aschempp]

You should not set a "default" value in your DCA, then it will certainly be NULL

[hmb]

This unfortunately gives the same behaviour:

 'test' => array
 (
  'label'                   => &$GLOBALS['TL_LANG']['tl_something']['year'],
  'exclude'                 => true,
  'sorting'                 => true,
  'inputType'               => 'text',
  'eval'                    => array('maxlength'=>4, 'minlength'=>4, 'rgxp'=>'digit', 
                                     'feEditable'=>true, 'feViewable'=>true,
                                     'feGroup'=>'contact', 'tl_class'=>'w50'),
  'sql'                     => "smallint(5) unsigned NULL"
 )

I Also tried:

  'default'                 => '',

Which has no effect. I suppose it only sets the default for a completely new record. Only, if I change the database entry to e.g. char then it works:

'sql'                     => "char(4) NULL"

[leofeyer]

I cannot reproduce the issue.

        'date' => array
        (
            'label'                   => &$GLOBALS['TL_LANG']['tl_news']['date'],
            'default'                 => null,
            'exclude'                 => true,
            'filter'                  => true,
            'sorting'                 => true,
            'flag'                    => 8,
            'inputType'               => 'text',
            'eval'                    => array('rgxp'=>'date', 'doNotCopy'=>true, 'datepicker'=>true, 'tl_class'=>'w50 wizard'),
            'sql'                     => "int(10) unsigned NULL"
        ),

When I click "new news item", a new database record is added with the date field set to NULL.

[tristanlins]

As far as I remember, not all numeric types are nullable...

[hmb]

Yep, you use a rgxp of "date", not "digit". And the issue does not arise whe clicking "new item". It arises when when you click "save" on an item. Remove all text from the input field, click "save" and the fields contains '0' (Zero digit).

[backbone87]

It does not matter if you use date or digit.
In MySQL SET myintegerfield = '' will set the field to 0 and not NULL because of autoconversion. The Textfield widget always returns a string (date conversion is done DC_Table level), so without a save callback mapping the empty string to NULL you cannot achieve this.

[hmb]

Well that's the whole point. I have this issue in various fields since upgrading from LTS to 3.1 The above example is only one problem. Apparently the new version can't handle NULL properly. In DB semantics the difference between 0 (zero) and NULL is essential. If this won't be fixed, I have to rewrite all my SQL queries or use save callbacks in order to get it working again. Or stay at 2.11 which is not an option.

If for instance you do:

 select id,firstname,lastname,fid from tl_children where fid<>30;

the NULL records get omitted automatically. The 0 (zero) records won't. As a consequence you get lot's of records that you did not get previously. In the current case this is even worse, as the field is a foreign key into another table. So I suddenly have FKs in my table that don't exist in the referenced table, thus breaking referential integrity, which in contao is (well should be) enforced on application level, not by database triggers.

The DCA in this case looks like this:

    'fid' => array
    (
      'label'                   => &$GLOBALS['TL_LANG']['tl_children']['fid'],
      'exclude'                 => true,
      'filter'                  => true,
      'flag'                    => 11,
      'inputType'               => 'select',
      'foreignKey'              => 'tl_member.CONCAT(firstname, \' \', lastname)',
      'eval'                    => array('chosen'=>true, 'includeBlankOption'=>true, 'tl_class'=>'w50'),
      'sql'                     => "int(10) unsigned NULL"
    ),

In V2.11 when selecting the blank option the field got set to NULL. Now it is set to 0 (zero).

And Leo on which version did you test?

I just took your above example and when I press "save" I get 0 within the database and "01.01.1970" within the backend. I'm talking about the current 3.1. master.

Cheers Holger

[backbone87]

Your example should save 0 insteadof NULL in 2.11, too, at least AFAIK.
Contao never saves NULL without save_callbacks from my experience.

I use callbacks like these:

public function saveNullIfEmpty($value) {
  return strlen($value) ? $value : null;
}

(together with eval.alwaysSave = true)

This works definitly in 2.11 and should work also in 3.x

[hmb]

Yep thanks backbone87, I don't know, just went through the git repo. I did not find that I changed something between 2.11 and 3.1.

What was confusing me, that Leo said it works and I could not get it to work.

Thanks anyway the save_callback did the trick. Saved my weekend.

[leofeyer]

@contao/workgroup-core Should we add a "nullIfEmpty" flag to the DCA?

[backbone87]

should be implemented on widget level (eval param)

[aschempp]

Honestly the only place I use NULL values is for text or blob fields (like the core does). And there I do not care about it.

As @backbone87 mentioned, it can already be solved with one line, and wit 3.2 it will even be easier with a closure...

[leofeyer]

Implemented in 624b060.

[cyberlussi]

I have an issue with this using Contao 3.2.3. I have a BE form with a select, the NULL is intended and stored correctly in DB when selecting the blank option (-) . But when I edit again the item the select drop-down selects "0" instead of "-" (I use NULL value to indicate that the value is not selected by a user, then I can overwrite it later by global or module settings, value 0 means "all").

It seems that saving NULL works, but the Widget in BE form is not setting "selected" correctly when editing.

My DCA snippet:

        'perRow' => array
        (
            'label'                   => &$GLOBALS['TL_LANG']['foobar']['perRow'],
            'exclude'                 => true,
            'inputType'               => 'select',
            'options'                 => array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12),
            'default'                 => null,
            'eval'                    => array('includeBlankOption'=>true, 'nullIfEmpty'=>true, 'tl_class'=>'w50'),
            'sql'                     => "smallint(5) unsigned NULL"
        ),

[steiha]

The "nullIfEmpty" flag is not in documentation yet.
@see https://contao.org/de/manual/3.3/data-container-arrays.html#referenz

Learned about it by digging through the code.
And I was delighted that I found it.
As @hmb stated, the ability to store NULL values is really essential.
Otherwise the CMS would be broken.

Could be further improved if this behaviour was default.
Or set implicit by the definition of the field. (e.g. "sql" => "int(10) unsigned NULL").

[aschempp]

@steiha it would be great if you could make a pull request for the documentation or at least open an issue at https://github.com/contao/docs/

[leofeyer]

Or set implicit by the definition of the field. (e.g. "sql" => "int(10) unsigned NULL")

I have created a separate ticket for this: contao/contao#17