PostgreSQL: Fix tests in views test group

+++ b/core/modules/views/src/Tests/Handler/AreaTitleWebTest.php
@@ -51,8 +51,9 @@ public function testTitleText() {
-      'value' => ['value' => '042118160112'],
...
+      'value' => ['value' => mt_rand(1000000000, 2000000000)],

I don't think we should be adding a mt_rand() call here. Also, asserting a random value does not exist introduces a risk of random failures when the condition happens to be true, so this does not necessarily seem like the right fix.

Why exactly is this change needed to get Postgres passing? What is invalid about 042118160112?

Ah, I see, so the value is larger than the field allows in Postgres. @dawehner will be sad. :)

I checked in this test (see ViewTestBase::enableViewsTestModule()). The schema for this table is retrieved from state in views_test_data_schema(). The schema is defined by ViewTestData::schemaDefinition(), and the dataset is provided by ViewTestData::dataSet().

So the ID field is an autoincremented field, with five rows by default. Setting it to a high value seems reasonable, but with a negative assertion, it seems like it would be better to assert a specific value that does not exist, so we know immediately in the future if the dataset changes, to avoid the risk of either a random fail or a false positive. Pairing it with a true assertion (for valid data) will verify this.

Patch attached. :) Let us know if this still fixes the fail on postgres.

So here is what the generated query looks like on MySQL:

SELECT taxonomy_term_field_data.name AS taxonomy_term_field_data_name, taxonomy_term_field_data.vid AS taxonomy_term_field_data_vid, taxonomy_term_field_data.tid AS taxonomy_term_field_data_tid, node_taxonomy_term_data.nid AS node_taxonomy_term_data_nid, taxonomy_term_data.tid AS taxonomy_term_data_tid, taxonomy_term_data.tid AS tid
FROM 
{taxonomy_term_data} taxonomy_term_data
LEFT JOIN {node} node_taxonomy_term_data ON (SELECT nodeINNER.nid AS nidINNER
FROM 
{node} nodeINNER
LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON nodeINNER.nid = taxonomy_indexINNER.nid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_nodeINNER ON taxonomy_indexINNER.tid = taxonomy_term_data_nodeINNER.tid
INNER JOIN {taxonomy_term_field_data} taxonomy_term_data_node__taxonomy_term_field_dataINNER ON taxonomy_term_data_nodeINNER.tid = taxonomy_term_data_node__taxonomy_term_field_dataINNER.tid
WHERE (( (taxonomy_term_data_node__taxonomy_term_field_dataINNER.tid = taxonomy_term_field_data.tid ) ))
ORDER BY nodeINNER.nid DESC
LIMIT 1 OFFSET 0) = node_taxonomy_term_data.nid
INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data ON taxonomy_term_data.tid = taxonomy_term_field_data.tid
ORDER BY taxonomy_term_data_tid DESC
LIMIT 10 OFFSET 0

So looking at that test, my first guess is looking at what's weird/special about that test view, which is the groupwise_max plugin. I started by looking in there, and the INNER are actually being added deliberately for namespacing the field... they're not for joins actually! See in the GroupwiseMax Views plugin.

I have two guesses -- either postgres does not support groupwise max the same way that mysql does, or INNER is being caught as a keyword even though it's actually being part of the field/table aliases?

OK, so it's good to know that the query is *mostly* the same, whether on MySQL or PostgreSQL -- in fact, when I take the MySQL query and run it manually on PostgreSQL, I get the same error.

The error seems to be tied to the condition in the WHERE clause... namely:

WHERE taxonomy_term_data_node__taxonomy_term_field_dataINNER.tid = taxonomy_term_field_data.tid

PostgreSQL is complaining that there's no FROM clause for the taxonomy_term_field_data table. I'm a bit unclear on what the intention of the query itself is here, but I can see two possible scenarios:

• That it's really meant to be referring to the taxonomy_term_data_node__taxonomy_term_field_dataINNER alias for the taxonomy_term_field_data table -- in which case, PostgreSQL requires that we use the alias instead of the table name itself. This doesn't exactly make sense in the WHERE clause, as it's essentially saying "the tid on the taxonomy_term_field_data table is equal to the tid on the alias to the taxonomy_term_field_data table", which should always be true.
• That the reference to taxonomy_term_field_data.tid is referring to another instance of the taxonomy_term_field_data table, and we're somehow missing a JOIN.

In short, the query *works* on MySQL, but I'm not sure it's doing what it's actually supposed to be doing.

Any clarification you can add would be helpful -- this is my first time trying to debug Views sql statements.

This doesn't exactly make sense in the WHERE clause, as it's essentially saying "the tid on the taxonomy_term_field_data table is equal to the tid on the alias to the taxonomy_term_field_data table", which should always be true

Well, not exactly -- they're two different aliases for the same table. So it's saying "In the subquery, pick the node with the maximum nid for the term with the tid that matches each one in the ON statement from the outer query." I think. :)

Original feature request: #470258: Groupwise maximum ('representative') relationships
When it was broken before the VDC merge: #1799040: Fixed groupwise max relationship

A way to test the view is by copying the config from views.view.test_groupwise_term.yml into the "single import" for config at /admin/config/development/configuration/single/import. Just to make sure the feature is actually working on MySQL still, I created taxonomy terms 'red', 'blue', and 'yellow'; and tagged miscellaneous nodes with these colors. The view showed a single row with each color, in each case the node with the highest nid tagged with that color.

BTW, reading GroupwiseMax::leftQuery() is... classic. Maybe around line 294 is where the unrecognized alias table name is getting substituted in? There's a note there specifically that it's deliberately circumventing SelectQuery.

FWIW, I would be unopposed to moving this particular plugin into contrib if we can't get it working on Postgres/SQLite but we get everything else working on those databases. So it might be best to fix other Views failures first, and file a separate issue for GroupwiseMax being broken on Postgres. (I'd also be interested to know whether it works on SQLite in that issue.)

Also #1417090-20: Taxonomy term "Representative node" views with filters and sorts don't work might be relevant.

Actually here is that patch for D8: #2379423: Representative Node Views fails due to invalid SQL

The patch from #2379423: Representative Node Views fails due to invalid SQL makes no difference on the tests on PostgreSQL using the drupalci infrastructure. I've attached a list of the tests, and it is exactly the same with or without that patch. So I'm going to set that aside for a moment, and dive back into the query itself.

I better understand what's going on with the query now -- and I have some ideas. First, I've found that the reason that the reference to the taxonomy_term_field_data table is failing in PostgreSQL is that the taxonomy_term_field_data table doesn't get joined until after the subquery. If I move the INNER JOIN (on the taxonomy_term_field_data table) above the LEFT JOIN (which contains the subquery), then the query works as expected in both PostgreSQL and MySQL.

My second comment (which probably belongs somewhere else in its own issue) is that using a correlated subquery to find the groupwise maximum is usually fairly inefficient, as the subquery has to run for each row returned by the outer query. A more efficient method is typically to use a self-JOIN and a HAVING clause to filter out all but the requested record per group.

For example, assuming you have a table of countries (with the name of the country, it's population, and its continent -- just like the sample MySQL data available at https://dev.mysql.com/doc/world-setup/en/) and you want to find the country with the highest population in each continent, you could use the following query:

SELECT 
  co1.Continent, 
  co1.Name   
FROM 
    Country AS co1 
  JOIN 
    Country AS co2
  ON co1.Continent = co2.Continent  
GROUP BY co1.Continent, co1.Name, co1.Population 
HAVING co1.Population = MAX(co2.Population) 
ORDER BY co1.Population DESC; 

Also, PostgreSQL has a more efficient syntax for creating a groupwise maximum... the DISTINCT ON syntax. The same query above would be rewritten as follows to use the DISTINCT ON syntax:

SELECT 
  DISTINCT ON ("Continent") 
  "Continent", 
  "Name" 
FROM 
  "Country" 
ORDER BY "Continent", "Population" DESC;

I haven't begun to wrap my head around how to fix this Views plugin to do the right thing here, but I'm leaving this information in hopes that someone else will come along and know what to do.

This is related to capital letters issue, but the patch in that issue didn't fix this. As soon as I quote/escape those table aliases, the query works.

Adding escaping in addJoin would require a lot of string manipulation because the $condition argument is a string provided by the caller. I don't know about stuffing more things into the driver, which was my first approach, but the Views plugin could do the escaping first as well in the patch in the linked issue.

I'll make this comment over there as well.

This is related to capital letters issue, but the patch in that issue didn't fix this. As soon as I quote/escape those table aliases, the query works.

I tried this, but it didn't work for me, even when the table names and aliases were quoted.