Problem/Motivation
As in title, the error is:
SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'THE_FIELD_COLUMN' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT node_field_data.langcode AS node_field_data_langcode, node_field_data.nid AS nid FROM {node_field_data} node_field_data WHERE node_field_data.status = :db_condition_placeholder_0 ORDER BY node_field_data.changed DESC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 )
Steps to reproduce
- Create a view with a table display format having fields: any, changed,
- set the table format default sorting to changed, descending
- add distinct on the query
- observe the error
Reproducible with any fields actually, it dosn't have to be changed.
I could reproduce this on mysql but not on mariadb.
Comment | File | Size | Author |
---|---|---|---|
#22 | 3195178-22-test-only.patch | 9.47 KB | mstrelan |
#22 | 3195178-22.patch | 10.53 KB | mstrelan |
#20 | views.view_.distinctbreak.yml | 6.7 KB | Lendude |
#18 | combined-2998016-2--3195178-17.patch | 10.1 KB | mstrelan |
#14 | 3195178-14.patch | 7.58 KB | Lendude |
Comments
Comment #2
Graber CreditAttribution: Graber as a volunteer commentedAlso reproduced on Drupal 9 on simplytest.me: https://stm6012dc39a8375-meagpb2j50vjpiiyoyfrghpkaqmajfix.tugboat.qa/ (yes, will dissapear in a week)
Comment #3
texas-bronius CreditAttribution: texas-bronius at Ocelot commentedHi @Graber -
As discussed offline, the issue (in my case) was the table plugin column sorting and either clicked or default sort specifies a field not in the query. `DISTINCT` doesn't seem to like it :) My workaround was to sniff out all possible clickable columns and add them to the query. Here's a view filter plugin that does it:
I hope this helps someone else running into this, how to work around your current experience, and maybe help identify the issue for core to consider how to address it if it's a bug.
Comment #4
LendudeYeah, easily reproduced. The click sorting (default or not) column isn't added to the query like a normal sorting is, causing the fail.
Comment #5
shailja179 CreditAttribution: shailja179 at Srijan | A Material+ Company for Drupal India Association commentedComment #7
tobiberlinI am a little bit surprised that this issue does not seem to effect so many people or everyone is working around it. It seems a common case to me to have a table display with table/column sorting and the need for the DISTINCT setting?! I feeld that this is something which should be solved with the display plugin?
I had this problem with the created field of a node. Maybe it is a problem with this field? I wonder how this field is not in the SELECT part of the query although it is added to the view?!
For all who run into the same issue I describe the full solution from #3 here:
1.) in your mymodule.module file:
2.) In folder mymodule/src/Plugin/views/filter:
3.) In your view configuration choose the filter "Table sorting distinc" under the "Global" group and save your view
Comment #8
tobiberlinCan somebody help? The code from #3 / #7 is not working when a field is used for sorting which comes from a joined table. It only works for fields from the base table.
In my case I get the following error:
Unknown column 'sidejob_organization.name' in 'field list
' - in my table I use the name column / field fromsidejob_organization
table. In database this is the correct name of the table but looking at the query I see the table is joined with an aliassidejob_organization_sidejob
. I thoughtensureTable()
would handle this but it seems that with the above code it does not.Comment #9
cilefen CreditAttribution: cilefen commentedIf you can get an exception via UI actions, that is major priority.
Comment #10
ericdsd CreditAttribution: ericdsd commentedHi thanks #7 helped a lot, beware not to use this filter on a display that doesn't have exposed sort as it would generate notices.
Comment #11
LendudeTried to make this fail in an automated test, but for some reason this passes.
Comment #12
Graber CreditAttribution: Graber as a volunteer commented@lendude, could it be because your tests use mariadb where the issue is not reproducible?
Comment #13
Lendude@Graber the test passes locally where on the same set up it fails when testing manually. So a bit baffled really.
Comment #14
LendudeTried with a node View. The same view errors on the normal site, but when running inside a test it is fine.
¯\_(ツ)_/¯
Comment #15
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commented@Lendude did you have any other patches applied when testing on the normal site? I can reproduce the error via an installed site and in tests when combining #14 with #2998016-2: Views distinct logic checks too early for non-empty $this->fields. Without that patch the DISTINCT keyword is never applied to the query. Note there are other ways to trigger the error such as is described in #3221418: MySQL error when a view has a default tablesort and uses a distinct query.
Setting back to "Needs work" since we only have a test-only patch.
Comment #16
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commentedComment #17
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commentedI think we can easily fix this as per 3195178-17.patch. I'm also attaching a combined patch with #2998016-2: Views distinct logic checks too early for non-empty $this->fields to confirm this fix works with that patch applied.
Comment #18
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commentedCombined patch as mentioned in #17.
Comment #19
larowlanComment #20
LendudeTested this again manually, will add the View I used, it couldn't be more basic. Manually still fails (on MySQL) without the fix, automated test still passes on MySQL and SQLite locally.
But since we have a failing test on the testbot, that is fine by me. Fix looks good and works when manually testing it.
Comment #21
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commentedLendude mentioned in Slack that he was testing on Umami which has multiple languages by default. Turns out we can trigger the error without the patch from #2998016: Views distinct logic checks too early for non-empty $this->fields by enabling multiple languages. This patch uses the view config from #20 with the test case in #14 but enables another language. We should see a fail in the test-only patch and a pass in the other.
Comment #22
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commentedForgot to enable language module in #21
Comment #24
mstrelan CreditAttribution: mstrelan at PreviousNext for Service NSW commentedComment #25
Lendude@mstrelan Fantastic work getting to the bottom of this!
We have a fix and a test, the fact that the test could be cleaner if #2998016: Views distinct logic checks too early for non-empty $this->fields landed first is, I feel, not a reason to postpone this, since it is only a minor tweak that is needed.
Comment #26
alexpottCommitted and pushed 4e5f54f439 to 9.3.x and c50e3f3277 to 9.2.x. Thanks!
Comment #30
jmsosso CreditAttribution: jmsosso at TUI commentedThis also happens in latest 8.9.x
Could any maintainer change the status to "Patch (to be ported)" or should I clone this issue to backport the patch?