[3.x] [4.x]: PDOException when using :notempty: and :empty: in element queries

[mmikkel]

What happened?

Description

Since Craft 3.7.45 and 4.0.5, the following PDOException is thrown when using :notempty: or :empty: values for relational field params in element queries:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'elements_sites.siteId' in 'on clause'

Full Craft 4 stack trace is here. (exception is basically identical for Craft 3).

I'm not able to reproduce on Craft 3.7.44 and 4.0.4. Possibly fe7ce94 is the culprit here?

Steps to reproduce

1. Create an entries field entriesField and add it to an entry type's field layout in a section news
2. Create an element query like
   {% set entries = craft.entries.section('news').entriesField(':notempty:').all() %}

Expected behavior

Actual behavior

Craft CMS version

3.7.45.1 and 4.0.5.1

PHP version

8.1.4

Operating system and version

macOS 12.4 (using Laravel Valet 3.1.7)

Database type and version

MySQL 5.7.37

Image driver and version

No response

Installed plugins and versions

None

[AbbeyDesign]

I have the same issue but after updating to Craft 3.7.45.1 using the same type of query as above. If I remove the related field from the query or if I give the related field a specific id eg (1234), it runs without error. It seems to be the ':empty:' part.

[jeromecoupe]

I can confirm the same issue with element queries containing :notempty: after upgrading from Craft 4.0.4 to 4.0.5.1 (Pro)

Error message

(yii\\db\\Exception(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'elements_sites.siteId' in 'on clause'

No errors on pages / views not using :empty: or :notempty: on the front end. No error in the CP either. As soon as I remove the :notempty: part from the element query, the error goes away. Replaced it with an extra .ids query and a relatedTo param as a temporary fix.

If I rollback to Craft 4.0.4 the error disappear

Environment

• PHP version: 8.0.8
• Database driver & version: MySQL 5.7.34
• Craft edition & version: Craft Pro 4.0.5.1
• Yii version: 2.0.45
• Twig version: v3.3.10
• Guzzle version: 7.4

[brandonkelly]

Strange, I’m not able to reproduce this locally. Can any of you please send your Composer files and a database backup into [email protected]? We can try looking into it from there.

[mmikkel]

@brandonkelly I'm able to reproduce on a completely fresh Craft 4.0.5.1 install. I'll be happy to send in those files, but:

I think the issue might be isolated to MySQL 5.7 (and possibly MariaDb, but I haven't tested), as I'm not able to reproduce at all on MySQL 8.

[jeromecoupe]

Files from local install sent to support. MySQL and not MariaDb for this project. Did not test with MySQL 8 yet but error definitely occurs with MySQL 5.7.34 on my side

[robzor]

Hi,

Just to add I did email this through but I am getting the same error in the control panel with custom entry listing views in the Entries page, that are based around things being 'empty' or 'not empty'.

MYSQL 5.7.37
Craft 4.0.5.1

Works fine on 10.6.5 (our Servd environment, which is MariaDB I believe)

(Product Gallery is an Assets field)

[brandonkelly]

Thanks all! I was able to reproduce with MySQL 5.7, and just released Craft 3.7.45.2 + 4.0.5.2 with a fix.