We are having issues getting data to show up using the bubble SQL DB connector plugin. The filter is working, it only shows 6 entries in the repeating group, however the data that is supposed to show won’t come up. The weird thing is if I take out the lng (Longitude) value from the query the data shows up fine. Also, if I run the exact same query in SQL from the command line or from google scripts it shows up fine.
Our best guess is that is has something to with the fact that longitude can be a negative number but we really aren’t sure. Thank you so much in advance for any help!
No it does not. This is is basically what I am testing on.
SELECT * FROM fcand WHERE (AENTDATE = DATE(“Glenn”) OR AFIRST LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AFIRST LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR ATITLE LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR ACODE LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AWKADDR LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AWKCITY LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AWKSTATE LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AWKZIP LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR ACOCODE LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AEMPLOYER LIKE CONCAT(‘%’, “Glenn”, ‘%’) OR AEMAIL LIKE CONCAT(‘%’, “Glenn”, ‘%’)) ORDER BY ASEQ DESC LIMIT 100;
In the query I am actually running I have every column written out in the select statement but I didn’t want to put the whole thing on here since it amounts to the same thing. I have run it with parameter binding, but the exact same thing is happening. We dug in like this to find out what was happening.
Below I have a picture of the repeating group with everything except the lng value, when I put it back in the query the same repeating group shows up with the same number of rows but none of the data shows up in those rows. If I take out the WHERE clause completely, 100 rows show up and if any row has a NULL value for lng, then it displays the data like the picture you see. Unfortunately, I cannot get screenshots of these because the plugin is having a different issue where I cannot update any SQL statements. This problem developed after the other, and I have told bubble about the bug and they are working on it. I also included a picture of when I run a similar query from the command line to show that the data shows up. It also shows up when I pull in all the columns but it is hard to read since there are so many columns so I did not include a screenshot of that.
I recognize your first two points, they are a little weird but not actually giving us any issues so I left them for now while I look at the actual issue.
The lng is a decimal type (Decimal(10,8) I think? That might be the lat column though. It is ultimately a Decimal) and the I am returning it as a number in bubble.
That is a good question I am not sure. As soon as bubble gets back to me with a solution for why I can’t update my SQL queries I will test that and get back to you on that.
Are you asking if I have tried to return a virtual column that returns the absolute value of the lng value? If so I have not tried but will as soon as bubble gets back to me on my other issue. That is a really good idea.
Thank you so much for your help so far! I really appreciate it.
Alright, so I tested what you suggested and when I have the lng value in the statement nothing is showing up in the debugger despite the fact that it does filter to the correct number of rows. I also tried using the absolute value and when I do data does display normally when we just pull in the data. This is good and will work for one table, but all of this was just one issue that we actually noticed in a different table. We have a table that is running a SQL statement that calculates the distance between a given Latitude and Longitude and the ones in the database. I have tested the SQL statement in the command line and in google scripts and it does what we want it to do but when we are using it in bubble it is having the same issue the the other table was having. Bellow is the original SQL statement I wrote:
SELECT AADDRESS, ACITY, ASTATE, AZIP, lat, lng, ASEQ, 6371 * 2 * ATAN2(SQRT(SIN((ABS(lat)-ABS(?))(pi()/180)/2) * SIN((ABS(lat)-ABS(?))(pi()/180)/2) + COS(ABS(?)(pi()/180)) * COS(ABS(lat)(pi()/180)) * SIN( (lng-?)(pi()/180)/2) * SIN((lng-?)(pi()/180)/2)), SQRT(1-(SIN((ABS(lat)-ABS(?))(pi()/180)/2) * SIN((ABS(lat)-ABS(?))(pi()/180)/2) + COS(ABS(?)(pi()/180)) * COS(ABS(lat)(pi()/180)) * SIN((lng-?)(pi()/180)/2) * SIN((lng-?)(pi()/180)/2)))) AS distance
FROM fcand
WHERE APPLICANT = 1 AND lat IS NOT NULL
ORDER BY distance LIMIT 50
After we found that the ABS(lng) helps return values in bubble I decided to try that here as well. First I just added it to the lng column in the select statement outside of the equation. Then I added it to the lng values in the equation, then I added it to the ‘?’ that correspond to the lng values. None of these worked.
I’d treat this as a diagnostic rather than a solution. Bubble does accept negative values, so if ABS(lng) comes through and lng doesn’t, its likely an issue with type or value conversion. Could you try casting the lng to a different type or precision, to see if that problem is cleared up? Could lng being NULL be an issue somewhere?
You may find a better approach is to wrap the complex formula inside a FUNCTION, then you can test it independently of Bubble, and simplifying the parameter passing.
If you don’t have access to create functions in the database, I’ll suggest another way of simplifying parameters.
I will look into the function suggestions now, thank you for those.
It is a simple Decimal type, is there any reason this wouldn’t work? A lot of the research I have done suggests using Decimal for this purpose (specifically lat lng and distance). It actually works better when lng is NULL.
There are limitations, for example, Decimal(10,8) as you suggested earlier, cannot store a longitude of 100 or more, or -100 or less, because it has only 2 digits reserved for the whole amount.
Nullable is a separate constraint than the type. I mentioned null because your query checks for null lat but doesn’t check for null lng.
Then there is character set, which can also affect conversions to other systems.
Can you do some simpler tests? For example,
SELECT lat, lng FROM fcand WHERE APPLICANT = 1 LIMIT 1
I’ve just realised that you might be referring to negative lng values on your parameters from Bubble? I thought you meant returning negative values from the SELECT into Bubble?
I should have checked before I gave that example. We are using decimal(11,8) for lat and decimal(12,8) for lng, sorry about that.
I recently added values to the database, I cycled through the whole thing and either added both a lat and a lng value to each one, or nothing. So if lat is NULL, lng is also NULL. I can check for that as well, but I didn’t think that would help with the issue at hand.
I ran the test you suggested and the same thing happens, it has one row but no data is visible. If I increase the limit more rows are visible without any data. Selection_002|434x500
But if I take out lng then we see this:
The lng values in the database are negative, the ones we compare it to are also negative. We used google API to convert addresses into lat/lng values and in then in the app the user looks up an address which a google API then turns into a lat/lng then compares to those in the database.
Send me a PM with your app editor and I’ll take a look. Opening the app editor for view does expose your database user password, so take appropriate steps for that if needed.
I have raised a report and they suggested I come here, but I will do it again.
I really appreciate the offer, I talked to my boss to make sure but unfortunately we can’t do that because of the nature of the project and the information. Sorry. Thank you again for all of your help.
I reproduced your issue using Azure. It looks like Bubble is treating column names “lat” and “lng” as special. The problem goes away when the columns are renamed in the query.
Alright, I guess that kind of makes sense. I went ahead and changed the name of the column and tried it in the main table and it worked! Unfortunately, it still does not seem to be working with our distance function/locate.
