I have a question about support_charting. I installed the module chart API but I apparently SQL error type :

* user warning: query: SELECT COUNT(t.nid) AS count, s.state as label, s.isclosed FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 GROUP BY s.isclosed ORDER BY s.isclosed in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 130.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: l'opérateur n'existe pas : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE G... ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, s.state as label FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY s.state ORDER BY s.state in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 149.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: l'opérateur n'existe pas : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE G... ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, p.priority as label FROM support_ticket t LEFT JOIN support_priority p ON t.priority = p.pid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY t.priority ORDER BY t.priority in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 168.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: l'opérateur n'existe pas : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE G... ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, c.name as label FROM support_ticket t LEFT JOIN support_client c ON t.client = c.clid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY t.client ORDER BY t.client in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 187.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: l'opérateur n'existe pas : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GR... ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, u.name as label FROM support_ticket t LEFT JOIN users u ON t.assigned = u.uid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY t.assigned ORDER BY t.assigned in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 199.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: l'opérateur n'existe pas : smallint_unsigned = boolean LINE 1: ...port_states s ON t.state = s.sid WHERE s.isclosed = TRUE AND... ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT GREATEST(n.changed, l.last_comment_timestamp) AS timestamp FROM node n LEFT JOIN node_comment_statistics l ON n.nid = l.nid LEFT JOIN support_ticket t ON n.nid = t.nid LEFT JOIN support_states s ON t.state = s.sid WHERE s.isclosed = TRUE AND n.type = 'support_ticket' AND (n.changed < 1253534745 OR l.last_comment_timestamp < 1253534745) AND (n.changed > 1251756000 OR l.last_comment_timestamp > 1251756000) ORDER BY timestamp in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 293.

An idea?

Thank you

Comments

jeremy’s picture

jeremy commented
Status: Active » Postponed (maintainer needs more info)

The issue appears to be some queries that are incompatible with PostgreSQL. Unfortunately your errors are in a language that I am unable to read, so I'm not sure what the exact problem is. I'll need errors in English before I can debug this...

pcsystemd’s picture

pcsystemd commented

Thank you. Indeed it looks like problems with Postgresql functions.These are the errors in English in the hope that it may help you.

* user warning: query: SELECT COUNT(t.nid) AS count, s.state as label, s.isclosed FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 GROUP BY s.isclosed ORDER BY s.isclosed in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 130.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: the operator does not exist : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE G... ^ HINT: No operator matches the given name and argument types. You need to add explicit type conversions. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, s.state as label FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY s.state ORDER BY s.state in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 149.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: the operator does not exist : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE G... ^ HINT: No operator matches the given name and argument types. You need to add explicit type conversions. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, p.priority as label FROM support_ticket t LEFT JOIN support_priority p ON t.priority = p.pid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY t.priority ORDER BY t.priority in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 168.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: the operator does not exist : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE G... ^ HINT: No operator matches the given name and argument types. You need to add explicit type conversions. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, c.name as label FROM support_ticket t LEFT JOIN support_client c ON t.client = c.clid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY t.client ORDER BY t.client in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 187.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: the operator does not exist : smallint_unsigned = boolean LINE 1: ...N t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GR... ^ HINT: No operator matches the given name and argument types. You need to add explicit type conversions. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, u.name as label FROM support_ticket t LEFT JOIN users u ON t.assigned = u.uid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = FALSE GROUP BY t.assigned ORDER BY t.assigned in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 199.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: the operator does not exist : smallint_unsigned = boolean LINE 1: ...port_states s ON t.state = s.sid WHERE s.isclosed = TRUE AND... ^ HINT: No operator matches the given name and argument types. You need to add explicit type conversions. in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT GREATEST(n.changed, l.last_comment_timestamp) AS timestamp FROM node n LEFT JOIN node_comment_statistics l ON n.nid = l.nid LEFT JOIN support_ticket t ON n.nid = t.nid LEFT JOIN support_states s ON t.state = s.sid WHERE s.isclosed = TRUE AND n.type = 'support_ticket' AND (n.changed < 1253534745 OR l.last_comment_timestamp < 1253534745) AND (n.changed > 1251756000 OR l.last_comment_timestamp > 1251756000) ORDER BY timestamp in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 293.

Thank's

jeremy’s picture

jeremy commented
Status: Postponed (maintainer needs more info) » Fixed

Fix committed, using declared field type in query. Please re-open this issue if the errors don't go away even after this change.

jeremy’s picture

jeremy commented
Title: Error SQL whith chart API and support_charting » PostgreSQL error with chart API and support_charting

Updating title for posterity.

pcsystemd’s picture

pcsystemd commented

Hello and thank you. I replaced the file by support_charts.module one you Comitted with your changes but I still have some SQL errors.

* warning: pg_query() [function.pg-query]: Query failed: ERROR: column "s.state" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, s.state as label, s.isclosed FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 GROUP BY s.isclosed ORDER BY s.isclosed in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 130.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "p.priority" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, p.priority as label FROM support_ticket t LEFT JOIN support_priority p ON t.priority = p.pid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 GROUP BY t.priority ORDER BY t.priority in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 168.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "c.name" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, c.name as label FROM support_ticket t LEFT JOIN support_client c ON t.client = c.clid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 GROUP BY t.client ORDER BY t.client in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 187.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "u.name" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/support/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(t.nid) AS count, u.name as label FROM support_ticket t LEFT JOIN users u ON t.assigned = u.uid LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND s.isclosed = 0 GROUP BY t.assigned ORDER BY t.assigned in /var/www/support/sites/all/modules/support/support_charts/support_charts.module on line 199.
jeremy’s picture

jeremy commented
Category: support » bug
Status: Fixed » Active

Re-opening bug report.

pcsystemd’s picture

pcsystemd commented

Ok thank's.

jeremy’s picture

jeremy commented

I'm not actually sure how to rewrite these queries that will work with PostgreSQL. I could replace the single query with multiple queries perhaps, but that changes the overall logic a fair bit. If someone wants to contribute a patch to make these queries work with PostgreSQL, that would be greatly appreciated.

(Simply adding the c.state field to the GROUP BY, in the first error for example, completely changes the resulting data.)

pcsystemd’s picture

pcsystemd commented

Well if I migrate my database is in Postgresql to Mysql. It would be desirable for future users of your module support_charts report that does not support Postgresql yet.

Otherwise your module is wonderful.

Thank you

augneL Developer’s picture

augneL Developer commented

Hello,

yesterday i was working in this issue. I downloaded the cvs version and modify the sql sentence.

I have modify the chart Api module, because it have a problem to.

Now this two module work well for postgresql. I don't test for mysql but i think work well. This two modules now are working in our company.

Do you want this change?

jeremy’s picture

jeremy commented

The best thing to do would be to post a patch for the support_charting module here, and another patch for the chart api module in the chart api issue queue.

pcsystemd’s picture

pcsystemd commented

Thank's for the patch.

jeremy’s picture

jeremy commented
Status: Active » Fixed

If this is still an issue, please re-open with a patch.

Status: Fixed » Closed (fixed)

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