postgres take quiz gives DB error, easily fixed [#1691072]

using posgres, I installed this module with no issues.
creating a quiz works fine as well.

taking the quiz gives me the following error:

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "" LINE 2: WHERE result_id IN('') ^: SELECT result_id, question_nid, question_vid FROM {quiz_node_results_answers} WHERE result_id IN(:result_id_0); Array ( [:result_id_0] => ) in quiz_delete_results() (line 4054 of /var/www/html/sites/all/modules/quiz/quiz.module).

A different error is encountered on trying to view the quiz results:

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "GROUP" LINE 13: ...s.nid = '69' AND time_end <> 0 GROUP BY qnrs.uid GROUP BY q... ^: SELECT u.name, qnrs.uid, qnrs.result_id, qnrs.score, qnrs.is_evaluated AS evaluated, qnrs.time_start as started, qnrs.time_end as finished, qnp.pass_rate, qnrs.time_end - qnrs.time_start as duration FROM {quiz_node_results} qnrs LEFT JOIN {users} u ON u.uid = qnrs.uid LEFT JOIN {quiz_node_properties} qnp ON qnrs.vid = qnp.vid INNER JOIN ( SELECT qnrs.uid, MAX(qnrs.score) AS top_score FROM {quiz_node_results} qnrs LEFT JOIN {users} u ON u.uid = qnrs.uid LEFT JOIN {quiz_node_properties} qnp ON qnrs.vid = qnp.vid WHERE qnrs.nid = :quiz_nid AND time_end <> 0 GROUP BY qnrs.uid ) AS qnrc ON qnrs.uid = qnrc.uid AND qnrs.score = qnrc.top_score WHERE qnrs.nid = :quiz_nid AND time_end <> 0 GROUP BY qnrs.uid GROUP BY qnrs.uid, u.uid, u.name, qnrs.result_id, qnrs.score, qnrs.is_evaluated, qnrs.time_start, qnrs.time_end, qnp.pass_rate; Array ( [:quiz_nid] => 69 ) in _quiz_results_mr_data_provider() (line 2055 of /var/www/html/sites/all/modules/quiz/quiz.admin.inc).

Comment	File	Size	Author
#10	quiz_duplicate_group_by_patch.txt	665 bytes	Andy Dorman
#8	patch.txt	951 bytes	Andy Dorman

Comments

Comment #1

nevosa commented 18 July 2012 at 14:48

update,
added this postgres compatible code after line 4054 of sites/all/modules/quiz/quiz.module,
and it seems to resolve the issue:

$rids = array_map(
create_function('$value', 'return (int)$value;'),
$rids
);

Aurora Webworks

Comment #2

nevosa commented 23 July 2012 at 08:06

Issue tags:

-posgresql

+posgresql pssql

regarding the second error, there seems to be some a duplicate GROUP BY for pssql driver:
on line 2137 of quiz.admin.inc
instead, I replaced the code with:
// $filter['group'] .= " GROUP BY qnrs.uid";
if (db_driver() == 'pgsql') {
$filter['group'] .= " GROUP BY qnrs.uid, u.uid, u.name, qnrs.result_id, qnrs.score, qnrs.is_evaluated, qnrs.time_start, qnrs.time_end, qnp.pass_rate";
}
else {
$filter['group'] .= " GROUP BY qnrs.uid";
}

please test these changes for compatibility,
Regards,

Comment #3

cfgregory commented 24 August 2012 at 21:30

I got the same error when trying to take a quiz I just created. I too have postgresql.

I try the fixes suggested but they did not fix the issue for me.

Here is my error:

Comment #4

cfgregory commented 24 August 2012 at 21:29

Priority:

Normal

» Major

Slight correction to comment above.

When I added the code from comment # 1 before:
$result = db_query($sql, array(':result_id' => $rids));

I can take the quiz. However, I get the following errors:

Warning: Invalid argument supplied for foreach() in element_children() (line 6300 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5767 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5777 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5815 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#printed' in drupal_render() (line 5822 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Invalid argument supplied for foreach() in element_children() (line 6300 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5767 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5777 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5815 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#printed' in drupal_render() (line 5822 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Invalid argument supplied for foreach() in element_children() (line 6300 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5767 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5777 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#children' in drupal_render() (line 5815 of /home/user/Public/www/drupalDev/includes/common.inc).
Warning: Illegal string offset '#printed' in drupal_render() (line 5822 of /home/user/Public/www/drupalDev/includes/common.inc).

Comment #5

cfgregory commented 24 August 2012 at 22:39

One more issue.

When I click on the results tab, I get the following error:

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "GROUP" LINE 13: ...ed, qnrs.time_start, qnrs.time_end, qnp.pass_rate GROUP BY q... ^: SELECT u.name, qnrs.uid, qnrs.result_id, qnrs.score, qnrs.is_evaluated AS evaluated, qnrs.time_start as started, qnrs.time_end as finished, qnp.pass_rate, qnrs.time_end - qnrs.time_start as duration FROM {quiz_node_results} qnrs LEFT JOIN {users} u ON u.uid = qnrs.uid LEFT JOIN {quiz_node_properties} qnp ON qnrs.vid = qnp.vid INNER JOIN ( SELECT qnrs.uid, MAX(qnrs.score) AS top_score FROM {quiz_node_results} qnrs LEFT JOIN {users} u ON u.uid = qnrs.uid LEFT JOIN {quiz_node_properties} qnp ON qnrs.vid = qnp.vid WHERE qnrs.nid = :quiz_nid AND time_end <> 0 GROUP BY qnrs.uid ) AS qnrc ON qnrs.uid = qnrc.uid AND qnrs.score = qnrc.top_score WHERE qnrs.nid = :quiz_nid AND time_end <> 0 GROUP BY qnrs.uid, u.uid, u.name, qnrs.result_id, qnrs.score, qnrs.is_evaluated, qnrs.time_start, qnrs.time_end, qnp.pass_rate; Array ( [:quiz_nid] => 25 ) in _quiz_results_mr_data_provider() (line 2055 of /home/user/Public/www/drupalDev/sites/all/modules/quiz/quiz.admin.inc).

Comment #6

falcon commented 17 October 2012 at 22:52

Priority:

Major

» Minor

Comment #7

cfgregory commented 25 October 2012 at 15:27

Just a quick note. With these bugs, the quiz module is unusable for anyone with postgresql. A user can NOT take quizzies.

Comment #8

Andy Dorman commented 19 February 2013 at 17:37

Status	File	Size
new	patch.txt	951 bytes

We love quiz and have used it on several D6 sites with PostgreSQL. We just installed 7.x-4.0-alpha12 and are working on patches.

Re the duplicate GROUP BY in #1, #2 and #5, looks like almost, but not all the fix from #2 has been applied. I have included a small patch to deal with that one.

Hope this helps.

Comment #9

Andy Dorman commented 23 February 2013 at 14:39

Title:	postgres take quiz gives DB error	» postgres take quiz gives DB error, easily fixed
Version:	7.x-4.0-alpha9	» 7.x-4.0-alpha12

Just a follow up note. We use PostgreSQL 9.1.x & PHP 5.4 and are currently using quiz 7.x-4.0-alpha12 with the above patch with no serious problems. So far we have two minor lingering issues that are not related to this ticket and may not even be related to the quiz module.

So with this patch and the patch from http://drupal.org/node/1827880, we consider quiz to be usable with PostgreSQL.

Thanks again for excellent work developing the quiz module.

Comment #10

Andy Dorman commented 18 June 2013 at 14:23

Version:

7.x-4.0-alpha12

» 7.x-4.0-beta1

Status	File	Size
new	quiz_duplicate_group_by_patch.txt	665 bytes

As noted before, we use PostgreSQL 9.1.x & PHP 5.4 and I just loaded Quiz 7.x-4.0-beta1 (Mar 04).

I see that the patch for http://drupal.org/node/1827880 was added to git AFTER the beta was released...so it will be in the next release. Great!

I also noticed that the patch for this issue, to fix the duplicate GROUP BY clause for PostgreSQL, has not yet been added to git. I had to manually add it to my install so we could upgrade to Beta1.

I just wanted to pass along a simplified version of this patch that makes Quiz completely usable with postgreSQL (when combined with the patch for http://drupal.org/node/1827880.)

Thanks again for Quiz. We have several thousand users using it every day on a couple of web sites.

Comment #11

Andy Dorman commented 18 June 2013 at 14:23

Comment #12

sivaji_ganesh_jojodae commented 20 June 2013 at 08:18

Status:

Active

» Fixed

Andy Dorman,

Thanks. Your patch has been committed (didn't test it though as I don't have postgresql here).

If someone need early access to code in downloadable format, it is availalbe from http://hub.knackforge.com/projects/quiz-support/files

Comment #13

4 July 2013 at 08:20

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

Comment #13.0

(not verified) commented 4 July 2013 at 08:20

Issue summary:

View changes

wording

Comment #14

8 April 2014 at 20:46

Commit c899908 on 7.x-4.x, 7.x-5.x authored by Andy Dorman, committed by sivaji:
```
Issue #1691072 by Andy Dorman | nevos: Fixed postgres take quiz gives DB...
```

Comment #15

23 April 2014 at 05:13

Commit c899908 on 7.x-4.x, 7.x-5.x, quiz-pages authored by Andy Dorman, committed by sivaji:
```
Issue #1691072 by Andy Dorman | nevos: Fixed postgres take quiz gives DB...
```

Comment #16

10 June 2014 at 20:06

Commit c899908 on 7.x-4.x, 7.x-5.x, quiz-pages, 2269219 authored by Andy Dorman, committed by sivaji:
```
Issue #1691072 by Andy Dorman | nevos: Fixed postgres take quiz gives DB...
```

Comment #17

22 June 2014 at 16:17

Commit c899908 on 7.x-4.x, 7.x-5.x, 2269219 authored by Andy Dorman, committed by sivaji:
```
Issue #1691072 by Andy Dorman | nevos: Fixed postgres take quiz gives DB...
```

postgres take quiz gives DB error, easily fixed