NULL int in database is read as 0 by koolreport

Eugene commented on Aug 1, 2019

Hi,

I have int field im my database table. This field can has NULL value.

Why when i do a query via $this->src(...')->query($query)->pipe($this->dataStore('myDS')); I get 0 for NULL values?

KoolReport commented on Aug 1, 2019

Hi Eugene,

How do you know the NULL is converted to 0, do you use Table to display. If you use Table to display then the Table does use function number_format so the NULL will be converted to 0 to display by default. You can avoid this by setting your own "formatValue" function to handle the display, for example display nothing when value is NULL.

Eugene commented on Aug 1, 2019

I do var_damp for my datastore and for string field that has null value i get null but for int fields i get 0 already in datastore.

KoolReport commented on Aug 1, 2019

Could you please send me the result of

var_dump($this->dataStore("myDS")->get(0)); //Change the 0 to the row number that has null value for int.

I would like to see.

Also, please tell me which DataSource are you using? Is it default PDO or others.

Eugene commented on Aug 1, 2019

Yes I use a default PDO ( I did not set any special class)

array (size=13)
  'date' => string '01/07/2019' (length=10)
  'shift_id' => int 1453
  'invoice_No' => int 18057290
  'amount' => int 594000
  'method' => string 'DEBIT CARD' (length=10)
  'transactionType' => string 'DEB Card  - BCA EDC' (length=19)
  'bankRate' => int 0
  'bank' => string 'BCA' (length=3)
  'payment_date' => null
  'ref_no' => null                     <-------------- HAS NULL in DB and type varchar
  'paidAmount' => int 0        <-------------- HAS NULL in DB and type bigint
  'type' => string 'DEB Card - BCA EDC' (length=18)
  'matched' => int 0

KoolReport commented on Aug 1, 2019

Please check the name of column in your query, I see in your database is "paid_amount" but in datastore is "paidAmount"

Eugene commented on Aug 1, 2019

a part of my SELECT

,gpm.paid_amount as paidAmount

so the names are correct

Eugene commented on Aug 1, 2019

If i change only the type of paid_amount to varchar i get NULL in datastore if it has bigint type - i get 0.

KoolReport commented on Aug 1, 2019

I've checked with dev.team and you are right, the NULL of number column has been converted to 0. It is not a bug but you want to make it correct NULL, then you can remove those lines of code in PdoDataSource (496):

            foreach ($numberColumnList as $cName) {
                    $row[$cName]+=0;
            }

Eugene commented on Aug 1, 2019

I would not say so unequivocally that this is not a bug. :-) This conversion was a surprise for you also as I see so It can not be called as an evident feature... For me, it is more bug than anything else.

This conversion means that koolreport engine makes a decision instead of me... I think it is not correct from the general point of view - NULL value and 0 value always have a different meaning, so it does not look like a good idea to convert NULL to 0 unconditionally. To continue this logic, you can convert NULL value for the strings to '', but you don't do it...

Sure I can edit the koolreport core code, but it means I have to remember it and do the same for every new version of koolreport. I prefer not to have cases like this.

Could I know about your plans? Are you going to keep this case as it is or maybe add an option to give a possibility for the programmers to have control over this conversion?

KoolReport commented on Aug 1, 2019

Yes, you are right, I argued with them with the same reason as you did. Sorry that I am totally unaware of behaviour. What they told me is that in the beginning they did not do any conversion, but due to data errorness (null) and at that time there is no DataClean package so to make things easier for users, they add the conversion ( they argued that most of user consider null as 0 in calculation). The NULL in number caused trouble for process like CalculatedColumn. I am not really convinced so I marked your topic as suggestion and submitted your case to Karl. He is considering two options:

Adding an option to PdoDataSource to remove the conversion when needed. Please understand the changes could cause trouble to many users.
Make it right (remove the conversion) and try todo the conversion in those processes like CalculatedColumn to minimize side effect.

Surely we will have a solution.

Eugene commented on Aug 1, 2019

I agree if I need to calculate something null is very often 0 but not always

As for me the current situation makes a bit complicated the realisation the following logic:

when this value exist i have to use it including the case if it equals 0
but if this value does not exist i have to use a default value and it could be not 0... so the current conversion add some problems for me... and it is only one example...

I hope you will find a solution. Thank you

Eugene commented on Nov 19, 2019

It looks like the case is resolved in the last version

Thanks a lot.

KoolReport's Forum

NULL int in database is read as 0 by koolreport #1027

Build Your Excellent Data Report