MySQL/MariaDB BigInt Primary Key ID not working in Where

[sid]

Again, you are right. I too tend to just switch to custom query, every once in a 1000 queries that fails. Have done the same for this one in my code.
But it seemed very simple and direct and something that should work out-of-the-box. So had to report it.

[George]

PHP support for native bigint is not that great and emerged recently. It is also very much dependent on the system supporting it. Like 32 bit or 64 bit and the constant PHP_INT_MAX

That is why when there is no support the big mint is for safety stored as string to not get lost.

See:

https://www.php.net/manual/en/language.types.integer.php

https://wiki.php.net/rfc/bigint

So this is actually something with PHP not the database query.

[sid]

@George

It is also very much dependent on the system supporting it.

So that could be the reson why it works for Teodor but not me?

@George

https://www.php.net/manual/en/language.types.integer.php

Did not understand the article to be honest. Went over my head.

@George

https://wiki.php.net/rfc/bigint

Looks like this is from 2015, last modified 2017, and its status is withdrawn.

@George

So this is actually something with PHP not the database query.

So you mean to say the query builder UI is dependent on PHP, but custom query builder is not? Because same query is working fine in custom.

[George]

@sid

So you mean to say the query builder UI is dependent on PHP, but custom query builder is not? Because same query is working fine in custom.

Hmm that is weird, could post the custom query and it’s output? Maybe the normal query has a bad mapping to PDO with bigint, that seem to be a common PDO problem as well. @patrick can check it

[sid]

QUERY:

@sid

select id from tbl_x where id = 4659555762231;

OUTPUT:

@sid

Tried it again, still not working for me.
I checked this time on client’s production DB server which has MySQL 5.7.32 (a bit higher than 5.7.14 you tested on).

Query is from multiple query builder. Custom is custom query.

[George]

You said custom query is working…? So what exact is working and what not?

Did you pass the filter value as parameter?

[sid]

@sid

Setup query builder like so:

It should return a blank data set. Even though it returns 1 row when same query is run directly on sql server.

Custom query builder returns the value.
Multiple/Single query builder does not return the value.

[George]

But why you enter the value statically? That will never be the case - try to bind it in both with parameter

[sid]

Created a variable to hold the value:

Bound the param in DB to that (similar in custom query):

Now both don’t return any value:

For my task here, I had a static value to work with, so did not test with a param binding.

[George]

And if you enter the value from other source like $_GET.id so you can enter if from the query string for example?

[sid]

Left office for the day. Will try to reply with results tomorrow.

[sid]

@Teodor Can you please close this? I did not get time to reply earlier and now I don’t have a use case to test one.
Will re-open a new report if I encounter this again.
Thanks.