Zero value being stored as null

[stoyleg]

I have insert & update queries for a bunch of yes/no select fields with standard values 1 for yes and 0 for no.

Yes is correctly storing the value as 1 but when I select No the 0 is not stored, instead the entry defaults to null.

I do have default(null) set up in the queries because the user must have the option to leave the select field blank, i.e the options are…

<option></option>
<option value="1">Yes</option>
<option value="0">No</option>

The data type in the database is TINYINT.

Does anyone know why the 1 is being stored but the 0 is not?

Thanks

[bpj]

0 evaluates as false so will then use the default value which you set as null

You could use something like:
somevalue == 0 ? 0 : somevalue.default(null)

[Apple]

I would hint this as a Wappler bug, because it makes no sense to cast a false to null

[bpj]

I disagree. 1 is true and 0 is false. The default function says that if the value is empty or false then use the value given in the parentheses.

[Hyperbytes]

Set your insert/ update queries with $_POST.myvalue.default(0) which forces a zero where no true value is sent.

[bpj]

It works most of the time but that won’t allow null being the third option - that the user hasn’t set a value yet - which is what I interpreted as the desired result

[Apple]

(edit: Sorry, I didn’t realize he was using .default() in first place; original comment below)

I wasn’t referring to your .default() expression, I was referring to $_POST.somevalue 0 magically transforming to null

In fact, $_POST.somevalue is actually “0” (a string), which then can be casted to false (boolean). If the string exists, it makes no sense to become null. Null would be if $_POST.somevalue didn’t exist at all

A possibility is the browser might not be sending $_POST.somevalue if its value is 0, so @stoyleg should look into the browser’s requests to see if it’s actually sending the value 0. I believe this issue happens with checkboxes, when they’re not ticked the HTML specification says to not send a value, so in such case it effectively becomes null

[stoyleg]

I kind of need to differentiate between a zero and a null - I need both options. As @bpj mentioned setting the default to zero won’t allow for the null option. However, I’m on a bit of a tight schedule with this so may have to live with the zero - I think I can make do.

[stoyleg]

The zero is definitely being sent - if I remove the default function it works but then it won’t accept a null.

[brad]

Why do you need to use 1 and 0? Why not a simple yes and no?

[bpj]

@stoyleg

I’m on a bit of a tight schedule with this so may have to live with the zero

Why can’t you use my suggestion?
$_POST.somevalue === '0' ? 0 : $_POST.somevalue.default(null)

I’ve just updated to look for 0 as a string in the ternary expression as POST values are sent as strings

[bpj]

I’m pretty sure it is not magically transforming to null, the default formatter is seeing ‘0’ as false and applying the default value. It doesn’t matter if it’s a string or number, 0 is still false in JavaScript.

Using a ternary is a quick and easy way to allow the third option of true(1)/false(0)/unset(null)

[Apple]

I actually missed the part he mentioned he was using a .default() formatter, sorry about that

Regardless, here’s a recap:

1. @stoyleg and @Apple had a given understanding of the .default() formatter (doesn’t actuate on false booleans)
2. @bpj had a (correct) different understanding of the .default() formatter (actuates on false booleans)
3. The .default() formatter is undocumented, and therefore lead to @stoyleg making a logic flaw

Proposal:

1. Add information on the formatter’s UI to explain a false boolean may be ignored and instead the specified default value may be assumed

I still believe the .default()'s logic is flawed by design, and people built their apps on the assumption of this flaw.

Here’s my thinking:

Imagine you build a software, and you want to send usage metrics to your server (to see what functionality users use most), and you want this enabled by default.

There’s an optional .env file, a user does the following:

SEND_METRICS=0

You access it like this:

$_ENV.SEND_METRICS.default(1)

What happens in this case? SEND_METRICS becomes true even if the user didn’t want to enable metrics. I explicitly mentioned 0, and your app just overwrote my decision with a default you picked. If the user didn’t explicitly have such environment variable defined then it would make sense to assume 1 by default

I could workaround like this:

$_ENV.SEND_METRICS === null ? 1 : $_ENV.SEND_METRICS

Which is certainly a valid solution, but perhaps not so straight-forward

[bpj]

@Apple

You could access it like this:

$_ENV.SEND_METRICS.default(1)

You could easily just reverse the statement:

$_ENV.DO_NOT_SEND_METRICS.default(0)

and use it as a flag to stop sending metrics if set. The way not set or set to 0 will send metrics and 1 will prevent sending them

What we’re talking about is the behaviour of 1 and 0 as true and false. This is standard JavaScript (and pretty much every programming language I have ever come across). Whether or not you explicitly defined it or not, using 0 will evaluate as false.

In terms of documentation - The default formatter has a simple task:
if the value evaluates to false (undefined, empty, 0, false) then use the value set in the parentheses

There is a slight caveat client-side where empty strings are not treated as false - discussed here:

Default - different behaviour for SC to Client-Side

Is the data binding default function meant to work the same for both SC (in my case PHP) and client-side? I’m finding that the behaviour for empty strings (not NULL) is different: Server Connect treats an empty string as something that would trigger the default value however, client-side formatter seems to leave it as an empty string. e.g. imagine a SC returns two values firstname = 'John' preferredname = 'Johnny' I use preferredname.default(firstname) to show the preferred name if presen…

[patrick]

What is the server model being used (NodeJS, PHP, ASP)?

A form only sends values as strings, so it will send "1", "0" or "". Since this is not the type the database expects it will cast it to the correct type. How it is doing the cast can differ per server model or database. Normally "0" would not be cast to null.

[Apple]

@bpj

What we’re talking about is the behaviour of 1 and 0 as true and false. This is standard JavaScript (and pretty much every programming language I have ever come across). Whether or not you explicitly defined it or not, using 0 will evaluate as false.

That’s not what I’m talking about. I’m talking about Wappler’s behaviour on the .default() formatter

Database softwares handle this differently. Assume a column boolean (tinyint), nullable, with default value NULL. It has three states:

• 0
• 1
• NULL

If you send it a 0, the database software inserts a 0 in the database, even if the default value is NULL. However, if you do the same in a Wappler expression (pseudocode: 0.default(null)), it assumes null instead of 0

So, there you have, two different behaviours for the same problem

[bpj]

It isn’t being cast to null, it is the result of the default formatter returning null for:
somevalue.default(null) where somevalue is ‘0’

[patrick]

@Apple

If you send it a 0, the database software inserts a 0 in the database, even if the default value is NULL. However, if you do the same in a Wappler expression (pseudocode: 0.default(null) ), it assumes null instead of 0

That is indeed not the correct behavior, in App Connect the default formatter only applies the default when the value is null or undefined. In Server Connect it is not doing the same, it applies the default when the value is falsely. This should probably be updated to the same behavior as App Connect, but the problem is that it often is used on form input and the value is not null or undefined, but instead it is "" and should then use the default. So it is used different on the Server then on the Client.

For NodeJS the current code is:

return val ? val : def;

I suggest that we could change that to:

return val == null || val == '' ? def : val;

That will only apply the default when the value is null, undefined or an empty string and should not break existing actions.

[patrick]

The default formatter is located in the file lib/formatters/core.js, you can change line 4 with the code I’ve given above.