After 9.5.3 upgrade: DatabaseExceptionWrapper: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nametid' in 'group statement'

• Are there any contributed views-related modules installed? Did you change or not change their versions at the same time as upgrading Drupal core?
• A stack trace would be super-helpful.
• Have you exported the configurations of the broken original view and of the working cloned view and compared those?
• The 9.5.3 release notes contain all changes. Do any of those seem a likely cause?

The release notes: https://www.drupal.org/project/drupal/releases/9.5.3 →

Agree with #2 that we need more info about the view itself and the site configuration to be able to help diagnose this.

I checked the changes in 9.5.3 and 9.5.4 and don't immediately see anything that was changed in Views that would affect this.

🐛 Select queries do not escape the GROUP BY fields Fixed did add escaping to GROUP BY, but as far as I can see that wouldn't have caused the syntax error in the query here. Somehow the name and tid columns have been merged together into a non-existent nametid column, but I don't understand how that could happen.

No other modules have changed during this update; core is the only variable. I exported the configurations of the working and non-working versions of the view and here is the result:

diff views_working.yaml views_broken.yaml 
1c1
< uuid: 00ee2f69-33e9-4795-a0c0-784c1c5b6dda
---
> uuid: 9489b3b0-8df3-473b-8776-5bb36a41eecb
22,23c22,23
< id: duplicate_of_presidential_recording
< label: 'Duplicate of Presidential Recording'
---
> id: presidential_recording
> label: 'Presidential Recording'
606a607,609
>           relationship: none
>           group_type: group
>           admin_label: ''
609a613
>           operator: '='
611a616
>           exposed: false
612a618,621
>             operator_id: ''
>             label: ''
>             description: ''
>             use_operator: false
615a625,642
>             identifier: ''
>             required: false
>             remember: false
>             multiple: false
>             remember_roles:
>               authenticated: authenticated
>           is_grouped: false
>           group_info:
>             label: ''
>             description: ''
>             identifier: ''
>             optional: true
>             widget: select
>             multiple: false
>             remember: false
>             default_group: All
>             default_group_multiple: {  }
>             group_items: {  }

I'm not familiar enough with views to make much sense of this. But in case it's helpful, I built the working copy simply by using the `duplicate` function on the original, broken one.

As a follow-up to my last post, I tried an experiment and got interesting results.

I wanted to see if I could import the working config to rebuild the broken view, and in the process, fix the break.

So I deleted the original, broken view and then imported the configuration from the working copy, replacing the UUID, id, and label fields with the data from the original. However, when I did the import, the resulting view still had the same SQL problem. So I tried again several times, keeping ALL fields constant except one at a time.

What I found was that the "id" field in the configuration seems to be the one that causes the sensitivity. That is, the original view had the id `presidential_recording`. If I change that to, say `new_presidential_recording`, keeping all other fields as in the config file (either one, working or the original, broken config), then the resulting view is fine.

So for whatever reason, there seems to be something about how the view's "id" field is handled that is causing the problem.

Are you able to upload the two full YAML files of the broken and working views? If not, a unified diff (diff -u views_working.yaml views_broken.yaml) will be a little easier to read.

This is interesting.

I just uploaded the config files to this issue. Please let me know if they aren't properly visible.

I faced the similar problem, not in a View, but in my custom query.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."mailudn"."field_display_name_valuetotal_nodes_voted_on"."total_nodes_voted_on_' at line 44

In my query I have this GROUP BY:

$query->groupBy('u.uid, u.name, u.mail, udn.field_display_name_value, total_nodes_voted_on.total_nodes_voted_on_sum, total_nodes_voted_on.avg_total_nodes_voted_on_sum, user.user_display_name_check, user_name.name_check, user_mail.mail_check');

which becomes
GROUP BY "u"."uidu"."nameu"."mailudn"."field_display_name_valuetotal_nodes_voted_on"."total_nodes_voted_on_sumtotal_nodes_voted_on"."avg_total_nodes_voted_on_sumuser"."user_display_name_checkuser_name"."name_checkuser_mail"."mail_check")

If I change my query to

$query->groupBy('u.uid')
      ->groupBy('u.name')
      ->groupBy('u.mail')
      ->groupBy('udn.field_display_name_value')
      ->groupBy('total_nodes_voted_on.total_nodes_voted_on_sum')
      ->groupBy('total_nodes_voted_on.avg_total_nodes_voted_on_sum')
      ->groupBy('user.user_display_name_check')
      ->groupBy('user_name.name_check')
      ->groupBy('user_mail.mail_check');

everything becomes fine.

I have the same or similar problem: After installing Autoban I get when calling in Autoban rules list-> Log analyse:
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log.messagelog.type' in 'group statement': SELECT "log"."message" AS "message", "log"."type" AS "type", COUNT(*) AS "cnt" FROM "watchdog" "log" WHERE "log"."type" NOT IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4) GROUP BY "log"."messagelog"."type" HAVING (COUNT(*) >= :threshold) ORDER BY "cnt" DESC; Array ( [:db_condition_placeholder_0] => autoban [:db_condition_placeholder_1] => cron [:db_condition_placeholder_2] => php [:db_condition_placeholder_3] => system [:db_condition_placeholder_4] => user [:threshold] => 5 ) in Drupal\autoban\Form\AutobanAnalyzeForm->getAnalyzeResult() (line 286 of modules/contrib/autoban/src/Form/AutobanAnalyzeForm.php).

Drupal: 9.5.8 PHP: 8.1.16

Please let’s keep this on topic as a Views issue.

I think @kachinsky raises an interesting point, even though it's not views-specific. If SQL queries are getting built in a wonky way somewhere else (i.e. those group_by statements), couldn't that be responsible for the problem we're seeing in views?