8 replies to this topic

[Zhengguo]

Hi,

 

When I tried some mathematical operation(exp function) on one of the PK parameters out of NCA, I got error message saying "Bad conversion from DBNULL to double". There are some rows of that PK parameters are empty, which was the NULL the message refers to. 

So I tried the if function such as: 

if(isNull(PK), 0, exp(PK))

it doesn't work. However, if instead of the exp function, I use simple operation like  addition then it works. e.g.,

 if(isNull(PK), 0, PK+1)

will correctly produce the row with value 0 where the PK is NULL and the rest of the rows with correct value of PK+1.  Since exp is only operating where the PK value is not empty (returned by if function), where does the error come from?

 

Is this a bug? How to solve this issue? 

 

Thanks,

 

[Simon Davis]

Hi Zhengguo

 

  to save me some time in replication please can you attach your project file and confirm the version of Phoenix you're using so I can investigate further please.

 

  thanks, SImon.

[Zhengguo]

Hi Simon,

 

Sure. dummy project attached, with 3 data wizard operations. The version is 8.1. 

 

I think I might know what's going on here. To put it shortly, empty cell are correctly detected by function isNull() but when used in operation, it was treated as value 0, which doesn't seems right to me. Missing value should not be treated as 0. 

 

You'll find in the attached file the following: 

1. if I do simple operation like PK + 1, the returned value from empty cell is 1 , though it should be NULL (like NA in R language). so empty cell is treated as 0.
2. When I use if(isNull(PK), 0, PK+1), the returned value from empty cell is 0, as expected from if function. So the Null value is detected correctly by isNull function .
3. However, if(isNull(PK), 0, exp(PK)), error message appears. 

Attached Files

•  DBNULL.error.phxproj   198.26KB   485 downloads

Edited by Zhengguo, 30 July 2019 - 10:45 AM.

[Simon Davis]

Sorry Zhengguo, I can confirm point 3 as a bug, logged as  QC16719 .

Regarding point 1, I think in this case it is easier for most users to get the result of 1, that is what they would get in Excel and to explicitly request with IsNull if you want to do something 'cleverer', so I think these two behaviours will remain as they are.  But other comments are welcome.

 

  Simon.
 

[Zhengguo]

Hi Simon,

 

Thanks for the confirmation. 

[Helmut Schütz]

Hi Simon,
 

Sorry Zhengguo, I can confirm point 3 as a bug, logged as  QC16719 .

Regarding point 1, I think in this case it is easier for most users to get the result of 1, that is what they would get in Excel and to explicitly request with IsNull if you want to do something 'cleverer', so I think these two behaviours will remain as they are.  But other comments are welcome.

 
Well, what Excel does is not necessarily the gold standard. ;-)
In strong-typed languages like SQL and C NULL has a special meaning. Even (weak-typed) R distinguishes between NA and NULL:

MyExp      <- expression(x + 1L) # add (integer) 1 to x
res        <- data.frame(x = rep("", 2), y = rep(y, 2),
                         op = as.character(parse(text = MyExp )),
                         z = "", str = "", stringsAsFactors = FALSE)
x          <- NA
res$x[1]   <- x
z          <- eval(MyExp)
res$z[1]   <- z
res$str[1] <- deparse(z)
x          <- NULL
res$x[2]   <- x
z          <- eval(MyExp)
res$z[2]   <- z
res$str[2] <- deparse(z)
print(res, row.names = FALSE)

What we get:

   x y    op    z         str
<NA> 1 x + y <NA> NA_integer_
     1 x + y       integer(0)

Since we declared y as an integer and try to add NA, we get NA but of the hidden type NA_integer in the first case.

If we try to add NULL, we get an integer NULL. Note that NULL can’t even be printed (columns x and z). Note that we are punished by two errors essentially telling us that no calculations are done with NULL.

 

In other words, NA is carried over in computations, whereas NULL is not. NULL is simply undefined, whereas NA is R’s missing value indicator.

x <- c(1, 2, NA)
y <- x + 1
y; length(y)
[1]  2  3 NA
[1] 3

x <- c(1, 2, NULL)
y <- x + 1
y; length(y)
[1] 2 3
[1] 2

[0521]

 

Hi Simon,
 

 
Well, what Excel does is not necessarily the gold standard. ;-)
In strong-typed languages like SQL and C NULL has a special meaning. Even (weak-typed) R distinguishes between NA and NULL:

MyExp      <- expression(x + 1L) # add (integer) 1 to x
res        <- data.frame(x = rep("", 2), y = rep(y, 2),
                         op = as.character(parse(text = MyExp )),
                         z = "", str = "", stringsAsFactors = FALSE)
x          <- NA
res$x[1]   <- x
z          <- eval(MyExp)
res$z[1]   <- z
res$str[1] <- deparse(z)
x          <- NULL
res$x[2]   <- x
z          <- eval(MyExp)
res$z[2]   <- z
res$str[2] <- deparse(z)
print(res, row.names = FALSE)

What we get:

   x y    op    z         str
<NA> 1 x + y <NA> NA_integer_
     1 x + y       integer(0)

Since we declared y as an integer and try to add NA, we get NA but of the hidden type NA_integer in the first case.

If we try to add NULL, we get an integer NULL. Note that NULL can’t even be printed (columns x and z). Note that we are punished by two errors essentially telling us that no calculations are done with NULL.

 

In other words, NA is carried over in computations, whereas NULL is not. NULL is simply undefined, whereas NA is R’s missing value indicator.

x <- c(1, 2, NA)
y <- x + 1
y; length(y)
[1]  2  3 NA
[1] 3

x <- c(1, 2, NULL)
y <- x + 1
y; length(y)
[1] 2 3
[1] 2

Hi Helmut Schütz,

I think this is not a fault caused by NULL and NA differences. He is not an error of exp() function, nor is it an error of isNull, nor is it an error of if function. He is a logic error! ! !

 

For example, for "if(isNull(pk3), 0, exp(pk3))", we generally think that the program is executed like this:

1. Execute the "if (criteria statement, true statement, false statement)" statement

       2. Execute the criteria statement,

              3. Return the value of the criteria statement,

       4. If the value of the criterion statement = true,

              5. Then execute the true statement,

              6. Return the value of the true statement,

              7. Take the value of the true statement as the return value of the if statement, exit the if statement.

       8. If the value of the criterion statement = false,

              9. Then execute the false statement,

              10. Return the value of the false statement,

              11. Take the value of the false statement as the return value of the if statement, exit the if statement.

12. End if

 

But the logic of Phoenix is ??problematic, I guess he executed the statement like this."

1. Execute a false  statement,

       2. Return the value of the false statement,

3. Execute the true statement,

       4. Return the value of the true statement,

5. Execute the criteria statement,

       6. Return the value of the criteria statement,

7. Execute the if statement,

       8. If the value of the criterion statement = true,

              9. Take the value of the true statement as the return value of the if statement, exit the if statement.

       10. If the value of the criterion statement = false,

              11. Take the value of the false statement as the return value of the if statement, exit the if statement.

12. End if

 

Therefore, no matter whether the result of your judgment statement is true or not, the false statement is applied with all the data, and if there is any failure to execute, an error is reported! ! !

 

I have encountered this wonderful logic error before.

https://support.cert...wizard-replace/

 

 

So to solve this problem in the current version, it can only be solved by introducing auxiliary columns.

For example, for the current problem:

The first step: Transformation--Custom

New Column Name:pk3fl

Formula:

If(isNull(pk3), 0, pk3)

The second step: Transformation--Custom

New Column Name:

Formula:

If(isNull(pk3), 0, exp(pk3fl))

 

Sincerely,

0521

[Zhengguo]

Hi Simon,

 

Yes, it's logic error indeed. What I was expecting from the if function is exactly what you described in the first part of the explanation. I have no idea how you figured out the internal logic workflow as you said in the second part, but if that's true, the logic is obviously seriously flawed.

 

The link you provided reminded me something similar happened to me some years ago with previous version of Phoenix.  So it would seem that the if workflow hasn't been fixed all those years. Hopefully this can be fixed too in the next version. At the moment, my workaround was adding additional column, do the calculation, and delete that additional column later on. 

 

That being said, I'd like to echo what Helmut said before, Excel is far from the golden standard when dealing with data science, we can always do better. 

[0521]

In Phoenix version 8.3, the bug seems to have been fixed.

But it seems that they may have used the'on error resume next' statement.

Anyway, it is working now.

Edited by Simon Davis, 18 June 2020 - 07:03 PM.
just trimmed the repeated post