This is a backport of a Views bugfix described in the issue quere:
https://www.drupal.org/node/571548

Problem/Motivation

Postgresql can not handle aliases longer than 63 characters. When constructing complex views, especially those with entity references added, the default method of assigning aliases can exceed 63 characters, causing the views to break on PostgreSQL.

Proposed Resolution

Check for alias length when assigning the alias, and truncate to 60 characters, before adding increments for aliases when duplicate tables are requested. In D7 views there are two methods in "views_plugin_query_default" which assign aliases, ::add_relationship() amd ::queue_table(). ::queue_table() is also called by ::add_table().

Remaining tasks

- review and test

User interface changes

None.

API changes

None

CommentFileSizeAuthor
#18 views_postgres_long_aliases-2492833-18.patch1.23 KBrobertwb
#16 views_postgres_long_aliasa-2492833-16.patch1.32 KBgbirch
#15 views_postgres_long_aliases-2492833-15.patch1.23 KBrobertwb
#12 views_postgres_long_aliases-2492833-12.patch3 KBrobertwb
#9 2492833-09.patch1.21 KBrobertwb
#5 drupal-2492833-05.patch1.74 KBrobertwb
#1 drupal-2492833-01.patch647 bytesrobertwb

Comments

robertwb’s picture

robertwb commented
StatusFileSize
new drupal-2492833-01.patch647 bytes
joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Status: Active » Needs review

Has patch, needs review. @robertwb thanks for posting this!

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Issue tags: +Needs tests

Likely could use some simpletests to show this is broken. But I tested this manually and it doesn't seem to help with my case.

Does this look to you like the same issue:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS field_product_commerce_product__field_data_field_product_ima, commerce_produc' at line 1

Happens when aggregation is turned on.

SELECT DISTINCT commerce_product_field_data_commerce_product.type AS commerce_product_field_data_commerce_product_type, field_product_commerce_product__field_data_field_product_image. AS field_product_commerce_product__field_data_field_product_ima, commerce_product_field_data_commerce_product__field_data_field_product_size.field_product_size_tid AS commerce_product_field_data_commerce_product__field_data_fie, commerce_product_field_data_commerce_product__field_data_field_original_sku.field_original_sku_value AS commerce_product_field_data_commerce_product__field_data_fie_1, commerce_line_item_field_data_commerce_line_items.line_item_id AS commerce_line_item_field_data_commerce_line_items_line_item_, flagging_node.entity_id AS flagging_node_entity_id, field_product_commerce_product.nid AS field_product_commerce_product_nid_1, commerce_line_item_field_data_commerce_line_items__field_data_commerce_unit_price. AS commerce_line_item_field_data_commerce_line_items__field_dat, commerce_line_item_field_data_commerce_line_items.quantity AS commerce_line_item_field_data_commerce_line_items_quantity, commerce_line_item_field_data_commerce_line_items__field_data_commerce_total. AS commerce_line_item_field_data_commerce_line_items__field_dat_1, commerce_product_field_data_commerce_product.title AS commerce_product_field_data_commerce_product_title, commerce_line_item_field_data_commerce_line_items.type AS commerce_line_item_field_data_commerce_line_items_type, commerce_order.order_id AS order_id, MIN(field_product_commerce_product.nid) AS field_product_commerce_product_nid, 'node' AS field_data_field_product_image_node_entity_type, MIN(commerce_product_field_data_commerce_product.product_id) AS commerce_product_field_data_commerce_product_product_id, 'commerce_product' AS field_data_field_product_size_commerce_product_entity_type, 'commerce_product' AS field_data_field_original_sku_commerce_product_entity_type, MIN(commerce_line_item_field_data_commerce_line_items.line_item_id) AS commerce_line_item_field_data_commerce_line_items_line_item__1, 'commerce_line_item' AS field_data_commerce_unit_price_commerce_line_item_entity_typ, 'commerce_line_item' AS field_data_commerce_total_commerce_line_item_entity_type
FROM
{commerce_order} commerce_order
INNER JOIN {field_data_commerce_line_items} field_data_commerce_line_items ON commerce_order.order_id = field_data_commerce_line_items.entity_id AND (field_data_commerce_line_items.entity_type = 'commerce_order' AND field_data_commerce_line_items.deleted = '0')
INNER JOIN {commerce_line_item} commerce_line_item_field_data_commerce_line_items ON field_data_commerce_line_items.commerce_line_items_line_item_id = commerce_line_item_field_data_commerce_line_items.line_item_id
LEFT JOIN {field_data_commerce_product} commerce_line_item_field_data_commerce_line_items__field_data_commerce_product ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.deleted = '0')
INNER JOIN {commerce_product} commerce_product_field_data_commerce_product ON commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.commerce_product_product_id = commerce_product_field_data_commerce_product.product_id
LEFT JOIN {field_data_field_product} commerce_product_field_data_commerce_product__field_data_field_product ON commerce_product_field_data_commerce_product.product_id = commerce_product_field_data_commerce_product__field_data_field_product.field_product_product_id
LEFT JOIN {node} field_product_commerce_product ON commerce_product_field_data_commerce_product__field_data_field_product.entity_id = field_product_commerce_product.nid
LEFT JOIN {flagging} flagging_node ON field_product_commerce_product.nid = flagging_node.entity_id AND (flagging_node.fid = '1' AND flagging_node.uid = '317' AND flagging_node.sid = '0')
LEFT JOIN {field_data_field_product_image} field_product_commerce_product__field_data_field_product_image ON field_product_commerce_product.nid = field_product_commerce_product__field_data_field_product_image.entity_id AND (field_product_commerce_product__field_data_field_product_image.entity_type = 'node' AND field_product_commerce_product__field_data_field_product_image.deleted = '0')
LEFT JOIN {field_data_field_product_size} commerce_product_field_data_commerce_product__field_data_field_product_size ON commerce_product_field_data_commerce_product.product_id = commerce_product_field_data_commerce_product__field_data_field_product_size.entity_id AND (commerce_product_field_data_commerce_product__field_data_field_product_size.entity_type = 'commerce_product' AND commerce_product_field_data_commerce_product__field_data_field_product_size.deleted = '0')
LEFT JOIN {field_data_field_original_sku} commerce_product_field_data_commerce_product__field_data_field_original_sku ON commerce_product_field_data_commerce_product.product_id = commerce_product_field_data_commerce_product__field_data_field_original_sku.entity_id AND (commerce_product_field_data_commerce_product__field_data_field_original_sku.entity_type = 'commerce_product' AND commerce_product_field_data_commerce_product__field_data_field_original_sku.deleted = '0')
LEFT JOIN {field_data_commerce_unit_price} commerce_line_item_field_data_commerce_line_items__field_data_commerce_unit_price ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_unit_price.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_commerce_unit_price.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_unit_price.deleted = '0')
LEFT JOIN {field_data_commerce_total} commerce_line_item_field_data_commerce_line_items__field_data_commerce_total ON commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.entity_id AND (commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.entity_type = 'commerce_line_item' AND commerce_line_item_field_data_commerce_line_items__field_data_commerce_total.deleted = '0')
WHERE (( (commerce_line_item_field_data_commerce_line_items.type IN ('product_discount', 'product')) AND (commerce_order.order_id = '106' ) ))
GROUP BY order_id, field_data_field_product_image_node_entity_type, field_data_field_product_size_commerce_product_entity_type, field_data_field_original_sku_commerce_product_entity_type, field_data_commerce_unit_price_commerce_line_item_entity_typ, field_data_commerce_total_commerce_line_item_entity_type, commerce_line_item_field_data_commerce_line_items.line_item_id
ORDER BY commerce_product_field_data_commerce_product_type ASC, commerce_product_field_data_commerce_product_title ASC, commerce_line_item_field_data_commerce_line_items_type ASC

robertwb’s picture

robertwb commented

Just a quick note - updated patch coming, but this current version does not work with relationships that are joined to other relationships since they call a different add_ handler. This may be the same case with fields to related entities (your case has this it seems). I believe that this need may apply to the D8 versions as well.

As for aggregation, I have not tested that at all. So, for tests we need 1) base case of related entity, 2) fields on a related entity, 3) related to a related entity .

robertwb’s picture

robertwb commented
StatusFileSize
new drupal-2492833-05.patch1.74 KB

The attached patch has been briefly tested with case #3 - linking relationship to another related entity. It has not been tested with fields.

robertwb’s picture

robertwb commented
Parent issue: » #571548: Identifiers longer than 63 characters are truncated, causing Views to break on Postgres
robertwb’s picture

robertwb commented

This patch in #5 has been tested more extensively and fails with FILTER conditions, i.e., those things appearing in the "WHERE" clause on tables joined via a relationship (the base table of the view seems unaffected?). For example, when a second iteration of a table is joined, for some reason any filters applied to this table get a null string as their table alias:

Query 1: Working prior to patch #5.

WHERE (my_entity.bundle IN  ('bundle1'))

Query 1: After applying patch #5.

WHERE (.bundle IN  ('bundle1'))
robertwb’s picture

robertwb commented
Status: Needs review » Needs work

Bug found when applying filters to multiple joins on the same feature.

robertwb’s picture

robertwb commented
StatusFileSize
new 2492833-09.patch1.21 KB

Attached patch 2492833-09.patch fixes the above problem. I had added code in both "add_table" and "queue_table" methods causing a double aliasing in some (all?) cases. This has been tested a small bit and found to be working.

robertwb’s picture

robertwb commented
Status: Needs work » Needs review

Patch has been tested by author of most recent version - now needs further review.

robertwb’s picture

robertwb commented
Issue summary: View changes
robertwb’s picture

robertwb commented
StatusFileSize
new views_postgres_long_aliases-2492833-12.patch3 KB

Re-rolled this patch against the latest dev branch, renamed to fit convention.

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Status: Needs review » Needs work 
  1. +++ b/plugins/views_plugin_query_default.inc
@@ -476,6 +477,14 @@ class views_plugin_query_default extends views_plugin_query {
+	  // now adjust alias for length

    This comment could use some updating. 

  2. +++ b/plugins/views_plugin_query_default.inc
@@ -1205,7 +1214,9 @@ class views_plugin_query_default extends views_plugin_query {
-        $non_aggregates[] = $fieldname;
+        if (!in_array($fieldname, $non_aggregates)) {
+          $non_aggregates[] = $fieldname;
+        }

@@ -1213,10 +1224,15 @@ class views_plugin_query_default extends views_plugin_query {
+        if (!in_array($fieldname, $non_aggregates)) {
+          $non_aggregates[] = $fieldname;
+        }

    Sneaked in from another patch?

robertwb’s picture

robertwb commented

> Sneaked in from another patch?

yeah, likely -- I need to make sure that I am rolling that against a clean dev install, and given the time lapse since posting, that is probably in order anyhow.

robertwb’s picture

robertwb commented
Status: Needs work » Needs review
StatusFileSize
new views_postgres_long_aliases-2492833-15.patch1.23 KB

Removed extraneous code and re-rolled against latest dev.

gbirch’s picture

gbirch commented
StatusFileSize
new views_postgres_long_aliasa-2492833-16.patch1.32 KB

Re-rolled against Views 7.x-3.20

Status: Needs review » Needs work

The last submitted patch, 16: views_postgres_long_aliasa-2492833-16.patch, failed testing. View results

robertwb’s picture

robertwb commented
StatusFileSize
new views_postgres_long_aliases-2492833-18.patch1.23 KB

#16 would not apply (says testbot), think it needed to be rolled against latest from git. Just re-rolled there and now attaching patch for testing.

mbnsorg’s picture

mbnsorg commented

#18 worked for us. Thanks!