Problem/Motivation

The new PostgreSQL 12 DrupalCI environment has numerous failures that do not happen on any of the Postgres 9 or 10 environments: https://www.drupal.org/pift-ci-job/1605867

They all seem to be related to datetime functionality in Views. For example:

1) Drupal\Tests\datetime\Kernel\Views\ArgumentDateTimeTest::testDatetimeArgumentYear
Drupal\Core\Database\DatabaseExceptionWrapper: Exception in test_argument_datetime[test_argument_datetime]: SQLSTATE[22007]: Invalid datetime format: 7 ERROR:  invalid value "T2" for "HH24"
DETAIL:  Value must be an integer.: SELECT node_field_data.nid AS node_field_data_nid, node_field_data.nid AS nid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_date} node__field_date ON node_field_data.nid = node__field_date.entity_id AND (node__field_date.deleted = :views_join_condition_0 AND node__field_date.langcode = node_field_data.langcode)
WHERE (TO_CHAR((TO_TIMESTAMP(node__field_date.field_date_value, 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '39600 SECONDS'), 'YYYY') = :node__field_date_field_date_value_year)
ORDER BY node_field_data_nid ASC NULLS FIRST; Array
(
    [:node__field_date_field_date_value_year] => 2000
    [:views_join_condition_0] => 0
)


/var/www/html/core/modules/views/src/Plugin/views/query/Sql.php:1541
/var/www/html/core/modules/views/src/ViewExecutable.php:1426
/var/www/html/core/modules/views/tests/src/Kernel/ViewsKernelTestBase.php:124
/var/www/html/core/modules/datetime/tests/src/Kernel/Views/ArgumentDateTimeTest.php:58
/var/www/html/vendor/phpunit/phpunit/src/Framework/TestResult.php:691

Proposed resolution

TBD

Remaining tasks

TBD

User interface changes

API changes

Data model changes

Release notes snippet

CommentFileSizeAuthor
#10 3118591-10.patch1.48 KBalexpott
#10 8-10-interdiff.txt797 bytesalexpott
#8 3118591-8.patch715 bytesalexpott

Comments

xjm created an issue. See original summary.

xjm’s picture

xjm
she/her
Primary language English
commented
Parent issue: #3118454: Drupal\KernelTests\Core\Database\SelectTest fails on postgres 10 »
Related issues: +#3118454: Drupal\KernelTests\Core\Database\SelectTest fails on postgres 10
chesnut’s picture

chesnut commented

We had this same issue. The problem is in the file PostgresqlDateSql.php in the core views module.
(core/modules/views/src/Plugin/views/query/PostgresqlDateSql.php)

the TO_TIMESTAMP function in PostgreSQL 12 changed on how it processes the formats. I don't know the full details but if the incoming string has a Character that is not in the format as "Optional" it is not ignored and it throws an error.

The easy fix is to modify the format string and make "T" optional. I've tested this in PostgreSQL 9.5 and in PostgreSQL 12.x Seems to work. I tests with string of format "2017-02-10T10:08:00" and "2017-02-10 10:08:00" manually in SQL.

Line 69 should read: return "TO_TIMESTAMP($field, 'YYYY-MM-DD\"T\"HH24:MI:SS')"; instead of return "TO_TIMESTAMP($field, 'YYYY-MM-DD HH24:MI:SS')";

My colleague should post a patch file shortly.

catch’s picture

catch
he/him
Primary language English
commented
Related issues: +#2923131: Views date field/timezone breaks in timezones that have varying offset, e.g. BST

Adding another Views date-related issue.

It would be really good to have a follow-up to move the postgres-specific logic in Views to the postgres database driver.

daffie’s picture

daffie commented

I agree with @catch that this belongs PostgreSQL database driver, only not in the driver directory, but as a plugin in the module part of the PostgreSQL database driver.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

daffie’s picture

daffie commented
Issue tags: +Bug Smash Initiative
alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented
StatusFileSize
new 3118591-8.patch715 bytes
alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented
Status: Active » Needs review
alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented
StatusFileSize
new 8-10-interdiff.txt797 bytes
new 3118591-10.patch1.48 KB

Let's fix the unit test too...

krzysztof domański’s picture

krzysztof domański
Location Poland
commented
Status: Needs review » Reviewed & tested by the community
andypost’s picture

andypost
he/him
Primary language Russian
commented 
+++ b/core/modules/views/src/Plugin/views/query/PostgresqlDateSql.php
@@ -66,7 +66,7 @@ public function __construct(Connection $database) {
-      return "TO_TIMESTAMP($field, 'YYYY-MM-DD HH24:MI:SS')";
+      return "TO_TIMESTAMP($field, 'YYYY-MM-DD\"T\"HH24:MI:SS')";

It needs fix for 9.x too

  • catch committed 1111de8 on 9.2.x 
    Issue #3118591 by alexpott, xjm, chesnut: Datetime-related test failures...

  • catch committed a5c2419 on 9.1.x 
    Issue #3118591 by alexpott, xjm, chesnut: Datetime-related test failures...

  • catch committed ea1e22a on 9.0.x 
    Issue #3118591 by alexpott, xjm, chesnut: Datetime-related test failures...

  • catch committed 3cd89e1 on 8.9.x 
    Issue #3118591 by alexpott, xjm, chesnut: Datetime-related test failures...
catch’s picture

catch
he/him
Primary language English
commented
Status: Reviewed & tested by the community » Fixed

Opened #3182793: Move PostgreSQL-specific views logic to the postgres database driver.

Committed/pushed to 9.2.x and cherry-picked back through to 8.9.x, thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.