View Issue Details

IDProjectCategoryView StatusLast Update
0007052OXID eShop (all versions)4.01. Database handlingpublic2020-01-20 08:33
Reportertimwetter Assigned To 
PrioritylowSeverityminorReproducibilityalways
Status acknowledgedResolutionreopened 
Product Version6.2.0-rc.1 
Summary0007052: DB 'NULL' value for DB float/double types not possible
Descriptionwhy are you deleting my entrie?
so if i am wrong you can say it!

but you can't set DB NULL value for float,double types in MySQL

because of:
protected function _setFieldData($fieldName, $fieldValue, $dataType = Field::T_TEXT)
    {
        $longFieldName = $this->_getFieldLongName($fieldName);
        //$sLongFieldName = $this->_sCoreTable . "__" . strtolower($sFieldName);
        // doing this because in lazy loaded lists on first load it is harmful to have initialised fields but not yet set
        // situation: only first article is loaded fully for "select oxid from oxarticles"
        //if ($this->_blUseLazyLoading && !isset($this->$sLongFieldName))
        // return;
        //in non lazy loading case we just add a field and do not care about it more
        if (!$this->_blUseLazyLoading
            && !$this->isPropertyLoaded($longFieldName)
        ) {
            $fieldsList = $this->_getAllFields(true);
            if (isset($fieldsList[strtolower($fieldName)])) {
                $this->_addField($fieldName, $this->_getFieldStatus($fieldName));
            }
        }
        // if we have a double field we replace "," with "." in case somebody enters it in european format
        $isPropertyLoaded = $this->isPropertyLoaded($longFieldName);
        if ($isPropertyLoaded
            && isset($this->$longFieldName->fldtype)
            && $this->$longFieldName->fldtype == 'double'
++ && $fieldValue !==null
        ) {
            $fieldValue = str_replace(',', '.', $fieldValue);
        }


if you think that I am wrong , please let me know and all the others, too
Steps To Reproduce1)
ALTER TABLE oxcountry
ADD COLUMN `DENIX1` int DEFAULT NULL,
ADD COLUMN `DENIX2` VARCHAR(30) DEFAULT NULL,
ADD COLUMN `DENIX3` double DEFAULT NULL

2)
insert input field for denix 1 to 3 in country:main.tpl
<tr>
    <td class="edittext">
        denix1 int<br_>
        denix2 varchar<br_>
        denix3 double
    </td>
    <td class="edittext">
        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix1->fldmax_length}]" name="editval[oxcountry__denix1]" value="[{$edit->oxcountry__denix1->value}]"[{$readonly}]><br_>
        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix2->fldmax_length}]" name="editval[oxcountry__denix2]" value="[{$edit->oxcountry__denix2->value}]"[{$readonly}]><br_>
        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix3->fldmax_length}]" name="editval[oxcountry__denix3]" value="[{$edit->oxcountry__denix3->value}]"[{$readonly}]>
    </td>
</tr>

3)
alter function save() in Application/Model/Country.php: add lines to function:
    if (isset($aParams['oxcountry__denix1']) && $aParams['oxcountry__denix1'] === '') {
        $aParams['oxcountry__denix1'] = null;
    }
    if (isset($aParams['oxcountry__denix2']) && $aParams['oxcountry__denix2'] === '') {
        $aParams['oxcountry__denix2'] = null;
    }
    if (isset($aParams['oxcountry__denix3']) && $aParams['oxcountry__denix3'] === '') {
        $aParams['oxcountry__denix3'] = null;
    }

4)
clear oxid's tmp cache && generate views

5)
open oxid admin backend and save a country

6)
SELECT DENIX1, DENIX2, DENIX3 FROM oxcountry WHERE {country you saved};

RESULT:
DENIX1 = NULL (for int/numeric value)
DENIX2 = NULL (for varchar/string)
DENIX3 = 0 (for double/numeric value)
Additional Information/vendor/oxid-esales/oxideshop-ce/source/Core/Model/BaseModel.php:1264
possible fix:
if ($isPropertyLoaded
            && isset($this->$longFieldName->fldtype)
            && $this->$longFieldName->fldtype == 'double'
            && $fieldValue
        )
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
Database VersionNot defined

Relationships

related to 0007049 closedQA DB 'NULL' value for DB float/double types not possible 

Activities

timwetter

2019-11-19 00:44

reporter   ~0013047

so again, the extra check, if new value is set to 'NULL' can not hurt performance or everythign else.
I am using php 7.3 for testing
and if I do not check this - the str_replace will causes a '0' instead of 'NULL' in DB mysql

QA

2019-11-19 08:21

administrator   ~0013048

Last edited: 2019-11-19 08:21

the bug entry was not simply closed, but set to unable to reproduce with the following comment:

Can‘t reproduce the behavior.
If I add a new column with type float NULL and add as described:

\OxidEsales\Eshop\Core\Registry::getConfig()->getRequestParameter("editval");
$aParams[oxcountry__oxnix] = 2;

Then I have OXINIX 2 in the field. If I changed the line to save with null afterwards, I also get NULL in the database field.

It seems to be more of a DB problem/configuration/non-supported DB.
Can you reproduce that with 6.1.x?

If you still have something to comment, please always reply to the first entry made, so as not to create a lot of duplicates.
https://bugs.oxid-esales.com/view.php?id=7049

- es -

timwetter

2019-11-19 09:42

reporter   ~0013049

php > $t=null;
php > echo $t===null?"y":"n";
y
php > echo (str_replace("d","c",$t)===null)?"y":"n";
n

QA

2019-11-19 10:49

administrator   ~0013050

Last edited: 2019-11-19 10:56

Hi timwetter,

to be able to reproduce the issue described by your first entry (0007049) the form of the country main template (country_main.tpl) has to be extended with the input field for the new column oxnix. This point is missing in your description. If there is an input field in the backend, then the shop will always write a 0 instead of null. As the summary of the entry 0007049 states.
But when I add your code for the method CountryMain.php then the framework will of course write NULL, as the code overwrites the value from the form. Which is contradictory to the summary as it shows that the framework is able to write NULL to float fields.

As it is not clear if you wanted to report if either the framework does not allow NULL in float fields or that the framework uses 0 instead of null, I suggest that you simply create a Pull Request for that case, as the first claim is not reproducible and the second is more a feature request than a bug: https://github.com/OXID-eSales/oxideshop_ce/blob/master/CONTRIBUTING.md

Thank you!

- MK

timwetter

2019-11-19 22:44

reporter   ~0013051

the framework does allow NULL in DB float fields, BUT saves 0 instead of null to DB!

And if you take your time and see why that is the case, then you can clearly see that this behavior was not intended.
Why should only fields of the type float not be able to assume the value 'null'?

1) this shows, that a null value as input will be converted to an empty string via str_replace
---%<----%<----%<----%<----%<----%<---
php > $t = null;
php > var_dump($t);
NULL
php > $t = str_replace('','',$t);
php > var_dump($t);
string(0) ""
php >
---%<----%<----%<----%<----%<----%<---

2)
the function _setFieldData will do the same with a $fieldValue=null and fldtype == 'double'

3)
function _getUpdateFieldValue in BaseModel:
---%<----%<----%<----%<----%<----%<---
if ((null === $fieldValue)) {
            if ($this->_canFieldBeNull($fieldName)) {
                return 'null';
---%<----%<----%<----%<----%<----%<---
function _canFieldBeNull would return true, but will never be entered, because value of $fieldValue is an empty string and !== null, but original value was null

QA

2020-01-08 15:56

administrator   ~0013088

Dear timwetter,

I’m SG and have also tried to understand this report 7052 and 7049.

I cannot replicate the problem "save 0 instead of null", if I follow your instructions as I understand it.

That would be:
A)
1, add column denix (your oxnix) as float with default null
2. change /Apllication/Controller/Admin/CountryMain::Save() as followd:
++ $aParams['oxcountry__denix'] = null;
3. save country by pressing save button.

I can reproduce your "save 0 instead of null" if I undergo the following steps:
B)
1, add column denix (your oxnix) as float with default null
2. insert input field for denix in country:main.tpl
 <tr>
                    <td class="edittext">
                        denix
                    </td>
                    <td class="edittext">
                        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix->fldmax_length}]" name="editval[oxcountry__denix]" value="[{$edit->oxcountry__denix->value}]"[{$readonly}]>

                    </td>
                </tr>
3. save country by pressing save button.

I suggest you undergo following steps, so you really can save null by submitting "" and save any valid value. I also see oxvat from oxarticles as a model:

1. create column
2. change .tpl (as described)
3. change save() :
if (isset($aParams['oxcountry__denix']) && $aParams['oxcountry__denix'] === '') {
            $aParams['oxcountry__denix'] = null;
        }
4. save per button



In Case B:
In my testing, no data-type, no text, no double got "null" as value. they were empty ("strings") or 0 ("number-based").
The reason is denix = ''
you see an '' is submitted, therefore it gets 0, because of:
http://www.sqlines.com/oracle/insert_empty_string_to_numeric_column

This behavior is not new to oxid.

Best regards

SG

timwetter

2020-01-18 22:17

reporter   ~0013091

Last edited: 2020-01-18 22:19

1)
ALTER TABLE oxcountry
ADD COLUMN `DENIX1` int DEFAULT NULL,
ADD COLUMN `DENIX2` VARCHAR(30) DEFAULT NULL,
ADD COLUMN `DENIX3` double DEFAULT NULL

2)
insert input field for denix 1 to 3 in country:main.tpl
<tr>
    <td class="edittext">
        denix1 int<br_>
        denix2 varchar<br_>
        denix3 double
    </td>
    <td class="edittext">
        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix1->fldmax_length}]" name="editval[oxcountry__denix1]" value="[{$edit->oxcountry__denix1->value}]"[{$readonly}]><br_>
        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix2->fldmax_length}]" name="editval[oxcountry__denix2]" value="[{$edit->oxcountry__denix2->value}]"[{$readonly}]><br_>
        <input type="text" class="editinput" size="10" maxlength="[{$edit->oxcountry__denix3->fldmax_length}]" name="editval[oxcountry__denix3]" value="[{$edit->oxcountry__denix3->value}]"[{$readonly}]>
    </td>
</tr>

3)
alter function save() in Application/Model/Country.php: add lines to function:
    if (isset($aParams['oxcountry__denix1']) && $aParams['oxcountry__denix1'] === '') {
        $aParams['oxcountry__denix1'] = null;
    }
    if (isset($aParams['oxcountry__denix2']) && $aParams['oxcountry__denix2'] === '') {
        $aParams['oxcountry__denix2'] = null;
    }
    if (isset($aParams['oxcountry__denix3']) && $aParams['oxcountry__denix3'] === '') {
        $aParams['oxcountry__denix3'] = null;
    }

4)
clear oxid's tmp cache

5)
open oxid admin backend and save a country

6)
SELECT DENIX1, DENIX2, DENIX3 FROM oxcountry WHERE {country you saved};

RESULT:
DENIX1 = NULL (for int/numeric value)
DENIX2 = NULL (for varchar/string)
DENIX3 = 0 (for double/numeric value)

------------------------------------
why should NULL only work for all types except double type?
please check the result of php str_replace function with NULL as input and oxids _setFieldData function

QA

2020-01-20 08:33

administrator   ~0013092

Dear Timwetter,

thanks!

I tested it again and this time i can reproduce it.

Best regards

-SG-