View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0007052 | OXID eShop (all versions) | 4.01. Database handling | public | 2019-11-19 00:39 | 2024-06-26 11:55 |
| Reporter | timwetter | Assigned To | |||
| Priority | low | Severity | minor | Reproducibility | always |
| Status | acknowledged | Resolution | reopened | ||
| Product Version | 6.2.0-rc.1 | ||||
| Summary | 0007052: DB 'NULL' value for DB float/double types not possible | ||||
| Description | why 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 Reproduce | 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 && 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 ) | ||||
| Tags | No tags attached. | ||||
| Theme | Not defined | ||||
| Browser | Not defined | ||||
| PHP Version | Not defined | ||||
| Database Version | Not defined | ||||
|
|
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 |
|
|
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 - |
|
|
php > $t=null; php > echo $t===null?"y":"n"; y php > echo (str_replace("d","c",$t)===null)?"y":"n"; n |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
Dear Timwetter, thanks! I tested it again and this time i can reproduce it. Best regards -SG- |
