Weird Eloquent results on 'exact match' db queries

I testet this with my Test User data and it works.

Select * From Users WHERE `uuid` Like '5%'

Users::where('uuid', 'like', 5%’)->get();

@EventFellows what does your migration look like?

It is an extended Spark installation so there are quite many columns. So far I have only noticed that with the UUID column wich is CHAR on the database itself.

    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->uuid('uuid')->required()->unique();
            $table->string('name')->unique();;
            $table->string('email')->required()->unique();
            $table->string('type')->required()->default('standard');
            $table->string('password', 60);
            $table->tinyInteger('verified')->default(false);                      // email verified
            $table->string('status')->required()->default('unverified');          // active, blocked

            // to grant free usage for a certain time on user account level
            $table->timestamp('free_downloads_until')->nullable()->default(null);
            $table->timestamp('free_events_until')->nullable()->default(null);

            $table->integer('login_count')->unsigned()->default(0);         // counts the number of logins since registration of account
            $table->timestamp('last_login')->nullable()->default(null);     // records the last time the user was logged in UTC time

            $table->rememberToken();
            $table->text('photo_url')->nullable();
            $table->tinyInteger('uses_two_factor_auth')->default(0);
            $table->string('authy_id')->nullable();
            $table->string('country_code', 10)->nullable();
            $table->string('phone', 25)->nullable();
            $table->string('two_factor_reset_code', 100)->nullable();
            $table->integer('current_team_id')->nullable();
            $table->string('stripe_id')->nullable();
            $table->string('current_billing_plan')->nullable();
            $table->string('card_brand')->nullable();
            $table->string('card_last_four')->nullable();
            $table->string('card_country')->nullable();
            $table->string('billing_address')->nullable();
            $table->string('billing_address_line_2')->nullable();
            $table->string('billing_city')->nullable();
            $table->string('billing_state')->nullable();
            $table->string('billing_zip', 25)->nullable();
            $table->string('billing_country', 2)->nullable();
            $table->string('vat_id', 50)->nullable();
            $table->text('extra_billing_information')->nullable();
            $table->timestamp('trial_ends_at')->nullable()->default(null);
            $table->timestamp('last_read_announcements_at')->nullable()->default(null);
            $table->softDeletes();
            $table->timestamps();
        });
    }

I now also noticed that if I run the query (as displayed in chrome dev tools) directly on the database the db does not give any data as expected.

What is ->required() in the migration? Never seen that one before..

To me it has always been the opposite to ->nullable() but now that you ask I cannot find it anywhere in the docs... weird(?!)

But even with removing it and doing migrate:refresh --seed the same issue occurs.

I have now looked into what happens on the db with DB::listen and it shows a different query than what chrome dev tools shows:

In summary: The request in laravel is:

User::where('uuid', '=', 4)->get();

DB:: listen shows as query:

select * from `users` where `uuid` = ? and `events`.`deleted_at` is null

and Chrome Dev Tools

SELECT * FROM `users` WHERE `uuid` = '4' and `users`.`deleted_at` IS NULL

This is all on the same request... @SaeedPrez or someone else: any idea I where I should look (there are no errors thrown)

@EventFellows To be honest, I've never used the uuid column type and I couldn't find information that made much sense about it. Not even on the MySQL official docs.

Unless someone else has more experience/info about this, I suggest you use a ->string() column type.

I have tried that, too in the meantime. Same result. I have event completely reinstalled all vendor files and use the latest version.

I am afraid it must be something super-stupid but I am not seeing it right now... Thanks for your efforts @SaeedPrez

Why does it say events.deleted_at when you query the users table?

Something odd here..

What database tools do you use?

Is the column for uuid varchar(36)?

Does your database show those uuid's in the right column?

Is there any primary key set on the table in the model?

@Snapey

I have the UUID issue on an Event Table and on the Users table (I corrected it in post above).

(Sidenote: The events table is like a 'meetings' table and not to be mixed up with the event system in laravel)

No sure what you mean with databasetools? I use Heidi SQL to view it (on Win10), other than that normal homestead setup out of the box with spark installed.

Columns is definded as UUID which ends up being a CHAR (36) on the database itself (but same behaviour with a string-column.

Database has the uuids in the uuid column. (and when querying via another field that all works fine)

There is an autoincrementing ID (as set by default) on the table, too, which is the primary key.

How about if you run that same query in heidi sql?

When you have your $users etc, how do you know it contains the wrong records. How are you dumping the data?

Try running the same query in php artisan tinker

In tinker, don't forget that you need to prefix the class App\User::where('uuid', '=', 5)->get();

If the underlying database is type CHAR then the documentation indicates what the expected behavior is. See the bottom regarding trailing spaces and comparison.

https://dev.mysql.com/doc/refman/5.7/en/char.html

@jimmck I am aware of the handling of trailing white spaces but this is not the problem.

@Snapey thanks for your suggestions. Here is what I found:

• on heidi SQL the query gives the correct response (no response in this case)

• i have dumped data via dd(), var_dump() and also used a foreach loop on a view, I have also used Clockwork::info() to push to chrome dev tools -> all with the same results

• php artisan tinker gives the same results as when I dump the data

I know that it is the wrong data because it is a test setup with only 28 users and can manually compare it:

E.g.:

User::where('uuid', 4)->get();

return 2 user in the current seeding:

• user.id 3 with user.uuid 4d7537ee-aa43-4fba-bde2-36aee4f1a9c8
• user.id 11 with user.uuid 4d25399a-7b47-4d7a-b5a8-228159ae6a4a

Whereas if I query for

User::where('uuid', 8)->get();

I do not get any users eventhough I have 6 users with an UUID that starts with 8 and one with an ID of 8.

Does anyone know where the where part of User::where is defined?

Have you tried whereStrict()?

@bashy No I have not but now tried it.

It gives an error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'strict' in 'where clause' (SQL: select * from `users` where `strict` = uuid and `users`.`deleted_at` is null)

config/database.php is already set to strict

What I find extremely confusing is that DB::listen resports a different query being run than Clockwork in chrome dev tools

Ah, glad both @Snapey and @bashy are here, you're in good hands now, these guys are at least 10x more experienced and knowledgeable than me.

Good luck and I hope you find the solution.

Oh it's for collections https://laravel.com/docs/5.3/collections#method-wherestrict

@SaeedPrez Don't put yourself down. We all learn from each other. I don't have a youtube channel, but you do and teach me things!

@eventfellows The query you see running in the dev tools is correct. The version with the question mark is the prepared statement. Later, the values are bound to the query.

So, if the SQL query is correct, and the same query in Maria produces the right result, then we should assume that eloquent also gets the same result, but something goes wrong between the response and the dumping of the variable.

It looks like Laravel may be setting the PDO datatype to number for a char field. A simple table with 36 char UUID's yields the following.

select * from myusers where id = '4'

 16:27:59  [SELECT - 0 rows, 0.004 secs]  Empty result set fetched
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.004/0.000 sec  [1 successful, 0 errors]

and...

select * from myusers where id = 4

4d7537ee-aa43-4fba-bde2-36aee4f1a9c8
4d25399a-7b47-4d7a-b5a8-228159ae6a4a

Table myusers

select * from myusers

4d7537ee-aa43-4fba-bde2-36aee4f1a9c8
8a7537ee-aa43-4fba-bde2-36aee4f1a9c8
4d25399a-7b47-4d7a-b5a8-228159ae6a4a

Table is defined as

CREATE TABLE IF NOT EXISTS myusers (
  id char(36) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

So if it is looking for a numeric it is matching to the first non-numeric

uuid starting 4dxxx evaluates to '4'

On the user that started 8 was the second digit also a numeric?

also,

So, if the SQL query is correct, and the same query in Maria produces the right result, then we should assume that eloquent also gets the same result, but something goes wrong between the response and the dumping of the variable.

try in Maria with and without quotes around the value you are looking for

@Snapey thank you buddy but I did not mean it in a negative way. I have been a restaurateur for the past 6 or so years, now I'm trying to get back into programming. One way I try to learn is by helping other people but I understand my limits and that I am not as experienced as someone who has been working as a developer for years. So I am glad when two of the top guys on this forum show up like superheroes when they're needed ☺♥

@SaeedPrez , @Snapey , @jimmck , @bashy all your support is super-highly appreciated - really!

Snapey, your hint was right, on the query with 8 only those results are returned that onyl have an 8 at the beginning AND are followed by a letter.

If there is a second digit after the 8 the result is not returned.

I also notices that if I query for 0 (the number zero) all UUIDs are returned that start with a letter...

Weird, i will keep investigating. I am located in Europe so I will call it a day for now and I will check back tomorrow if you have any additional ideas.

Is there anyway in the code that I could directly dump the data to track it down further?

So, i knocked up a quick table to confirm what @jimmck was saying;

>>> App\UuidTest::where('uuid',4)->get()
=> Illuminate\Database\Eloquent\Collection {#698
     all: [
       App\UuidTest {#699
         id: 1,
         uuid: "4d7537ee-aa43-4fba-bde2-36aee4f1a9c8",
       },
       App\UuidTest {#700
         id: 2,
         uuid: "4d25399a-7b47-4d7a-b5a8-228159ae6a4a",
       },
     ],
   }
>>> App\UuidTest::where('uuid','4')->get()
=> Illuminate\Database\Eloquent\Collection {#676
     all: [],
   }
>>>

First query, 4 is unquoted

second query, 4 is quoted as a string

I looked for this originally, but in your examples the value being looked for is being quoted? Is this accurate or did you type it in the post as you remembered it or expected it to be???

ok, got it

If there is a chance that the user might try and query the model with an integer instead of the uuid, use of type casting will protect from returning multiple unwanted results.

eg

App\UuidTest::where('uuid', (string) $request->uuid)->get()

Helpful to know.

The deeper I look into it I feel it might be a bug introduced in 5.3.

@Snapey I see that the problem can be worked around when casting to string but not ideal as users can search by ID or UUID at the same time.

And to me an exact match should never return a result that essentially says 4 is an exact match of 4asdfa-asdf... - in my opinion that is just plain wrong

Here is what I also found throughout the day today:

• the problem seems to affect L5.3 but was not there in 5.2
• I could trace it down to vendor\laravel\framework\src\Illuminate\Database\Connection@select that calls $statement->fetchAll($fetchMode) method at the end.

In that method the behavior diverges between L5.2 and L5.3

• In 5.2 get_class($statement) is of class PDOStatement but the fetchAll-class is not being called for some reason (maybe namespacing).

• In 5.3 get_class($statement)is of class Doctrine\DBAL\Driver\PDOStatement which seems to be the correct namespace. There the fetchAll methods gets called but does deliver the wrong result as explained earlier...

Here is where I got stuck: In Doctrine\DBAL\Driver\PDOStatement there is the a call to parent::fetchAll() with different sets of parameters but I cannot locate this parent::fetchAll(). Where do I find this parent:: reference

Also, thank you so much to @SaeedPrez , @Snapey , @jimmck , @bashy for your hints and tips. Without you I would have given up already on this bug-hunt.

@EventFellows As I said the error is in how the data binding is done in Laravel 5.3. In Connection.php you will find the code.

    /**
     * Bind values to their parameters in the given statement.
     *
     * @param  \PDOStatement $statement
     * @param  array  $bindings
     * @return void
     */
    public function bindValues($statement, $bindings)
    {
        foreach ($bindings as $key => $value) {
            $statement->bindValue(
                is_string($key) ? $key : $key + 1, $value,
                is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
            );
        }
    }

Based on the type of the where clause value Laravel sets the bind type to Integer or String. When you enter a number like 4 ion the Where clause in passes as a number regardless of the datatype of the column being compared. In the case UUID that is a CHAR(32). You can see the behavior in a SQL editor as I posted above.

I think your assumption is wrong in that the exact same behaviour is proven just using a raw sql statement in a sql tool (SQLPro in my case)

If the id is quoted then it is treated as a string and does not match to any UUID

If the ID is unquoted then the column is treated as numeric. Much the same as PHP will treat '4dabc'+0 = 4 mysql is doing the same, taking as much from the field as it can and considering it a number.

If your user might request with an ID or UUID then you will be looking in two different columns anyway so the query will be different;

    if(strlen($request->id) ==36){

        $model = Model::where('uuid', (string) $request->id)->first();

        } else {

                $model = Model::find($request->id);

        }

@Snapey I have not compared it to a raw SQL query but with an almost identical L5.2 and L5.3 installation on the same database.

In 5.3 the systems shows the weird matching behaviour as discussed (due to the public function bindValues($statement, $bindings) implementation @jimmck mentioned (thanks for pointing me there)

But on a 5.2 system all works fine (5.2 has a dfiferent implementation to bind the values)

So IMO 5.3 is a step back in that regard.

It seems the problem had been there already in 2014 (https://github.com/laravel/framework/issues/5254 ), was then fixed somewhere on the way towards 5.2 and now is back in 5.3 and 5.4