Custom Query Response Truncated - MySQL PHP

Wappler General Bugs
#1

Wappler Version : 2.4.1
Operating System : W10

Expected behavior

I have a view which when called from regular Query, works fine. But, when the same query is called from Custom query, the results are weird.
The reason why I need to call it from Custom Query is that I need to add to this query. But since it did not work here (although it worked perfectly in phpMyAdmin), I tested various things and finally found this.

Actual behavior

This query works fine and returns correct number of rows:

image
image.png707×497 13.2 KB

But this fails and returns just two rows:

image
image.png701×480 12.3 KB

Have tried using AND instead of BETWEEN same results.
Please help @patrick .

2019: The Year in Review
#2

What are the results stored in the database? Probably a timezone issue with the server.

#3

Timezone issue, how?
I am running the same query on the same UI.
Only difference is one time the date comes from param and other its hardcoded.
Definitely not a timezone issue.

#4

What are the difference between the results returned?

#5

Failed: 2 rows.
Success: 76 rows.

#6

So how are the values different in these two results? What are the dates which fail?

#7

Please read the question again.
The custom query’s ‘run’ option itself fails. Date is the one which is shown in screen shot.

#8

Sid, you say that it returns just two rows and the other one returns correct rows.

WHAT are the dates which are actually NOT returned? Are they near dates, are they same dates? Are they previous or next day?

What is different in the two test results?
We don’t see the actual query results on your screenshot…

#9

The issue is with the parameters. Please see the screenshots carefully.
Its the same date - ‘2019-11-30’ for which result is 2 rows when using parameter, and 76 when hardcoded.

#10

Sid…
I am asking about the dates in the database which are not displayed? What are the records dates?
Are they 2019-11-30 or 2019-11-29 or 2019-12-01?

#11

Oh. Sorry.
The response is crashing on starting 2 entries/rows only. So I am just getting two records, both with date 2019-11-01 (which is the start date)

In the full result set, there are more records on more dates.

#12

I see, so it only displays the first date.
Does this happen with the regular query builder and BETWEEN condition?

#13

No. The regular query works fine with the between condition set via UI.

1 Like
#14

I had not checked this fact. That is indeed true. I have only 2 records in the DB for start date. And only those two are returned.

I thought its breaking randomly after 2 records. :sweat_smile:

#15

Any update on this @Teodor ?

#16

Sid,
As soon as we check the issue and be able to recreate it, and/if there is an update you will be notified in this topic.

#17

Sid I think you are comparing strings instead of dates …

#18

The query works fine in phpMyAdmin and Wappler’s regular database query step.
Its the custom query that is causing this issue. So probably that is comparing strings instead of dates… if that is actually the issue.

#19

It might help move this along if you tick the debug box on the custom action and post the results here.

#20

Damn. I did not notice when the debug option got added to custom query.
So from debug, it turns out the first param is getting replicated for all.
This is how the debug output looks like:

"custom1": { "query": "select * from vw_bill where Date between ? and ? and Sno = ?;", "params": [ { "name": ":P1", "value": "2019-11-01 00:00:00", "test": "2019-11-01" }, { "name": ":P1", "value": "2019-11-01 00:00:00", "test": "2019-11-01" }, { "name": ":P1", "value": "2019-11-01 00:00:00", "test": "2019-11-01" } ] }

I added in a third variable just to test. In the UI I have P1, P2 & P3. But debug shows only P1 for all three cases.