PostgreSQL 9 issues with Product report (uc_reports.admin.inc) [#1138358]

Having issues with the product report crashing and burning when running against a PostgreSQL 9.0.4 database, as follows:

Trying to run Store | Reports | Product reports fails to produce a report and gives this error in the Recent log messages:

PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR: column "revenue" does not exist LINE 1: SELECT revenue AS revenue, n.nid AS nid, n.title AS title, n... ^: SELECT revenue AS revenue, n.nid AS nid, n.title AS title, nc.totalcount AS totalcount, (SELECT SUM(uop.qty) FROM {uc_order_products} AS uop LEFT JOIN {uc_orders} AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN (:statuses_0) AND uop.nid = n.nid) AS sold, (SELECT (SUM(uop.price * uop.qty) - SUM(uop.cost * uop.qty)) FROM {uc_order_products} AS uop LEFT JOIN {uc_orders} AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN (:statuses_0) AND uop.nid = n.nid) AS gross, (SELECT (SUM(uop.price * uop.qty)) FROM {uc_order_products} AS uop LEFT JOIN {uc_orders} AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN (:statuses_0) AND uop.nid = n.nid) AS revenue FROM {node} n LEFT OUTER JOIN {node_counter} nc ON n.nid = nc.nid WHERE (n.type IN (:db_condition_placeholder_0, :db_condition_placeholder_1)) GROUP BY n.nid, n.title ORDER BY revenue DESC LIMIT 30 OFFSET 0; Array ( [:db_condition_placeholder_0] => product [:db_condition_placeholder_1] => taxexempt [:statuses_0] => completed ) in PagerDefault->execute() (line 80 of /var/www/drupal-7.0/includes/pager.inc).

This appears to be related to the fact that some of the select fields (i.e. totalcount) don't appear in the group by clause which is illegal SQL syntax (at least for PostgreSQL) and also the revenue column is in the select list twice: one time as a simple "select revenue as revenue" -- how does that work? and a second time as a large computed field which seems to makes sense. Clearly the syntax of this report is capitalizing on some MySQL'ism that can select the large computed column named "revenue" as another simple field with the same name.

So I made a bit of a hacky attempt to correct the Product report (uc_reports_products function) so at least it works with PostgreSQL 9 -- see the uc_reports_products_pgsql.patch attached to this bug. The revenue column is not clickable (i.e. sortable) with this hacked attempt but the error at least goes away.

Comment	File	Size	Author
#4	uc_reports.admin_.inc_.patch	2.44 KB	thatoneguy
#4	uc_reports.admin_.inc_.patch	2.44 KB	thatoneguy
#3	uc_reports.admin_.inc_.patch	2.46 KB	thatoneguy
	uc_reports_products_pgsql.patch	1.74 KB	alphasupremicus

Comments

Comment #1

tr commented 29 April 2011 at 03:01

Status:

Active

» Needs work

The issue with totalcount is the same issue that PostgreSQL has with Ubercart 6.x-2.x, see #976804: Sales report PostgreSQL queries for the details of that issue and for my feedback on the proposed fix. Please help out with that issue, as the fix for that will be the same as the fix needed here.

Your fix above for revenue is not correct, because all you've done is to remove some functionality by removing the ability to sort by the revenue column in the resulting HTML table. A proper fix would be to figure out how to re-organize the query so it works for PGSQL. revenue is a named expression, which you can see above: (SELECT (SUM(uop.price * uop.qty)) FROM {uc_order_products} AS uop LEFT JOIN {uc_orders} AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN (:statuses_0) AND uop.nid = n.nid) AS revenue. The question is why the PGSQL driver is inserting the "SELECT revenue AS revenue" line, since that is not something that Ubercart puts into the query.

Comment #2

alphasupremicus commented 29 April 2011 at 12:49

Well, seeing I'm a newb when it comes to messing with Drupal modules, I'm not sure I will be much immediate help, but it appears the orderByHeader() function call where this line is added to the header array:

array('data' => t('Revenue'), 'field' => 'revenue', 'sort' => 'desc'),

then that entry above seems to be creating the duplicate "SELECT revenue AS revenue" part of the SQL, because if I substitute that part of the code with my hacky attempt of:

array('data' => t('Revenue'))

instead -- then the malformed "SELECT revenue AS revenue" disappears from the SQL. So it's gonna be a little beyond me as to where to look next as I'm quite unfamiliar with the Drupal 7 data access libraries and how they might work or not work with PostgreSQL. Can anyone with MySQL tell me what SQL is generated so we can compare and see if it's a SQL-generator issue?

Anyways, you are right -- my patch is not a real fix as I've purposefully damaged the code in some fairly benign way in order for it to mostly work. I get that -- but I guess I'm a bit out of my league right now as to what else I can do.

I'll poke around as time permits. Thx for other link.