Queries with boolean values not working

[TomD]

Wappler Version : 4.4.5
Operating System : Windows 10
Server Model: PHP
Database Type: MySQL
Hosting Type: Custom

Expected behavior

It should be possible to use a value of 1 in a query including a column defined as a TINYINT (ie as a boolean field)

Actual behavior

The query fails. The ‘1’ entered in Query Builder is converted to ‘True’. This causes the query to fail.

How to reproduce

Create a query comparing the value of the boolean field to 1. The ‘1’ is changed to ‘True’ and the query will fail:

When Query Builder is reopened the problem is more apparent:

I was testing a local project, having updated the Wappler files. I was about to update the live site before I noticed some queries were returning no results. The problem was the due to this issue.

This bug has appeared off and on ever since Database Manager was added to Wappler. Running the query in DM works as expected, which is not very helpful. The query it executes (in this case using ‘not equal’) uses this condition:
where hide != true
However, when the query is run in the browser it fails. The SELECT statement is prepared but not executed. In previous versions of the bug, I think the query seemed to run correctly, but used the value of ‘true’ instead of ‘1’. In either the case, the query failed.

As before, one workaround is to create a variable with a value of 1 to use in the query. Unfortunately, after this bug was fixed before, I removed some of these workarounds; I should have left them in place.

(I’m not sure in which version of Wappler this bug was reintroduced. Probably not the current version.)

[TomD]

In case it’s relevant:

I duplicated a step in an API created some time ago. This is an UPDATE which sets a TINYINT column to 0. This column appears as type ‘number’ in the original query. When I duplicated the step, the column appears as type ‘boolean’. Here’s the original step and the duplicated one:

The update works fine in any case.

[Teodor]

@TomD

Expected behavior

It should be possible to use a value of 1 in a query including a column defined as a TINYINT (ie as a boolean field)

Actual behavior

The query fails. The ‘1’ entered in Query Builder is converted to ‘True’. This causes the query to fail.

How to reproduce

Create a query comparing the value of the boolean field to 1. The ‘1’ is changed to ‘True’ and the query will fail:

Tom, are you sure your connection is not using some old metadata?
Please reload the table metadata in the database manager by clicking refresh for the table (on the table name).

[TomD]

Thanks Teo. I think I tried that before, but I’ve just done another database refresh and restarted Wappler - but the results are the same.

If this is the connection file you mean, mine looks like this:

[Teodor]

No, i am referring to going to database manager, right clicking your table, selecting refresh.
then - reopening the server action/query step and reapplying it.

[TomD]

@Teodor

database manager, right clicking your table, selecting refresh

Yes, that's what I did.

[Teodor]

So just to clarify - your issue is that when using an TINYINT field in a mysql db, with PHP server model, when you enter equals to 1 as a condition, the query fails?

[TomD]

Yes. Because the 1 is converted to true (as in the first screenshot above). This looks like the same bug that has been reported several times in the past (and fixed).

[George]

Also booleans should be TINYINT(1) for MySQL this is what Wappler Database manager makes from the Boolean.

So check that it is displayed as Boolean type in the database manager.

We already had few topics around this in the past…

[Teodor]

I just tested this and it works perfectly fine. I even created the table in a third party tool (mysql query browser).

The table field definitions:

The table values:

The table values previewed in the database manager:

The query builder using equal to 1 condition:

Results as expected:

[TomD]

It’s strange. I created a new query. I entered ‘1’ which was turned into ‘true’:

This the query which executed (well, not quite):

This is as far as it gets. ‘Execute SELECT’ doesn’t happen.

If I change the query using a variable == 1:

… the correct query is executed:

[TomD]

I created a new column (using Navicat):

ADD COLUMN boolean_test tinyint UNSIGNED NULL DEFAULT NULL

I then refreshed Database Manager. This is how the new column appears:

I think it shoud have a ‘tick’ symbol. I have these in other tables. If I could persuade Wappler to see this column as a boolean perhaps all would be well. I’m not sure what I could have done differently. The tables were originally created in MySQL 5.7.21. I’m now using v8.0.27 (locally and on the server). Could that be relevant?

[George]

Also booleans should be TINYINT(1) for MySQL this is what Wappler Database manager makes from the Boolean.

[TomD]

This is indeed how I usually create them. I just created another new column:
ADD COLUMN boolean_2 tinyint(1) UNSIGNED NULL DEFAULT NULL

After refreshing Database Manager, this field appears as a number, not a boolean. It seems the only way I can create a boolean which Database Manager recognises is to create it in DM. However, even doing this doesn’t solve the problem.

Here I have a boolean which Wappler recognises. I enter ‘1’, DM changes this to ‘true’ and the query fails to execute properly:

Could I be doing something wrong? Should I not be entering ‘1’?

There have certainly been problems like this the past (experienced by other users too). However, I haven’t had any problems for quite a while.

[George]

Maybe refer to all the previous topics about this… no need to post a new topic about this every couple of months …

Tinyint field set to true in condition

In Wappler an TinyInt(1) is handled as a Boolean. For the database it is converted to the correct format the database understands, in databases that do not support native boolean fields this is often 1 and 0.

Tinyint fields converted to boolean

The refresh only updates the metadata from the database, it does not update anything in the query, so you indeed have to reapply the condition after the refresh.

Boolean fields used in Query Builder cause queries to fail

true/false will evaluate as 1/0 on the server, the query shown in the Query Builder is not the actual query being executed on the server. Are you getting any error in your action?

[TomD]

@George

no need to post a new topic about this every couple of month

... unless the bug keeps coming back every couple of months.

I have referred to these previous topics - indeed I contributed to them. I reported the issue in March, so did @swf, who also reported it in May. Several others have reported similarl/possibly related issues too.

As far as I know it was fixed several versions ago and it seems to be back once again, or at least a slightly different version of it. I haven't had any problem with it for a while, until today. If nobody else has the issue, something must have changed with my setup - queries which were showing results are no longer showing results. It's not a big issue as I can use the same workaround as I've used before.