Framework: [6.0] PostgreSQL incompatibility?

[hotmeteor]

• Laravel Version: 6.0.1
• PHP Version: 7.2.22 / 7.3.9
• Database Driver & Version: PostgreSQL 9.5.18

Description:

There seems to be an issue when using PostgreSQL with Laravel 6.0 with how some columns are being represented in queries. I've only been able to check this with 1-1 relationship so far, but from what I can see Laravel 6 is producing a query like:

 select * from "user_2fa" where "user_2fa"."user_id" = 1 and ("user_2fa.user_id") is not null limit 1

Note the " (double-quotes) around "user_2fa.user_id". This syntax is trying to find a column with that entire string as its name, instead of table.column as expected.

PostgreSQL would accept: "user_2fa"."user_id" or 'user_2fa.user_id' (using single quotes) instead.

I might be doing something wrong, but this seems to be pretty broken out of the box. Been using the same code on previous versions for over a year.

Steps To Reproduce:

1. Connect to a PostgreSQL database
2. Create a 1-1 relationship between two models
3. Reference the relationship in the code
4. See broken query

[ankurk91]

Can you share your php code. Atleast the eloquent code.

[hotmeteor]

User model:

public function user2fa()
{
    return $this->hasOne(User2fa::class);
}

User2fa model:

public function user()
{
    return $this->belongsTo(User::class);
}

[ninjaparade]

@rs-sliske nice thumbs down on example code. Helpful 👍

[rs-sliske]

@ninjaparade sorry if my opinions of database design offend you

[ninjaparade]

@rs-sliske You frown upon the use of relationships whilst designing your database?

[rs-sliske]

@ninjaparade 1 -> 1 relationships yes

[tillkruss]

Who the heck is this @hotmeteor guy?

[tillkruss]

@hotmeteor: Works for me on Laravel 6.0 and PG 11.5. What version are you on?

[hotmeteor]

9.5.18

[browner12]

I am also getting this issue with a 1-to-many relationship. It is quoting the table and column names and not quoting the value names.

Laravel 6.0.3
MySQL v8.0.17

[driesvints]

Thanks for reporting this. Appreciating help with this.

[hotmeteor]

It’s also happening on any table that has a soft-delete, ie. ”post.deleted_at”

If I switch back to my 5.8 branch it works as expected.

[browner12]

so I just discovered I'm not actually running into this issue with MySQL, but rather when I run my tests with an in-memory SQLite database.

@hotmeteor are you actually getting it with Postgres, or is it with SQLite, too?

[hotmeteor]

@browner12 PostgreSQL, haven't tried SQLite.

[tillkruss]

Are you getting this error?

Query 1 ERROR: ERROR:  syntax error at or near "user_id"
LINE 1: user_id
        ^

[hotmeteor]

Here's another example:

SQLSTATE[42703]: Undefined column: 7 ERROR: column "teams.deleted_at" does not exist LINE 1: ...on "teams"."id" = "team_workers"."team_id" where ("teams.del... ^ (SQL: select "positions".*, (select count(*) from "team_workers" inner join "teams" on "teams"."id" = "team_workers"."team_id" where ("teams.deleted_at") is null and "positions"."id" = "teams"."position_id" and ("teams.deleted_at") is null) as "user_count" from "positions" order by "positions"."name" asc limit 100 offset 0)

[browner12]

My issue actually turned out to be something else.

Mine was caused by SQLite not understanding the keywords RLIKE or REGEXP, and from my misunderstanding of the standard SQL syntax for enclosing identifiers and values.

TIL that the SQL standard says identifiers should be enclosed in double quotes, and literals should be enclosed in single quotes. I always thought the MySQL backticks (`) were the correct way to enclose identifiers, but apparently I've been wrong for 15 years.

[hotmeteor]

I've finally found the issue and it's not framework related. Another library was overridding the whereNull and messing up the wrapping of the columns. I'm going to create a PR over there. Sorry for the noise.

[grEvenX]

@hotmeteor Perhaps you can share some more insight into what library and any reference for the issue created "there" for others running into similar issues ? I'm have the same problem, so more information would be appreciated 😄

By "stalking" your GitHub activity, I can see that we both have in common that we use phaza/laravel-postgis library, could that be the culprit?

[grEvenX]

After some more time looking into this, the problem for me was related to phaza/laravel-postgis and bosnadev/database.
Upgrading to the latest phaza/laravel-postgis version solved the issue.