Quantcast

Postgresql problem with GROUP BY clauses

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Postgresql problem with GROUP BY clauses

mschumann
Hi,

I have a MojoMojo-1.02 installation with a Postgresql 8.4 backend.
When clicking on any "tags" link, there is an error page.

I checked the apache log and found

[error] DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed: column "content.created" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...epth, me.lft, me.rgt, me.content_version ORDER BY content.cr...
                                                             ^ [for Statement "SELECT me.id, me.version, me.parent, me.name, me.name_orig, me.depth, me.lft, me.rgt, me.content_version FROM page as me, page as ancestor, tag, content WHERE ( ( ( me.id =ancestor.id OR ( me.lft > ancestor.lft AND me.rgt < ancestor.rgt ) ) AND ancestor.id = ? AND content.page =me.id AND content.version =me.content_version AND me.id =tag.page AND tag = ? ) ) GROUP BY me.id, me.version, me.parent, me.name, me.name_orig, me.depth, me.lft, me.rgt, me.content_version ORDER BY content.created DESC" with ParamValues: 1='1', 2='G<E4>stehaus Behnke'] at /usr/share/perl5/MojoMojo/Schema/Result/Page.pm line 219

This is caused by the GROUP BY clause with its column missing in the SELECT clause, which is not supported by Postgresql:

"When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column."

Thus there are two possibilities:

* add the missing column to the SELECT clause
* drop the GROUP BY clause

Appearantly dropping the GROUP BY clause is not an option, so I am currently searching for a patch to add another column. Other methods need to be checked, too. Maybe someone has a fix already?

Kind regards
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Postgresql problem with GROUP BY clauses

mschumann
Hi,

when the test script t/schema_DBIC_Page.t is tried, it throws the exception mentioned in my first post. Unfortunately is has to be patched to not destroy the database:

--- t/schema_DBIC_Page.t.orig	2011-01-02 07:53:55.529421000 +0100
+++ t/schema_DBIC_Page.t	2011-01-11 21:15:08.166922962 +0100
@@ -16,7 +16,8 @@
 use lib 't/lib';
 use MojoMojoTestSchema;
 
-my $schema = MojoMojoTestSchema->init_schema(populate => 1);
+#my $schema = MojoMojoTestSchema->init_schema(populate => 1);
+my $schema = MojoMojoTestSchema->get_schema;
 
 my ($root_path_pages, $root_proto_pages) = $schema->resultset('Page')->path_pages('/');
 my $root_path_pages_count = @$root_path_pages;

Then I get the result:

t/schema_DBIC_Page.t .. 1/11 
...
#   Failed test 'Got 1 tagged descendant.'
#   at t/schema_DBIC_Page.t line 62.
#          got: '2'
#     expected: '1'
DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed: column "content.created" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...epth, me.lft, me.rgt, me.content_version ORDER BY content.cr...
                                                             ^ [for Statement "SELECT me.id, me.version, me.parent, me.name, me.name_orig, me.depth, me.lft, me.rgt, me.content_version FROM page as me, page as ancestor, tag, content WHERE ( ( ( me.id =ancestor.id OR ( me.lft > ancestor.lft AND me.rgt < ancestor.rgt ) ) AND ancestor.id = ? AND content.page =me.id AND content.version =me.content_version AND me.id =tag.page AND tag = ? ) ) GROUP BY me.id, me.version, me.parent, me.name, me.name_orig, me.depth, me.lft, me.rgt, me.content_version ORDER BY content.created DESC" with ParamValues: 1='1', 2='test'] at .../libmojomojo-perl-devel/lib/MojoMojo/Schema/Result/Page.pm line 219
# Looks like you planned 11 tests but ran 10.
# Looks like you failed 3 tests of 10 run.
# Looks like your test exited with 255 just after 10.
t/schema_DBIC_Page.t .. Dubious, test returned 255 (wstat 65280, 0xff00)
Failed 4/11 subtests 

Test Summary Report
-------------------
t/schema_DBIC_Page.t (Wstat: 65280 Tests: 10 Failed: 3)
  Failed tests:  4-5, 9
  Non-zero exit status: 255
  Parse errors: Bad plan.  You planned 11 tests but ran 10.
Files=1, Tests=10,  0 wallclock secs ( 0.02 usr  0.00 sys +  0.54 cusr  0.03 csys =  0.59 CPU)
Result: FAIL

I keep on tracking down this issue...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Postgresql problem with GROUP BY clauses

Mateu X Hunter
Hello, 

I believe I have a fix for the query issue you encountered.  It is part of the commit listed below:


- Mateu



2011/1/11 mschumann <[hidden email]>

Hi,

when the test script t/schema_DBIC_Page.t is tried, it throws the exception
mentioned in my first post. Unfortunately is has to be patched to not
destroy the database:

--- t/schema_DBIC_Page.t.orig   2011-01-02 07:53:55.529421000 +0100
+++ t/schema_DBIC_Page.t        2011-01-11 21:15:08.166922962 +0100
@@ -16,7 +16,8 @@
 use lib 't/lib';
 use MojoMojoTestSchema;

-my $schema = MojoMojoTestSchema->init_schema(populate => 1);
+#my $schema = MojoMojoTestSchema->init_schema(populate => 1);
+my $schema = MojoMojoTestSchema->get_schema;

 my ($root_path_pages, $root_proto_pages) =
$schema->resultset('Page')->path_pages('/');
 my $root_path_pages_count = @$root_path_pages;

Then I get the result:

t/schema_DBIC_Page.t .. 1/11
...
#   Failed test 'Got 1 tagged descendant.'
#   at t/schema_DBIC_Page.t line 62.
#          got: '2'
#     expected: '1'
DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed:
column "content.created" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: ...epth, me.lft, me.rgt, me.content_version ORDER BY content.cr...
                                                            ^ [for
Statement "SELECT me.id, me.version, me.parent, me.name, me.name_orig,
me.depth, me.lft, me.rgt, me.content_version FROM page as me, page as
ancestor, tag, content WHERE ( ( ( me.id =ancestor.id OR ( me.lft >
ancestor.lft AND me.rgt < ancestor.rgt ) ) AND ancestor.id = ? AND
content.page =me.id AND content.version =me.content_version AND me.id
=tag.page AND tag = ? ) ) GROUP BY me.id, me.version, me.parent, me.name,
me.name_orig, me.depth, me.lft, me.rgt, me.content_version ORDER BY
content.created DESC" with ParamValues: 1='1', 2='test'] at
.../libmojomojo-perl-devel/lib/MojoMojo/Schema/Result/Page.pm line 219
# Looks like you planned 11 tests but ran 10.
# Looks like you failed 3 tests of 10 run.
# Looks like your test exited with 255 just after 10.
t/schema_DBIC_Page.t .. Dubious, test returned 255 (wstat 65280, 0xff00)
Failed 4/11 subtests

Test Summary Report
-------------------
t/schema_DBIC_Page.t (Wstat: 65280 Tests: 10 Failed: 3)
 Failed tests:  4-5, 9
 Non-zero exit status: 255
 Parse errors: Bad plan.  You planned 11 tests but ran 10.
Files=1, Tests=10,  0 wallclock secs ( 0.02 usr  0.00 sys +  0.54 cusr  0.03
csys =  0.59 CPU)
Result: FAIL

I keep on tracking down this issue...
--
View this message in context: http://mojomojo.2358427.n2.nabble.com/Postgresql-problem-with-GROUP-BY-clauses-tp5908804p5912401.html
Sent from the mojomojo mailing list archive at Nabble.com.

_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo


_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Postgresql problem with GROUP BY clauses

Marcus Ramberg
1.03 including this fix is out on CPAN now.

Marcus

2011/1/12 Mateu X. Hunter <[hidden email]>
Hello, 

I believe I have a fix for the query issue you encountered.  It is part of the commit listed below:


- Mateu



2011/1/11 mschumann <[hidden email]>


Hi,

when the test script t/schema_DBIC_Page.t is tried, it throws the exception
mentioned in my first post. Unfortunately is has to be patched to not
destroy the database:

--- t/schema_DBIC_Page.t.orig   2011-01-02 07:53:55.529421000 +0100
+++ t/schema_DBIC_Page.t        2011-01-11 21:15:08.166922962 +0100
@@ -16,7 +16,8 @@
 use lib 't/lib';
 use MojoMojoTestSchema;

-my $schema = MojoMojoTestSchema->init_schema(populate => 1);
+#my $schema = MojoMojoTestSchema->init_schema(populate => 1);
+my $schema = MojoMojoTestSchema->get_schema;

 my ($root_path_pages, $root_proto_pages) =
$schema->resultset('Page')->path_pages('/');
 my $root_path_pages_count = @$root_path_pages;

Then I get the result:

t/schema_DBIC_Page.t .. 1/11
...
#   Failed test 'Got 1 tagged descendant.'
#   at t/schema_DBIC_Page.t line 62.
#          got: '2'
#     expected: '1'
DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed:
column "content.created" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: ...epth, me.lft, me.rgt, me.content_version ORDER BY content.cr...
                                                            ^ [for
Statement "SELECT me.id, me.version, me.parent, me.name, me.name_orig,
me.depth, me.lft, me.rgt, me.content_version FROM page as me, page as
ancestor, tag, content WHERE ( ( ( me.id =ancestor.id OR ( me.lft >
ancestor.lft AND me.rgt < ancestor.rgt ) ) AND ancestor.id = ? AND
content.page =me.id AND content.version =me.content_version AND me.id
=tag.page AND tag = ? ) ) GROUP BY me.id, me.version, me.parent, me.name,
me.name_orig, me.depth, me.lft, me.rgt, me.content_version ORDER BY
content.created DESC" with ParamValues: 1='1', 2='test'] at
.../libmojomojo-perl-devel/lib/MojoMojo/Schema/Result/Page.pm line 219
# Looks like you planned 11 tests but ran 10.
# Looks like you failed 3 tests of 10 run.
# Looks like your test exited with 255 just after 10.
t/schema_DBIC_Page.t .. Dubious, test returned 255 (wstat 65280, 0xff00)
Failed 4/11 subtests

Test Summary Report
-------------------
t/schema_DBIC_Page.t (Wstat: 65280 Tests: 10 Failed: 3)
 Failed tests:  4-5, 9
 Non-zero exit status: 255
 Parse errors: Bad plan.  You planned 11 tests but ran 10.
Files=1, Tests=10,  0 wallclock secs ( 0.02 usr  0.00 sys +  0.54 cusr  0.03
csys =  0.59 CPU)
Result: FAIL

I keep on tracking down this issue...
--
View this message in context: http://mojomojo.2358427.n2.nabble.com/Postgresql-problem-with-GROUP-BY-clauses-tp5908804p5912401.html
Sent from the mojomojo mailing list archive at Nabble.com.

_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo


_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo




--
--- 
Marcus Ramberg 
Chief Yak Shaver 
Nordaaker Consulting 
+47-93417508


_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Postgresql problem with GROUP BY clauses

mschumann
In reply to this post by Mateu X Hunter
Hi,

Am 12.01.2011 03:17, schrieb Mateu X. Hunter:
> Hello,
>
> I believe I have a fix for the query issue you encountered.  It is part
> of the commit listed below:
>
> https://github.com/marcusramberg/mojomojo/commit/74d0774e182ecf2518affeb53f3fe3561e53b71
>
> - Mateu

great job, rocks now.

Thank you very much.
--
Cheers, Marco

_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Postgresql problem with GROUP BY clauses

mschumann
In reply to this post by Marcus Ramberg
Hi,

Am 12.01.2011 14:31, schrieb Marcus Ramberg:
> 1.03 including this fix is out on CPAN now.
>
> Marcus

maybe OT, but I haven't found an updated version at CPAN, does it take a
certain amount of time to appear?

Furthermore, I cannot build MojoMojo-1.03 as inc/Module/Install.pm was
deleted but Makefile.PL insists on that file... is that correct?

This is the result of svn_load_dirs:
     Deleted                            Added
   0 inc/Module/AutoInstall.pm_________ script/util/bust_cache.pl
   1 inc/Module/Install/AutoInstall.pm_ root/themes/default/
   2 inc/Module/Install/Base.pm________ t/formatter_idlink.t
   3 inc/Module/Install/Can.pm_________
lib/MojoMojo/Formatter/GoogleCalendar.pm
   4 inc/Module/Install/Catalyst.pm____ lib/MojoMojo/Formatter/IDLink.pm
   5 inc/Module/Install/Fetch.pm_______ lib/MojoMojo/I18N/ko.po
   6 inc/______________________________ MANIFEST.SKIP
   7 inc/Module/Install/Include.pm_____ script/po2json.pl
   8 inc/Module/Install/_______________
   9 inc/Module/Install.pm_____________
  10 inc/Module/Install/Makefile.pm____
  11 MANIFEST__________________________
  12 META.yml__________________________
  13 inc/Module/Install/Metadata.pm____
  14 inc/Module/_______________________
  15 t/var/mojomojo.yml________________
  16 inc/Module/Install/Scripts.pm_____
  17 inc/Module/Install/Win32.pm_______
  18 inc/Module/Install/WriteAll.pm____
--
Cheers, Marco

_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Postgresql problem with GROUP BY clauses

Marcus Ramberg
looks like I managed to botch the upload the first time. This should fix it:

mBook:MojoMojo marcus$ cpan-upload MojoMojo-1.03.tar.gz 
registering upload with PAUSE web server
POSTing upload for MojoMojo-1.03.tar.gz
PAUSE add message sent ok [200]

Anyways, with regards to your inc/ query,  assuming you refer to your checkout, inc/ is not in version control. just delete it to make Makefile.PL regenerate it.

Marcus

On Wed, Jan 12, 2011 at 10:45 PM, <[hidden email]> wrote:
Hi,

Am 12.01.2011 14:31, schrieb Marcus Ramberg:
> 1.03 including this fix is out on CPAN now.
>
> Marcus

maybe OT, but I haven't found an updated version at CPAN, does it take a
certain amount of time to appear?

Furthermore, I cannot build MojoMojo-1.03 as inc/Module/Install.pm was
deleted but Makefile.PL insists on that file... is that correct?

This is the result of svn_load_dirs:
    Deleted                            Added
  0 inc/Module/AutoInstall.pm_________ script/util/bust_cache.pl
  1 inc/Module/Install/AutoInstall.pm_ root/themes/default/
  2 inc/Module/Install/Base.pm________ t/formatter_idlink.t
  3 inc/Module/Install/Can.pm_________
lib/MojoMojo/Formatter/GoogleCalendar.pm
  4 inc/Module/Install/Catalyst.pm____ lib/MojoMojo/Formatter/IDLink.pm
  5 inc/Module/Install/Fetch.pm_______ lib/MojoMojo/I18N/ko.po
  6 inc/______________________________ MANIFEST.SKIP
  7 inc/Module/Install/Include.pm_____ script/po2json.pl
  8 inc/Module/Install/_______________
  9 inc/Module/Install.pm_____________
 10 inc/Module/Install/Makefile.pm____
 11 MANIFEST__________________________
 12 META.yml__________________________
 13 inc/Module/Install/Metadata.pm____
 14 inc/Module/_______________________
 15 t/var/mojomojo.yml________________
 16 inc/Module/Install/Scripts.pm_____
 17 inc/Module/Install/Win32.pm_______
 18 inc/Module/Install/WriteAll.pm____
--
Cheers, Marco

_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo



--
--- 
Marcus Ramberg 
Chief Yak Shaver 
Nordaaker Consulting 
+47-93417508


_______________________________________________
Mojomojo mailing list
[hidden email]
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/mojomojo
Loading...