Product attribute adjustment page has PDOException with PostgreSQL and SQL Server

I can't reproduce this. How many options do each of your attributes have?

Which database server are you using? MySQL, Postgres, something else?

Drupal has a database abstraction layer that is supposed to insulate modules like Ubercart from the differences between various databases. The SQL query that's causing the problem isn't actually constructed by Ubercart, it's formed by the database abstraction layer given the parameters that Ubercart specifies. If there's a grouping error (your first error) or a syntax error (your second error), that indicates the database abstraction layer didn't do a good job of constructing the query for PostgreSQL. Likewise, almost any error of this type where a query fails on PostgreSQL but works on MySQL indicates a problem with the Drupal database API implementation, not with the module (Ubercart) doing the query. Therefore, you should first check the Drupal core issue queue for any open (or closed) issues similar to what you're reporting and see if there is a proposed solution. If not, you should isolate the exact SQL that is being generated by the database abstraction layer, as well as the module code (e.g. Ubercart code) that is being used, and submit that as a bug report in the core Drupal issue queue.

In the future, realize that as a PostgreSQL user, you are in a small minority. ANY database error you see is potentially, even likely, due to Drupal's PostgreSQL support. Because of this, the FIRST thing you should mention when submitting a bug report about a database error is that you're using PostgreSQL.

Better title, no more info required, now we need someone with Postgres to debug this and determine if we should fix it in Ubercart or Drupal core.

In this case, it's almost certainly a core issue, but we may be able to avoid it by rewriting the query (which is something that we shouldn't have to do, as it will make for fragile and seemingly arbitrary code).

One of PostgreSQL's quirks is that if you group the query results, you must specify every column of the result set in your GROUP BY. However, the Drupal database API doesn't require a groupBy() for every column - it's the job of the DB abstraction layer to fill in all the non-specified column names when it generates the SQL for PostgreSQL. The original poster already tried the first-line fix of specifying groupBy('a.aid') explicitly to circumvent the failure of the DB abstraction layer in this instance, which fixed the initial problem but lead to a syntax error later down the road. This is where it starts to get touchy - we're trying to tweak the generated SQL by making small, non-functional changes at the API level, in effect trying to guess how to force the SQL to be what we want by what I term "asking the same question" in a different way. I'm sure this can be done at the Ubercart level if we had someone with PostgreSQL willing to work on it, but I'm also sure this is the wrong place to fix it in the long run.

Has anyone been able to fix this problem? I'm also using PostgreSQL and get this error.

Wow. Postgres requires ON conditions for INNER JOIN, even when you don't want to reduce the number of results. Postgres provides CROSS JOIN to make that easier, but I don't think Drupal's DB API does. But we can probably say

$query->join('uc_product_options', "po$i", '(TRUE)');

The Postgres documentation mentions this, and I'll test it myself since I'm using Postgres.

Any resolution or work-around for this?

I need attributes to offer trial discounts to webapps. Using drupal as the front-end for subscriptions.

Peter

Noted in #2044223: SQL Server Compatiblity that SQL Server has the same issue with this query.

Patch for PostgreSQL is simple: add line #1221, ->groupBy('a.aid') in function uc_product_adjustments_form, in file uc_attribute.admin.inc.

vitalijus.trainys@gmail.com: According to #2, adding ->groupBy('a.aid') will cause a syntax error.

If you would like to help out on this issue, what we need is a PATCH. A patch will allow the automated tests to check the proposed modification, will allow other PostgreSQL and SQL Server users to test your fix, and will allow MySql users to make sure the proposed modification doesn't break existing functionality.

Ubercart 8.x-4.x works properly with PostgreSQL.

Both POSGRE and SQLSRV require that all columns in the select list are part of the aggregate.

MYSQL but it's known for it's lack of formal correctness (which means bread for today and hunger for tomorrow). It is basically too loose in many aspects.

This is WHY all columns should be present in the aggregate:

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-co...

Posgre, MSSQL and Oracle are real database engines, and thus they enforce such proper usage patterns.

The Drupal database abstraction layer does not make any assumptions about this requirement. But.... making the the database drivers expand missing columns into the aggregate will be a formal mistake. It's like forcefully introducing a MySQL bug just to make them behave like MySQL.

As for the concerns in #2 of this change breaking something in posgre, note that the error is a SYNTAX error. That clearly points to something wrong with the query builder, or to a NON RELATED issue.

I'm patching with the original proposal.

I resqued another fix from a related issue that is of the same nature: adding a field to an aggregate query and the field not being part of the aggregate.

In #2 the original poster said that adding ->groupBy('a.aid') works to fix the grouping for the PostgreSQL query, but the functionality of the page isn't fixed because there is an additional PostgreSQL problem when you have multiple attributes.

In #9 Island Usurper pointed out the possible reason for the syntax error with multiple attributes, but he never followed up with a patch. Nor did any other PostgreSQL user.

Did you test the multiple attribute use case described in #2? I would like to fix *all* the PostgreSQL problems on the product adjustment page which have been mentioned here.

It doesn't affect how we fix the problem, but my point about the abstraction layer is that it should *never* generate syntactically incorrect SQL, and that the whole purpose of the abstraction layer is to hide the different DB SQL quirks from the API user. So DBTNG should handle the GROUP BY differences either by raising an error (instead of generating and sending incorrect SQL to the DB) OR by expanding the GROUP BY clause so as to effect the same results regardless of DB (I suppose this means expanding so that the query emulates MySQL when not all columns are specified in the GROUP BY). If DBTNG code works properly with one DB but not with another, that means DBTNG is broken, as that is its raison d'etre.