Identifiers longer than 63 characters are truncated, causing Views to break on Postgres

I have this problem too and so have other users: http://drupal.org/node/371711

Here is a patch, please try.

I test the patch in #2 and so far it works.

$view->name = 'longfield';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'title' => array(
    'label' => 'Title',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'field_story_very_long_field_value' => array(
    'label' => 'Long field',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_story_very_long_field_value',
    'table' => 'node_data_field_story_very_long_field',
    'field' => 'field_story_very_long_field_value',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'story' => 'story',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'long');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));

Ok, so the patch looks ok, but it does not conform to coding standards. For example, function arguments should have proper spacing, } should always be followed by a newline.

Also, in looking at this it seems like this really should happen during add_field(), not during query() -- because aliases get returned to the function for later use. That means changing them could change them out from under a handler. That seems odd to me.

I'll rewrite the patch that it uses add_field(), but where should I put the safe_alias counter? should I introduce a new member to the query object? This counter is needed, because when very long aliases (esp with cck and relations) have the some shortened prefix I need a counting number so I can distinguish between them.

I think add_field already performs collision detection. Are you sure you still need to do that?

If you really do,then go ahead and stick the counter on $query, but I am dubious that it is necessary.

We should fix this before alpha 2 of views 3.

I have created a patch for this in views 3 http://drupalbin.com/12557

I didn't post here because for views 2 doesn't apply.

Also I have remember Earl said something about fix this issue using views_safe_alias() from here #576694: Enable views to handle external tables properly, allowing for joins across tables in different mysql databases

This is a version for views3

Why not do this on add_field level?

It is returned the alias by default, so people can work with the new alias, if needed.

If you have these two aliases:

• node_event_customers2event_node_data_field_account_zipcode_field_account_zipcode_value
• node_event_customers2event_node_data_field_account_zipcode_field_account_city_value

They would both have the safe prefix of 'node_event_customers2event_node_data_field_account_zipcode_f' which of course will colide and I need to give them unique names like 'node_event_customers2event_node_data_field_account_zipcode_f_1' and 'node_event_customers2event_node_data_field_account_zipcode_f_2'.

I looked at add_field and could not see any collision detection?!?

Reworked the patch. safe_aliases are now generated in add_field. The patch builds upon the old patch from #2

I used code-style.pl to check for compliance to the coding standards. One thing I was a bit unsure about:

$item-> {$field['alias']} = $item-> {$field['safe_alias']};

code-style.pl complained about missing space in front of the {, but this looks a bit special to me.

codestyle.pl is quite bad.

+++ b/trunk/src/drupal/sites/all/modules/views/includes/query.inc
@@ -938,24 +956,7 @@ class views_query {
     foreach ($fields_array as $field_name => $field) {
-      // If aliases are longer than 63 chars, postgres cut them off
-      // and Views does cannot find the results. So we need to limit
-      // ourself here and make the aliases <= 63 chars.
-      if (strlen($field['alias']) > 63) {
-        $safe_alias_prefix = substr($field['alias'],0,60);
-        if (array_key_exists($safe_alias_prefix,$too_long_fields)) {
-          $too_long_fields[$safe_alias_prefix]++;
-        } else {
-          $too_long_fields[$safe_alias_prefix] = 0;
-        }
-        $this->fields[$field_name]['safe_alias'] = sprintf("%s_%d",$safe_alias_prefix,$too_long_fields[$safe_alias_prefix]);
-        $field['safe_alias'] = $this->fields[$field_name]['safe_alias'];
-      } else {
-        $this->fields[$field_name]['safe_alias'] = $field['alias'];
-        $field['safe_alias'] = $this->fields[$field_name]['safe_alias'];
-      }

Please make a batch from the head version, not relative to your old patch

I'm on crack. Are you, too?

I have recreated the patch based on the latest devel snapshot.

Lets move this issue to 6.x-3.x version and then back-port it.

I think this should be enough.

Makes more sense.

Can someone with pgsql confirm #16 fixes the problem they were having?

Ok, I have tested with a very long field and get this error:

user warning: Unknown column 'node_data_field_long_name_for_a_field_field_long_name_for_a_field_value' in 'order clause' query: SELECT node.title AS node_title, node.nid AS nid, history_user.timestamp AS history_user_timestamp, node.created AS node_created, node.changed AS node_changed, node.type AS node_type, users.name AS users_name, users.uid AS users_uid, node.status AS node_status, node.uid AS node_uid, node_revisions.format AS node_revisions_format, node_data_field_long_name_for_a_field.field_long_name_for_a_field_value AS node_data_field_long_name_for_a_field_field_long_name_for_a_, node.vid AS node_vid FROM node node LEFT JOIN users users_node ON node.uid = users_node.uid LEFT JOIN history history_user ON node.nid = history_user.nid AND history_user.uid = 1 INNER JOIN users users ON node.uid = users.uid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid LEFT JOIN content_type_page node_data_field_long_name_for_a_field ON node.vid = node_data_field_long_name_for_a_field.vid ORDER BY node_changed DESC, node_data_field_long_name_for_a_field_field_long_name_for_a_field_value ASC LIMIT 0, 20 in drupal-sites/test/modules/views/plugins/views_plugin_query_default.inc on line 1093.

This occurs only when a very long alias is used in the ORDER BY

Lets try this patch

Commited to 3.x in both 7.x and 6.x -- just need to backport to 2.x and that should be trivial.

Untag.

Here it is

Update

remove todo

Patch did not work Views 6.x-2.8.

No longer missing field values, but I still get duplicate alias errors.

Query:

SELECT node.nid AS nid,
   node_node_data_field_relatedinthenewsitem.title AS node_node_data_field_relatedinthenewsitem_title,
   node_node_data_field_relatedinthenewsitem.nid AS node_node_data_field_relatedinthenewsitem_nid,
   node_node_data_field_relatedinthenewsitem_node_data_field_externalname.field_externalname_value AS node_node_data_field_relatedinthenewsitem_node_data_field_ex,
   node_node_data_field_relatedinthenewsitem.type AS node_node_data_field_relatedinthenewsitem_type,
   node_node_data_field_relatedinthenewsitem.vid AS node_node_data_field_relatedinthenewsitem_vid
 FROM node node 
 LEFT JOIN content_field_relatedinthenewsitem node_data_field_relatedinthenewsitem ON node.vid = node_data_field_relatedinthenewsitem.vid
 LEFT JOIN node node_node_data_field_relatedinthenewsitem ON node_data_field_relatedinthenewsitem.field_relatedinthenewsitem_nid = node_node_data_field_relatedinthenewsitem.nid
 LEFT JOIN content_field_externalname node_node_data_field_relatedinthenewsitem_node_data_field_externalname ON node_node_data_field_relatedinthenewsitem.vid = node_node_data_field_relatedinthenewsitem_node_data_field_externalname.vid
 LEFT JOIN content_field_externallink node_node_data_field_relatedinthenewsitem_node_data_field_externallink ON node_node_data_field_relatedinthenewsitem.vid = node_node_data_field_relatedinthenewsitem_node_data_field_externallink.vid
 WHERE node.nid = 14

• warning: pg_query() [function.pg-query]: Query failed: ERROR: table name "node_node_data_field_relatedinthenewsitem_node_data_field_exter" specified more than once in /usr2/home2/www/drupal-6.13/includes/database.pgsql.inc on line 139.
• user warning: query: SELECT node.nid AS nid, node_node_data_field_relatedinthenewsitem.title AS node_node_data_field_relatedinthenewsitem_title, node_node_data_field_relatedinthenewsitem.nid AS node_node_data_field_relatedinthenewsitem_nid, node_node_data_field_relatedinthenewsitem_node_data_field_externalname.field_externalname_value AS node_node_data_field_relatedinthenewsitem_node_data_field_ex, node_node_data_field_relatedinthenewsitem.type AS node_node_data_field_relatedinthenewsitem_type, node_node_data_field_relatedinthenewsitem.vid AS node_node_data_field_relatedinthenewsitem_vid FROM node node LEFT JOIN content_field_relatedinthenewsitem node_data_field_relatedinthenewsitem ON node.vid = node_data_field_relatedinthenewsitem.vid LEFT JOIN node node_node_data_field_relatedinthenewsitem ON node_data_field_relatedinthenewsitem.field_relatedinthenewsitem_nid = node_node_data_field_relatedinthenewsitem.nid LEFT JOIN content_field_externalname node_node_data_field_relatedinthenewsitem_node_data_field_externalname ON node_node_data_field_relatedinthenewsitem.vid = node_node_data_field_relatedinthenewsitem_node_data_field_externalname.vid LEFT JOIN content_field_externallink node_node_data_field_relatedinthenewsitem_node_data_field_externallink ON node_node_data_field_relatedinthenewsitem.vid = node_node_data_field_relatedinthenewsitem_node_data_field_externallink.vid WHERE node.nid = 14 in /usr2/home2/www/*/modules/views/includes/view.inc on line 769.

I tested the patch from #25 on views 6.x-2.8 w/ drupal 6.16 and I'm not seeing the errors from #26 anywhere -- I'm still somewhat new to drupal, so should I be looking somewhere besides the Recent log entries or on a page that would execute these queries?

If you think about dagmars approahc, it cannot work. We have to create different aliases.

I've modified the patch to make the alias shorten from the end rather than the front. This makes the alias more likely to be unique.

maybe this issue is realated to #742006: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

@Josh Waihi
Mh i still think that this will work everytime there could be this example

maaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaan_is_this_a_long_alias
maaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaan_is_this_a_long_alias2
tmaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaan_is_this_a_long_alias

So i guess we need to add a counter at the end to be 100%sure

sure, though I'd like to point out your alias examples are extremely unlikely. CCK is generally the culprit of this bug and there naming schema is something like (though not excatly) [module]_[table]_[table_alias]_[column]_[column_alias] so the closer to the end the alias is, the more unique it becomes.

Also discard #30, that was for another issue sorry.

Part of the reason I never fixed that @fixme in there was that I was afraid there would be some unintended consequence of doing so. That means if we fix that, we'll need to thoroughly test this patch. Are you sure we want to do that here?

Applied the patch in #29 to views-6.x-2.9, without success: it yields different errors for different views.

• Fatal error: Cannot access empty property in /var/www/foo/sites/all/modules/contrib/cck/includes/views/handlers/content_handler_field.inc on line 174
• warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ASC" LINE 16: ORDER BY ASC ^ in /var/www/foo/includes/database.pgsql.inc on line 139.
  user warning: query: SELECT DISTINCT FIRST(DISTINCT(node.nid)) AS nid, FIRST(node_node_data_field_local_authors.title) AS _local_authors_title, FIRST(node_node_data_field_local_authors.nid) AS ield_local_authors_nid, FIRST(node_node_data_field_local_authors.language) AS _authors_language, FIRST(node.type), FIRST(node_data_field_taxo_evszam.field_taxo_evszam_value) AS zam_value, FIRST(node_data_field_taxo_evszam.field_publicationtitle_value) AS alue, FIRST(node_data_field_taxo_evszam.field_publicationtitle_format) AS mat, FIRST(node_data_field_taxo_evszam.field_kotet_oldal_value) AS dal_value FROM mikrobi_node node LEFT JOIN mikrobi_content_field_local_authors node_data_field_local_authors ON node.vid = node_data_field_local_authors.vid INNER JOIN mikrobi_node node_node_data_field_local_authors ON node_data_field_local_authors.field_local_authors_nid = node_node_data_field_local_authors.nid LEFT JOIN mikrobi_content_type_publikacio node_data_field_taxo_evszam ON node.vid = node_data_field_taxo_evszam.vid WHERE (node.status <> 0) AND (node.type in ('publikacio')) AND (node.language in ('hu', '')) AND (node_node_data_field_local_authors.nid = 45) GROUP BY node.nid ORDER BY ASC in /var/www/foo/sites/all/modules/contrib/views/includes/view.inc on line 775.

Without the patch there are no displayed errors, but some of the fields are not displayed, since their views-generated field alias is too long. Anyway, being a PGSQL/Views newbie I don't even know how to continue.

According to the pg documentation: http://www.postgresql.org/docs/8.1/interactive/runtime-config-preset.html, it seems the problem is the max_identifier_length parameter.

max_identifier_length (integer)
Reports the maximum identifier length. It is determined as one less than the value of NAMEDATALEN when building the server. The default value of NAMEDATALEN is 64; therefore the default max_identifier_length is 63.

This parameter is read-only. Cannot be altered after a db connection only if you recompile the whole postgresql.

In the pg 8.4 this value is 63 too, nothing changed. (pg from ubuntu repo).

OK, this bug annoys me severely. The patch in #29 helped to solve a site running on PHP-5.2.14 & PGSQL-8.4.3, but gave the fatal errors mentioned in #34 with PHP-5.2.6-something-weird-that-debian-foolks-have-added-as-revision-number & PGSQL-8.3.11. Digging to the deeps of Views & CCK code, I had to come to the conclusion that the field aliases are simply left off in some cases. Digging even deeper into the problem (beyond my own elbows and my colleague's knees) I had to face that the bug was introduced by the patch in #29 - which has one serious flaw: the $alias will be empty if it's shorter than something, since PHP's substr() is... khm... a bit weird in some cases, especially with negative stuff.

So, here is my solution: do not try to shorten the aliases if they are not _too_ long.

The attached patch is a slightly modified version of the one in #29: it makes the aliases shorter only in the case they are too long (ie. longer than 60 chars). It does solve my problem on both the sites I have mentioned above, so be cautious with those stones.

@@ -833,7 +840,7 @@
     }
 
     if ($field) {
-      $this->add_field($table, $field, $as);
+      $as = $this->add_field($table, $field, $as, $params);
     }
 
     $this->orderby[] = "$as " . strtoupper($order);

Can you explain what this is for?

@Josh Waihi: it was taken from your patch in #29, so I thought you know what you are doing. ;) It was present in dereine's patch in #25, #24, #23, even in dagmar's one in #20, which is the point this was introduced in this issue. Anyway, I don't know what's that for exactly, to be honest.

Hi, add_field function returns an alias. This is needed because you have to sort by alias and not by the original key.

I realized of this after #19.

Hi, I'm kinda new on Drupal and here me and my co-workers had the same problem. We applied the patch in #25 and it fixed the problem for a while but soon we had another issue because the patch makes "collision" aliases (I mean two or more aliases were the same). So we thought about how to make really unique alias, as a result we used MD5 (yes I know, it makes much less readable aliases but are unique...)
So I leave you our patch here(based in #25)

Use md5 is not the right solution, we have to find the raise of the problem for alias collision. (Maybe if you export your view it can help us)

Why is not the right solution? The SQL command is not meant to be interpreted by enybody .. or there is something I'm missing..

I had the same problem an the workaround was to create a theme for the field in the view with the following code:

<?php print  $row->{substr($field->field_alias,0,63)};?>

I know this code doesn't work in the case where two fields have the same truncated prefix, but is not my case and the patch didn't work for me.

@valarauco #40

MD5 alias patch works fine for me and fixes the alias length issue, thanks.

I like the idea of an MD5 patch as it ensures the aliases are unique.

The SQL query can still be viewed and interpreted in the view edit preview panel.

cheers,

DT

Given many tables are shortened by convention when writing manual queries, e.g. it's common place to alias "{node}" as just "n", why not use that within the identifier building to truncate "node_" to "n_", etc? A list of twenty or thirty common one & two-letter aliases could be agreed upon which should shorten some of this, e.g:

This would be beneficial for anyone using hook_views_query_alter(), not just people using PostgreSQL.

@#44 The problem there is an abbreviation may collide with other similarly named tables, e.g fields or content types with similar names.

A 32 character MD5 hash is guaranteed to be unique based on the table / field in question and requires no naming convention.

I'm using the patch from #40 and working well

DT

@#45: Using the term „guaranteed” seems to be a bit harsh: have you _checked_ that _every_ identifier has _unique_ md5sum, with their possible length up to, eg., 128? ;) Instead „guaranteed to be unique” I would only say „likely to be unique with high probability”, or something -- in other words I would not simply and blindly take the assumption that this approach would work without glitches regarding to all the table names and other identifiers out there.

FYI this impacts the calendar module: http://drupal.org/node/1038486

This is not only a postgres issue, I've had that happen in mysql too.

5.0.67-0.dotdeb.1-log

This happened for me with a "group by", generated by views_groupby

Here's a patch that tries to address the problem.

The first part in ->execute could be put in the replacement hook before, but there's no such hook to clean up the results below.

Even though I realize the MD5 solution is not ideal, patch from #40 solved the problem for me.
Thanks to @valarauco.

@madth3

Can you try the patch from killes, too?

Is there a reason why this issue for #49 is on "needs work"?

Still having this problem on Views 6.x-2.12 using MySQL 5.1.41 64-bit.

Any news on this?

The patch committed in #21 is causing problems for Views Calc. See #1163514: Totals not displayed due to truncated variable names.

Changing the version. The patch committed in #21 has the side-effect of introducing potential namespace collisions, for which several solutions have been suggested in the subsequent discussion on this thread. Therefore the issue still needs to be properly fixed in 6.3 before back-porting to 6.2.

/Kevin

It looks like there are 2 choices right now:

1. The MD5 fix in #40 which has been reported to work, but for which there is some concern that we could still have alias collisions;

2. The most recent patch in #49 which killes@www.drop.org submitted but did not set to "needs review" I think because he's concerned that "there's no such hook to clean up the results below"

If creating the unique alias in add_field() as merlinofchaos suggests way back in #7 is the way to go, it seems like we should be able to come to agreement on how to get this fixed.

Can we:

1. Keep the long alias if it's less than 60 characters?
2. Use some simple means of generating an alias if it's >= 60 characters?

merlinofchaos has not chimed in on this since April 2010. If anyone has either tested the patch in #49 and gotten it to work or can supply another patch that would fix this in 6.x-3.x it would be great to get this to "needs review" again soon.

1) Yes. Could be handy if someone wants to debug or just examine their View. It also gives people to option to switch to short aliases for this purpose.
2) Some sort of hash with collision avoidance must be the way to go. If that rules out MD5, then there are others such as SHA1 that would do the job. Is performance an issue? See http://www.php.net/manual/en/function.hash.php#89574 for some figures.

/Kevin

I don't get why we need to use a hash. Wouldn't a static counter work just as well? view_alias_1, view_alias_2, ...

OK, I reread some of the early posts and it seems that a counter is a perfectly valid solution. The question is, which patch should we be testing?

@kmcnamee - I haven't tried any of them. The patch in #49 looks like it's doing something with a counter. It also looks like it's doing a lot more than the patch in #40.

The patch in #40 is the MD5 patch which we will skip for now. The patch in #49 does some clever things but not using MD5 (contrary yo the name of the patch). This patch could be our test candidate. Now, what exactly needs to be tested?I am hoping some of the original authors could chime in on this.

+++ includes/view.inc	(Arbeitskopie)
@@ -720,6 +720,10 @@
+        uksort(&$replacements, 'views_sort_by_length');

Can you explain why this needs to be sorted?

I'm kind of late coming to this topic, only just having upgraded to Views 6.x-3.0-alpha4. But I've got to ask one question:

Has anyone considered how much existing code is going to break due to this change when people upgrade? I'm right now having to go through 5 or 6 custom modules and 20 or 30 .tpl files in order to truncate the identifiers that have been shortened by this change. They worked fine in 6.x-2.x with MySQL, because its limit seems to be 256 characters. But due to the new truncation I'm now having to do a lot of extra work. And the truncated field names are nowhere near as human-readable.

Couldn't the truncation limit have been chosen based on the database engine being used? It would be a pretty simple matter to add a DB API call to return this number on a per-engine basis, retrieving the database variable for pgsql, and just returning the constant 256 for MySQL. That way, people like me, who use MySQL and have field names that work within its ample limits, would not be affected.

Views could also provide an API function that would translate a field's name based on this value, so that instead of doing something like:

  $foo = $node->really_long_identifier;

you could do:

  $id = views_truncate_identifier('really_long_identifier');
  $foo = $node->$id;

@Gribnif - which patch did you apply? The one in #40 or the one in #49?

@marcp: I didn't apply a patch. I'm using views 6.x-3.0-alpha4, which already includes what I believe is #36.

Encountered this issue with postgres 8.4 with drupal 6.22 using views 2.16

Original views query (taken from the query display at the bottom of views):

SELECT node.nid AS nid,
   node_node_data_field_original.nid AS node_node_data_field_original_nid,
   node_node_data_field_original_node_data_field_name.field_name_value AS node_node_data_field_original_node_data_field_name_field_name_value,
   node_node_data_field_original.type AS node_node_data_field_original_type,
   node_node_data_field_original.vid AS node_node_data_field_original_vid,
   node_node_data_field_original.created AS node_node_data_field_original_created,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created
 FROM node node 
 LEFT JOIN content_type_excerpt node_data_field_original ON node.vid = node_data_field_original.vid
 INNER JOIN node node_node_data_field_original ON node_data_field_original.field_original_nid = node_node_data_field_original.nid
 LEFT JOIN content_field_name node_node_data_field_original_node_data_field_name ON node_node_data_field_original.vid = node_node_data_field_original_node_data_field_name.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.status = 1) AND (node.type in ('excerpt'))
   ORDER BY node_created DESC

The above query silently failed, note the alias 'node_node_data_field_original_node_data_field_name_field_name_value' -> this is 68 characters long. When running this query directly against postgres, using raw SQL above, the column returned was 'node_node_data_field_original_node_data_field_name_field_name_v', or in other words 63 characters.

With patch #40 applied, this is views new SQL:

SELECT node.nid AS nid,
   node_node_data_field_original_node_data_field_name.field_name_value AS views_5c38a3c249d7ec2a4f6d1bfa8800a89d,
   node_node_data_field_original.nid AS node_node_data_field_original_nid,
   node_node_data_field_original.type AS node_node_data_field_original_type,
   node_node_data_field_original.vid AS node_node_data_field_original_vid,
   node_node_data_field_original.created AS node_node_data_field_original_created,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created
 FROM node node 
 LEFT JOIN content_type_excerpt node_data_field_original ON node.vid = node_data_field_original.vid
 INNER JOIN node node_node_data_field_original ON node_data_field_original.field_original_nid = node_node_data_field_original.nid
 LEFT JOIN content_field_name node_node_data_field_original_node_data_field_name ON node_node_data_field_original.vid = node_node_data_field_original_node_data_field_name.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.status = 1) AND (node.type in ('excerpt'))
   ORDER BY node_created DESC

Note the absence of the 68 character alias, and the introduction of the MD5.

Regardless of (potential) collisions that the MD5 patch may introduce, it still causes a view that *did not work*, to *work*. So I think this patch is a step forward in that regard.

Keen for this patch to be applied to views, as #40 does address my problem.

Reading the previous comment... About patch in #40:

- For:
- Several reports stating it solved the issue
- No reports saying otherwise

- Against:
- Concern about the possibility of collisions, but no actual reports saying so
- It makes much less readable aliases (a hash), but, according to davidwhthomas in #43, «The SQL query can still be viewed and interpreted in the view edit preview panel.»
- Proposals (with no patch ready for review or even started) for other approaches (e. g. «add a DB API call» in #62 or create a «static counter» in #57).

So, this issue is going on since 2009 and it does have a working patch since 2010. Maybe we should apply it already and deal with related feature requests (readable aliases out of the view edit preview panel) or edge cases with collisions later.

I'm proposing #40 as tested and reviewed by the community. What do you think?

The system is always right... Even I can see now that the #40 patch needs some drupalization...

Hey Guys

Is there any further status update on this?
Im still running into this issue with Drupal 7.14 using Views 3.3 running on PostgreSQL 9.1.

Error message that comes back:

SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "field_collection_item_field_data_field_persoon_ab__field_data_f" specified more than once

Original query:

SELECT node.nid AS nid, field_collection_item_field_data_field_persoon_ab.item_id AS field_collection_item_field_data_field_persoon_ab_item_id, field_data_field_persoon_naam.field_persoon_naam_value AS field_data_field_persoon_naam_field_persoon_naam_value, 'node' AS field_data_field_persoon_status_node_entity_type, 'node' AS field_data_field_persoon_naam_node_entity_type, 'node' AS field_data_field_persoon_voornaam_node_entity_type, 'node' AS field_data_field_persoon_straat_node_entity_type, 'node' AS field_data_field_persoon_postcode_node_entity_type, 'node' AS field_data_field_persoon_plaats_node_entity_type, 'field_collection_item' AS field_data_field_ab_jaar_field_collection_item_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_persoon_ab} field_data_field_persoon_ab ON node.nid = field_data_field_persoon_ab.entity_id AND (field_data_field_persoon_ab.entity_type = 'node' AND field_data_field_persoon_ab.deleted = '0')
LEFT JOIN {field_collection_item} field_collection_item_field_data_field_persoon_ab ON field_data_field_persoon_ab.field_persoon_ab_value = field_collection_item_field_data_field_persoon_ab.item_id
LEFT JOIN {field_data_field_ab_jaar} field_collection_item_field_data_field_persoon_ab__field_data_field_ab_jaar ON field_collection_item_field_data_field_persoon_ab.item_id = field_collection_item_field_data_field_persoon_ab__field_data_field_ab_jaar.entity_id AND (field_collection_item_field_data_field_persoon_ab__field_data_field_ab_jaar.entity_type = 'field_collection_item' AND field_collection_item_field_data_field_persoon_ab__field_data_field_ab_jaar.deleted = '0')
LEFT JOIN {field_data_field_ab_type} field_collection_item_field_data_field_persoon_ab__field_data_field_ab_type ON field_collection_item_field_data_field_persoon_ab.item_id = field_collection_item_field_data_field_persoon_ab__field_data_field_ab_type.entity_id AND (field_collection_item_field_data_field_persoon_ab__field_data_field_ab_type.entity_type = 'field_collection_item' AND field_collection_item_field_data_field_persoon_ab__field_data_field_ab_type.deleted = '0')
LEFT JOIN {field_data_field_persoon_naam} field_data_field_persoon_naam ON node.nid = field_data_field_persoon_naam.entity_id AND (field_data_field_persoon_naam.entity_type = 'node' AND field_data_field_persoon_naam.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('persoon')) AND (TO_CHAR(field_collection_item_field_data_field_persoon_ab__field_data_field_ab_jaar.field_ab_jaar_value, 'YYYY') = '2007') AND (field_collection_item_field_data_field_persoon_ab__field_data_field_ab_type.field_ab_type_value IN  ('bmz')) ))
ORDER BY field_data_field_persoon_naam_field_persoon_naam_value ASC

I know at the heart of this is a PostgreSQL issue, but in the Views plugin code, in the query.default.inc, there is code taking this limit into account, but yet this problem still remains.

Cheers
T

UPDATE

I tried the patch mentioned in #40, and it does fix the aliasses for the SELECT part of the query.
However, as you can see in the above query, it does not affect the aliasses in the LEFT JOIN statements.

You can easily reproduce this by building a view that takes a field collection as a relation and use two field from that field collection as filters. When you try to use both filters at the same time, the above situation happens.

Cheers
T

I'm wondering whether DBTNG in drupal core should care about that in 7.x-3.x, though for 6.x this is in the scope of views to do it.

Oracle only supports aliases up to 30 chars so this patch could be reworked to accommodate that. Or maybe there should be a way to manually suggest aliases for table names instead of just truncating the alias?

Something like:

$data['very_long_column_name']['table']['alias'] = 'vlcn';

It also seems that the first patches only take care of field aliases but ignore table aliases.

Don't you already have problems with tables defined by core? For example i guess defining a field and running a simpletest will already exceed the limit of 30 characters. In general it's somehow your own fault if you use oracle :)

Btw. this patch simply does not work as it is for 7.x

@dawehner: I'm using views with non-Drupal tables. Drupal is living is MySQL and there is content integrated from an external Oracle DB. Currently I just fixed it with the first patch and truncating $relationship when creating the table alias. This currently works but isn't an elegant solution.

Hit this bug in #1787764: Unknown column caused by orderby alias not matching field alias

Reading the thread I see that views needs better aliasing probably md5() is way but not for oracle which needs shorter hash

Talked in IRC with dawehner and come to conclusion that this should be a part of query class to be overridable for special databases ala oracle, and #49 is a way to go

By definition, using a hash means the possibility of collisions. Using a shortened alias with a counter of some kind appended is probably a better way.

Any progress on this? I'm seeing the same problem as Timusan, on Drupal 7.15, Views 3.3, PostgreSQL 9.1. You can pretty much guarantee getting this if you're using two Field Collection fields as filters or sorts.

Hm, can we move this to the drupal project? Maybe somebody can help there, and if this is still present in the 8.x version, we have now a drupal core bug.

Add tag, i guess this could be solved even on the level of dbtng.

Let's move to the database component to see whether someone from the db team has an oppinion whether it's worth to fix on the base level.

A fix at the database level is not really an option. Debian and Ubuntu compile PostgreSQL with this hard limitation on aliases. I don't see why the aliases that views uses need to be the way they are. They are elongated and hard to read. IMO, Views would be better off using sequential aliases like:

SELECT a.title, b.timestamp FROM node a INNER JOIN node_revision b on a.vid = b.vid;

Suppose better scheme for naming to assign a first letter of table with sequental suffix after it. maybe better to use first letters of parts in table name:

first node = n
next node =  n1
node_revision = nr

@andypost
This naming scheme though really is hard for the generic approach. Just imagine all the fieldapi tables, for example the following two tables or no rate configuration:

field_data_field_tags
field_data_field_topics

Sure we could try to find a good fitting solution all the time, though it's a problem if you have a non-easy-deterministic way to find table aliases.

Not sure whether this actually works, but let's see how much is broken with that. In general i'm totally not convinced by this approach/way to truncate the aliases as they would suck.

#72 / #73

Oracle only supports aliases up to 30 chars so this patch could be reworked to accommodate that. Or maybe there should be a way to manually suggest aliases for table names instead of just truncating the alias?

Don't you already have problems with tables defined by core? For example i guess defining a field and running a simpletest will already exceed the limit of 30 characters. In general it's somehow your own fault if you use oracle :)

Pondering the decision between the portability and DB-abstraction way vs. optimizing for the most common DB backend, both had their pros and cons, but we decided for portability and DB-abstraction. Now as a consequence we should aim for maximum portability, and of course that should include Oracle, even if there's still a way to go until we're fully compatible out of the box.
That clearly means we should get along with a maximum length of 30.

While both the hash-only and the truncate-and-index approaches are unique, they're both not self-descriptive (the first never, the latter not necessarily).
Therefore I believe we should revisit Damien McKenna's standard abbreviations approach in #44. We might even be able to leave out the underscores, so it gets really compact while still descriptive. Only if X is too long, we'd need to truncate and index X to avoid going over the 30 characters limit.

d7 version of #86

Re #87: No. Hashes are not unique.

#86: drupal-571548-86.patch queued for re-testing.

It does not make sense to use the drupal version because we just care about ascii chars.

same problem here as @Timusan and @coleman with drupal 7.23 ,views 7.x-3.7

tag "field collection item" is added.
When field collection item is added as relationship to views this will cause
NOTICE: identifier "field_collection_item_field_data_field_actors__field_data_field_actors_list" will be truncated to "field_collection_item_field_data_field_actors__field_data_field" and leads to SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name.

In above example field_data_field_actors is field collection item and field_data_field_actors_list is normal textfield inside fileld collection.

example
Movie(Normal node) ----------------------Inception
-|- Actors(fieldcollection item)------------
-----|--Actors List(multivalue textfiels)----Dicaprio
-----|------------------------------------Tom

Works on PG 9.3, View 3.7, D 7.26

This version is similar to #96 only a) it will apply properly to the codebase, b) it takes the last 60 characters of the alias name instead of the first 60.

Related: #998898: Make sure that the identifiers are not more the 63 characters on PostgreSQL

This has been fixed in Views itself already -- not sure if we need to port the fix to the postgresql driver anymore as this should only affect contrib modules, very rarely?

@stefan.r Not sure I follow you. The changes that make sure they do not overflow the 63 char limit is already committed to the PostgreSQL 8.x core driver, what we do not need/should do, is to have special code in views regarding Postgres?

@steinmb I don't think we fixed table aliases yet in the core patch (#998898: Make sure that the identifiers are not more the 63 characters on PostgreSQL), this only affected indexes/constraints/primary keys. There is however already some special code in Views that makes sure we do not overflow the 63 char limit! :)

From core/modules/views/src/Plugin/views/query/Sql.php:

   // PostgreSQL truncates aliases to 63 characters: http://drupal.org/node/571548

    // We limit the length of the original alias up to 60 characters
    // to get a unique alias later if its have duplicates
    $alias = strtolower(substr($alias, 0, 60));

Ah. Thank you for clarifying. I was, as usual, confused :)

Ah. Thank you for clarifying. I was, as usual, confused :)
A 504 gave me a double post, sorry.

@stefan.r is that core patch fixes issues when views has field collection relationship ? (#70, #79, #95)

Rerolled.

Above rerolled patch has:
1. Empty spaces introduced at the end of line 286.
2. New lines before line 285 & after 286 removed.

Attaching proper reroll.

Created a issue and D7 backport patch for this issue. https://www.drupal.org/node/2492833#comment-9947839

Re-uploading patch for the bots.

#998898-58: Make sure that the identifiers are not more the 63 characters on PostgreSQL Introduced a method: ensureIdentifiersLength for Drupal 8. Are we sure this is still an issue for D8?

If this is still an issue, the proposed solution in #109 is not accurate and should use an approach similar to the implemented in #998898.

Hi,

Looks like it's not enough to cut the alias at 60 chars, because that could cause duplicate aliases.

I've created a patch for 7.x version that tries to solve it.

I found that patch #107 wasn't sufficient when creating a view when I had more than one relationship involving taxonomy term fields within paragraphs on a node so I extended the patch.

@yce:
Thanks for your contribution!
This is however an issue against Drupal core 8.
Patches against Views 7 are welcome in #2492833: truncated identifiers with more than 63 chars, causing Views to break (D7 Backport).

I had the issue on a Drupal 8.9.2 that I'm building for a client, because it had a lot of long field and table names, which resulted in that just cutting it still gives the error.
Here I instead shorten the base table name before creating the relationship. Here only the first letters for the base table is joined together to make room for field tables and column aliases.
This also makes that the sql preview gets shorter and prettier in the views edit.

I found the issue still persists after cutting the length down if the fields.
So instead of cutting down I made a patch which takes the first letters of the base table names and uses that as bases for the alias.
This also makes the preview in views edit easier to look though.

Patch failed to apply.

Re-rolled the patch in #123. Kindly review.

Thank you!

I had this issue with Druapl 8.8.10.

The patch pgsql_views_alias_571548-123-d8.patch works but there are risks of table name collisions and some warning (eg 2 successive "_").

I had this issue with Drupal 8.8.10.

The patch pgsql_views_alias_571548-123-d8.patch works fine but there are risks of tables name collisions and some warning (successive '_' in the original name).

I tried to reproduce this in a test, but I can't get this to fail on Postgres. Am I not reading the steps to break right?

@lendude As requested have a looked at the issue. First: Your test does not fail because the "real table" name is much shorter then 63 characters.
Second: The PostgreSQl driver makes sure that all indexes, constraints, primary keys and sequences have a name that is save. Even when that name is longer then 63 characters. It does not do that for table names, column names and aliases. When you create a table with a name that is longer then 63 characters it just uses the first 63 characters for the table name. I think that the problem for views may come when you have 2 or more table names that are longer then 63 characters and are for the first 63 characters the same. My question to you is: is that a problem for views or not?

Yes, the issue described here is that the table names/aliases are truncated to 63 characters.

@daffie - the problem is absolutely with Views. Views creates it's own aliases for tables during its query creation. In order to test appropriately one would need to create a view with sufficient complexity (i.e. multiple joins) and sufficiently long base table names, that the aliases that Views automatically generates exceeds 63 characters.

This can be achieved via these steps:
# Create a new content node type
# add an entity reference field with a long name like "verbosely named entity reference field"
# create a view to show nodes of this content type
# Add an entity reference relationship in the view based on the "verbosely named entity reference field"
# Add a second entity reference relationship in the view based on the "verbosely named entity reference field"

Execute the view. The name will be truncated by this patch to 61 characters.

Put another way: given that the test-only patch passes on PostgreSQL instead of failing, is it possible that newer releases of PostgreSQL have resolved this problem?

The current PostgreSQL Limits say "identifier length: 63 bytes". It also says "can be increased by recompiling PostgreSQL". Some people could be testing in versions compiled to allow longer.

So do the tests need to check for that setting in PostgreSQL?

It would be best if the d.o. testing infrastructure just doesn't customize that setting. People on their own might have longer allowed, but it would get caught when testing happens here.

The current PostgreSQL Limits say "identifier length: 63 bytes". It also says "can be increased by recompiling PostgreSQL". Some people could be testing in versions compiled to allow longer.

As Drupal we cannot rely on user recompiling their PostgreSQL database. For Drupal it must work with the 63 character limit.

Completely agree with #141.

Can confirm that issue is still present on Drupal 9.1.3 & PostgreSQL 11.7.

@papajo I tried your patch #130 and it was working in one case but failing in a case when both parts of the alias were long, so I slightly modified your patch to pass both parts of the alias string to the shortenTableAlias function. This works for me, but I'm not a Drupal Views expert so I hope it doesn't break anything else.

Outside of core, Oracle 11 has a limit of 30 chars for identifiers, including aliases.

IMHO we should reconsider, as suggested in some comments, trying to solve this at the database API level, as part of escapeAlias() implementation, using hashing techniques to reduce the alias length, encoding the input during SQL preparation. Then, decode the hashed alias back while fetching records.

They have the same problem in the migrate module.

Filed #3200743: [WIP] Decouple identifier management from database Connection, introduce an IdentifierHandler class.

I tried patch 145 and it resolved the character length limitations, but I different errors about non unique aliases. I hash the $table_name and use the first 16 character to ensure uniqueness.

Re-rolled patch. Please review.

It uses a hash to ensure uniqueness, and then reverts the hashed alias in the postExecute().

Fixed custom commands fail in patch #153

Patch 153 was using a php8 function, str_starts_with; replaced with strpos($property, $this->shortAlias) === 0) .

bluegeek9

#155
This works without a function postExecute().
And it doesn't work with it if the result has already been cached. I see empty fields in the table.
What happens if I delete postExecute()?

Uploading working pacth file Drupal version 9.41 and PostgreSQL version 13

Fixed Failed Commands #160
Attached interdiff

Thanks for the patch!

I don't have sufficient understanding of Views to say where it's failing, but the approach used in #155 (and subsequent rerolls) is insufficient. It worked for the View I was working on at the time I applied the patch, but it turned out to be responsible for breaking a different one. The broken View was using aggregation/grouping, which might be relevant.

I didn't know where to start with adapting that patch to work in both scenarios, so I've taken a different approach.

I looked for every piece of code in core/modules/views/src/Plugin/views/query/Sql.php which seemed to be generating an alias value arbitrarily, and I put those through a sanitizer function so that it produces a safe alias value in each case.

I've done something similar to #155 in generating a short hashed prefix (I used crc32 instead of truncating a sha1 value, just because it's already short).

I haven't been remotely so aggressive with the length limit (#155 set that to a tiny 19 chars). I assume that was for the benefit of certain other database products? I'm using Postgres which has a 63 char limit for aliases, so I just kept the 60-char limit that Views was kinda-sorta trying (but failing) to use already.

I've tested all the existing Views on my particular site with this in place. I'm pretty confident I have not exercised all of the affected code paths in that process, so "works for me" is all I can say right now.

Fixed Failed Commands #164
Attached interdiff

Thanks for the updates poojah saraah. I had no idea the testbot was nowadays insisting on dictionary words only in identifier names. (That must have been a fun change when it was implemented!?)

So the test failure we're seeing now is expected -- it's testing a truncated alias, so it's seeing the old style (simple truncation) rather than the patched/uniquified style.

The failed assertion in testNoDataTableRelationship() in core/modules/field/tests/src/Kernel/EntityReference/Views/EntityReferenceRelationshipTest.php is:

      // Test the forward relationship.
      $this->assertEquals(1, $row->entity_test_mul_property_data_entity_test__field_test_data_i);

which is exactly 60 characters. It's not immediately obvious what field_test_data_i was originally, as nothing starts with all of that; but grepping for field_test_data turns up lots of relevant things, so we're looking for "something which starts with 'i'" which really doesn't narrow things down as much as I'd like. Maybe it was "id"?

In any case, I believe the sanitizer needs to be available to the test code for this, as at present it looks to me like the test contains a hard-coded assumption that this previous code was executed:

    // We limit the length of the original alias up to 60 characters
    // to get a unique alias later if its have duplicates
    $alias = strtolower(substr($alias, 0, 60));

As I can't tell from perusing the code what that alias would have been originally, I also can't tell what we would need to pass to the sanitizer in order to fix the test.

The test in question originates here:
https://git.drupalcode.org/project/drupal/-/commit/b58ab8cef820d11e0305e...

I should note that I also looked at #147 and tested performing the sanitization in that escapeAlias() method (which certainly sounded good, albeit also increasing the scope of the issue beyond only Views), but I still saw the same long aliases from Views making it through to Postgres and causing errors, so evidentially escapeAlias() isn't guaranteed to process all aliases (although it was processing some of them, so the code was working where it could).

I applied the 164 and also 165 and it still gives me an error when I turn on aggregation in views

Error message
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c2548530755iations__commerce_product_variation__field_images.field_images_' in 'field list': SELECT COUNT(*) AS "expression" FROM (SELECT "commerce_product_field_data"."title" AS "commerce_product_field_data_title", "c2548530755iations__commerce_product_variation__field_images"."field_images_" AS "c2393567943rce_product_variation__field_images_field_images_", "c3542200800variation_field_data_commerce_product__variations"."sku" AS "c2937206518ation_field_data_commerce_product__variations_sku", "c3009431666tions__commerce_product_variation__commerce_price"."commerce_price_" AS "c2419217350product_variation__commerce_price_commerce_price_", "commerce_product__variations"."variations_target_id" AS "commerce_product__variations_variations_target_id", "c3542200800variation_field_data_commerce_product__variations"."price__number" AS "c4143245525d_data_commerce_product__variations_price__number", "commerce_product_field_data"."product_id" AS "product_id", DATE_FORMAT((DATE_ADD('19700101', INTERVAL c3542200800variation_field_data_commerce_product__variations.created SECOND) + INTERVAL 7200 SECOND), '%Y%m%d%H') AS "c1689386505ld_data_commerce_product__variations_created_hour", MIN(commerce_product_field_data.product_id) AS "product_id_1", MIN(c3542200800variation_field_data_commerce_product__variations.variation_id) AS "c1113239435ld_data_commerce_product__variations_variation_id", 1 AS "expression" FROM "commerce_product_field_data" "commerce_product_field_data" LEFT JOIN "commerce_product__variations" "commerce_product__variations" ON commerce_product_field_data.product_id = commerce_product__variations.entity_id AND commerce_product__variations.deleted = :views_join_condition_0 INNER JOIN "commerce_product_variation_field_data" "c3542200800variation_field_data_commerce_product__variations" ON commerce_product__variations.variations_target_id = c3542200800variation_field_data_commerce_product__variations.variation_id LEFT JOIN "commerce_product_variation__field_images" "c2548530755iations__commerce_product_variation__field_images" ON c3542200800variation_field_data_commerce_product__variations.variation_id = c2548530755iations__commerce_product_variation__field_images.entity_id AND c2548530755iations__commerce_product_variation__field_images.deleted = :views_join_condition_1 LEFT JOIN "commerce_product_variation__commerce_price" "c3009431666tions__commerce_product_variation__commerce_price" ON c3542200800variation_field_data_commerce_product__variations.variation_id = c3009431666tions__commerce_product_variation__commerce_price.entity_id AND c3009431666tions__commerce_product_variation__commerce_price.deleted = :views_join_condition_2 GROUP BY commerce_product_field_data.product_id, commerce_product_field_data_title, c2393567943rce_product_variation__field_images_field_images_, c2937206518ation_field_data_commerce_product__variations_sku, c2419217350product_variation__commerce_price_commerce_price_, commerce_product__variations_variations_target_id, c1689386505ld_data_commerce_product__variations_created_hour, c4143245525d_data_commerce_product__variations_price__number) "subquery"; Array ( [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 [:views_join_condition_2] => 0 )

That original field_images_ (when it selects c2548530755iations__commerce_product_variation__field_images"."field_images_" AS "c2393567943rce_product_variation__field_images_field_images_") is not an alias; so yes -- my patch has no effect on whatever is causing that.

It may very well be a different truncation bug, though. See if you can discover where that is happening, and then open a separate issue for that (and link to it from here).

Could you add a re-rolled patch for 10.1 and 10.2 ?

#165 applies just fine to 10.1. You can check 10.2 and let us know if it doesn't apply.

Following up my comments in #166:

It's not immediately obvious what field_test_data_i was originally ... Maybe it was "id"?

I believe that guess is correct...

EntityReferenceRelationshipTest::setUp() does this:

    // Create reference from entity_test to entity_test_mul.
    $this->createEntityReferenceField('entity_test', 'entity_test', 'field_test_data', 'field_test_data', 'entity_test_mul');

    // Create reference from entity_test_mul to entity_test.
    $this->createEntityReferenceField('entity_test_mul', 'entity_test_mul', 'field_data_test', 'field_data_test', 'entity_test');

Which looks like it's making field_test_data a reference to a entity_test_mul entity, and field_data_test a reference to a entity_test entity.

Both of those entity classes declare:

 *   entity_keys = {
 *     "id" = "id",
 *     "uuid" = "uuid",
 *     "bundle" = "type",
 *     "label" = "name",
 *     "langcode" = "langcode",
 *   },

So id looks like the only property it might be if the name is just a concatenation of field name and property key.

And finally, my interpretation for the truncated name is consistent with other appearances of that comment for the field_data_test field:

3 matches for "Test the forward relationship." in buffer: EntityReferenceRelationshipTest.php
       :
    142:      // Test the forward relationship.
       :      $this->assertEquals(1, $row->entity_test_mul_property_data_entity_test__field_test_data_i);
-------
       :
    222:      // Test the forward relationship.
       :      $this->assertEquals(1, $row->entity_test_entity_test_mul__field_data_test_id);
-------
       :
    281:      // Test the forward relationship.
       :      // $this->assertEquals(1, $row->entity_test_entity_test_mul__field_data_test_id);

So I think the way forward is:

use Drupal\views\Plugin\views\query\Sql;
...
      // Test the forward relationship.
      $alias = Sql::sanitizeAlias('entity_test_mul_property_data_entity_test__field_test_data_id')
      $this->assertEquals(1, $row->{$alias});

So let's give that a whirl (against 10.1.x initially).

----

That test gave us:

Running PHPStan on *all* files.
 ------ ---------------------------------------------------------------
  Line   core/modules/views/src/Plugin/views/query/Sql.php
 ------ ---------------------------------------------------------------
  525    Variable $alias in isset() always exists and is not nullable.

Which is complaining about the pre-existing (not part of this patch) code in Drupal\views\Plugin\views\query\Sql::queueTable():

    // If no alias is specified, give it the default.
    if (!isset($alias)) {
      $alias = $this->tables[$relationship][$table]['alias'] . $this->tables[$relationship][$table]['count'];
...

The "always exists" is certainly accurate, but on first look I'm not convinced by the "is not nullable" part. PHPStan must have a reason for claiming otherwise (and I'd tend to assume it's right and I'm wrong), but the reason is sufficiently non-obvious that I can only question the value of eliminating safeguard code on that basis.

By my reading, if we had two calls to queueTable($table, $relationship = NULL, JoinPluginBase $join = NULL, $alias = NULL) using the same table and relationship arguments, and if the second of those calls had a NULL alias argument, then that null could reach the isset() call. Joining to a database table twice with different aliases and join conditions is 100% a valid thing to do in a SQL query (I wouldn't even call it uncommon), so I see no reason why in principle this couldn't happen.

It does seem like an empty string alias shouldn't be considered valid, though (especially as the alias is used as an array key, and null array keys are coerced to empty strings), so I'll change that !isset() to empty() so that we can catch that case and hopefully prevent PHP Stan from asking us to delete a pre-existing safeguard.

Attempting to work around the PHP Stan complaint.

Re-roll of #179 for 10.2.x and 11.x.

(The testbot seems generally unhappy right now, so we'll need to re-test later, once https://www.drupal.org/node/3060/qa has a green light.)

Recommend turning to MR as patches are being phased out.

But appears to have several test failures.

Also issue summary should follow standard issue template.

Thanks!

> Recommend turning to MR as patches are being phased out.

The MR workflow at drupal.org continues to not provide persistent immutable patch URLs which is hugely problematic when it comes to referencing patches externally, so I'm sure the old method isn't going anywhere while that flaw remains. So long as the traditional patch files are supported I'll prefer to keep using them.

> appears to have several test failures.

Of the tested Drupal versions (10.1, 10.2, 11.x) only 10.1 seems to be currently passing its own tests (sans patches), and against that version this patch passed testing too. AFAICT the vast number of test failures on 10.2 and 11 are entirely unrelated to this patch, and there doesn't seem any point in re-testing until those branches start passing tests on their own. (From a brief look, the problems appear to be related to disk-space on the test servers rather than code; but regardless, there seems little purpose to re-queueing tests just now.)

> Also issue summary should follow standard issue template.

Indeed, that would be good.

The MR workflow at drupal.org continues to not provide persistent immutable patch URLs which is hugely problematic when it comes to referencing patches externally, so I'm sure the old method isn't going anywhere while that flaw remains. So long as the traditional patch files are supported I'll prefer to keep using them.

Please follow this issue #3412417: Disable DrupalCI testing regarding the DrupalCI deprecation.

That said, you will be able to upload patches like any other files in the future, but no other tasks regarding testing/DrupalCI would work after specific date on these files.

Based on that issue, I'm not confident that the 10.2.x and 11.x core branches are going to pass their traditional testing anytime soon, so I've made a MR for testing #180 on those versions.

Can you update to 11.x as the target. It can be backported on commit but has to land on 11.x first.

But see 10.2 is green so that’s good

Unless I'm missing something, the MR workflow more or less necessitates separate branches for testing against each version of Drupal as it's no longer a simple patch that's being applied, so I've created 10.2.x and 11.x branches for this.

Well, there's a MR test failure for "PHPUnit Functional Javascript 1/2" which recurred on a retry, but there's no Javascript involvement here, so I'm setting this back to Needs Review.

Edit: Now re-tested without seeing that false-positive failure.

Edit2: Actually, it's still happening for some pipeline runs.