Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 116489
Status: open
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: akzhan.abdulin [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: count on 'having' queries simply fail
Date: Tue, 26 Jul 2016 16:33:57 +0300
To: bug-DBIx-Class [...] rt.cpan.org
From: Akzhan Abdulin <akzhan.abdulin [...] gmail.com>
Download (untitled) / with headers
text/plain 4.7k
$VAR1 = { 'collapse' => 1, 'order_by' => 'id ', 'columns' => [ 'me.id' ], 'group_by' => [ 'me.id' ], 'having' => \[ 'me.price*3600/sum( actions.lifetime ) < 80000' ], 'join' => [ 'group_setting', 'group_geo', 'actions', 'group_favorites' ] }; my $count = BUX::Entity::Group->count( $cond, $attr ); DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st execute failed: ERROR: column me.lifetime does not exist LINE 1: SELECT COUNT( * ) FROM (SELECT me.id, me.lifetime FROM group... ^ [for Statement "SELECT COUNT( * ) FROM (SELECT me.id, me.lifetime FROM groups me LEFT JOIN group_settings group_setting ON group_setting.id_groups = me.id LEFT JOIN group_geo group_geo ON group_geo.group_id = me.id LEFT JOIN actions actions ON actions.id_groups = me.id LEFT JOIN group_favorite group_favorites ON group_favorites.group_id = me.id WHERE ( ( ( ( group_setting.day_limit IS NULL OR group_setting.day_limit = ? OR group_setting.day_limit > ( SELECT count(*) FROM group_run r WHERE r.group_id = group_setting.id_groups AND to_timestamp(end_time)::date = current_date ) ) AND ( group_geo.country_id IS NULL OR ( ( group_geo.city_id IS NULL OR group_geo.city_id = ? ) AND group_geo.country_id = ? ) ) AND ( me.id NOT IN ( SELECT group_run.group_id FROM group_run WHERE executor_id = ? ) OR ( group_setting.repeatedly = ? AND me.id NOT IN ( SELECT group_run.group_id FROM group_run WHERE executor_id = ? AND group_run.end_time > date_part('epoch',now()) - group_setting.repeat_interval ) ) ) AND ( group_favorites.user_id != ? OR group_favorites.user_id IS NULL ) ) AND group_setting.end_date > date_part('epoch', now()) AND group_setting.min_rate < ? AND group_setting.start_date < date_part('epoch', now()) AND ( me.id NOT IN ( SELECT actions.id_groups FROM actions WHERE cat_id NOT IN ( SELECT c.id FROM social_networks_data d JOIN categories c ON c.social=d.id_social_networks WHERE id_client_users = ? AND status = ? ) AND actions.id_groups IS NOT NULL ) AND me.id NOT IN ( SELECT id_groups FROM actions ac JOIN categories ct ON ac.cat_id = ct.id JOIN social_networks_data d ON ct.social = id_social_networks WHERE id_client_users = ? AND (1=0 OR ( ct.social = 4 AND (1=0 OR COALESCE((d.profile::json->>'statuses_count')::int,0) NOT BETWEEN (settings::json#>>'{requirements,twits,0}')::int AND (settings::json#>>'{requirements,twits,1}')::int OR COALESCE((d.profile::json->>'followers_count')::int,0) NOT BETWEEN (settings::json#>>'{requirements,subscribers,0}')::int AND (settings::json#>>'{requirements,subscribers,1}')::int ) ) OR ( ct.social in (1, 2, 5) AND (1=0 OR COALESCE((current_date - (profile::json->>'birthday')::date)/365,0) NOT BETWEEN (settings::json#>>'{requirements,age,0}')::int AND (settings::json#>>'{requirements,age,1}')::int OR COALESCE((d.profile::json->>'friends_count')::int,0) NOT BETWEEN (settings::json#>>'{requirements,friends,0}')::int AND (settings::json#>>'{requirements,friends,1}')::int OR settings::json#>>'{requirements,sex}' = '0' AND d.profile::json->>'gender' != 'm' OR settings::json#>>'{requirements,sex}' = '1' AND d.profile::json->>'gender' != 'f' ) ) ) AND id_groups is not null ) ) AND moderate IS NULL AND status = ? ) ) GROUP BY me.id HAVING me.price*3600/sum( actions.lifetime ) < 80000) me" with ParamValues: 1='0', 2='4125', 3='20', 4='77813', 5='1', 6='77813', 7='77813', 8='50', 9='77813', 10='approved', 11='77813', 12='active'] at ../backend/lib//BUX/DataModel/Repository.pm line 148
Subject: Re: count on 'having' queries simply fail
Date: Tue, 26 Jul 2016 16:35:53 +0300
To: bug-DBIx-Class [...] rt.cpan.org
From: Akzhan Abdulin <akzhan.abdulin [...] gmail.com>
Download (untitled) / with headers
text/plain 5.1k
Take a note that me.lifetime is absolutely absent in SQL text, it was added by count. 2016-07-26 16:33 GMT+03:00 Akzhan Abdulin <akzhan.abdulin@gmail.com>: Show quoted text
> $VAR1 = { > 'collapse' => 1, > 'order_by' => 'id ', > 'columns' => [ > 'me.id' > ], > 'group_by' => [ > 'me.id' > ], > 'having' => \[ > 'me.price*3600/sum( actions.lifetime ) < 80000' > ], > 'join' => [ > 'group_setting', > 'group_geo', > 'actions', > 'group_favorites' > ] > }; > > my $count = BUX::Entity::Group->count( $cond, $attr ); > > > DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st > execute failed: ERROR: column me.lifetime does not exist > LINE 1: SELECT COUNT( * ) FROM (SELECT me.id, me.lifetime FROM group... > ^ [for Statement "SELECT > COUNT( * ) FROM (SELECT me.id, me.lifetime FROM groups me LEFT JOIN > group_settings group_setting ON group_setting.id_groups = me.id LEFT JOIN > group_geo group_geo ON group_geo.group_id = me.id LEFT JOIN actions > actions ON actions.id_groups = me.id LEFT JOIN group_favorite > group_favorites ON group_favorites.group_id = me.id WHERE ( ( ( ( > group_setting.day_limit IS NULL OR group_setting.day_limit = ? OR > group_setting.day_limit > ( > SELECT count(*) > FROM group_run r > WHERE r.group_id = group_setting.id_groups > AND to_timestamp(end_time)::date = current_date > ) ) AND ( group_geo.country_id IS NULL OR ( ( > group_geo.city_id IS NULL OR group_geo.city_id = ? ) AND > group_geo.country_id = ? ) ) AND ( me.id NOT IN ( SELECT > group_run.group_id FROM group_run WHERE executor_id = ? ) OR ( > group_setting.repeatedly = ? AND me.id NOT IN ( SELECT group_run.group_id > FROM group_run > WHERE executor_id = ? > AND group_run.end_time > > date_part('epoch',now()) - group_setting.repeat_interval ) ) ) AND ( > group_favorites.user_id != ? OR group_favorites.user_id IS NULL ) ) AND > group_setting.end_date > date_part('epoch', now()) AND > group_setting.min_rate < ? AND group_setting.start_date < > date_part('epoch', now()) AND ( me.id NOT IN ( SELECT actions.id_groups > FROM actions > WHERE cat_id NOT IN ( > SELECT c.id > FROM social_networks_data d > JOIN categories c > ON c.social=d.id_social_networks > WHERE id_client_users = ? AND status = ? > ) AND actions.id_groups IS NOT NULL ) AND me.id NOT > IN ( > SELECT id_groups > FROM actions ac > JOIN categories ct ON ac.cat_id = ct.id > JOIN social_networks_data d ON ct.social = > id_social_networks > WHERE id_client_users = ? > AND (1=0 > OR ( > ct.social = 4 > AND (1=0 > OR > COALESCE((d.profile::json->>'statuses_count')::int,0) > NOT BETWEEN > (settings::json#>>'{requirements,twits,0}')::int > AND > (settings::json#>>'{requirements,twits,1}')::int > OR > COALESCE((d.profile::json->>'followers_count')::int,0) > NOT BETWEEN > (settings::json#>>'{requirements,subscribers,0}')::int > AND > (settings::json#>>'{requirements,subscribers,1}')::int > ) > ) > OR ( > ct.social in (1, 2, 5) > AND (1=0 > OR COALESCE((current_date - > (profile::json->>'birthday')::date)/365,0) > NOT BETWEEN > (settings::json#>>'{requirements,age,0}')::int > AND > (settings::json#>>'{requirements,age,1}')::int > OR > COALESCE((d.profile::json->>'friends_count')::int,0) > NOT BETWEEN > (settings::json#>>'{requirements,friends,0}')::int > AND > (settings::json#>>'{requirements,friends,1}')::int > OR settings::json#>>'{requirements,sex}' = > '0' AND d.profile::json->>'gender' != 'm' > OR settings::json#>>'{requirements,sex}' = > '1' AND d.profile::json->>'gender' != 'f' > ) > ) > ) > AND id_groups is not null > ) ) AND moderate IS NULL AND status = ? ) ) GROUP BY > me.id HAVING me.price*3600/sum( actions.lifetime ) < 80000) me" with > ParamValues: 1='0', 2='4125', 3='20', 4='77813', 5='1', 6='77813', > 7='77813', 8='50', 9='77813', 10='approved', 11='77813', 12='active'] at > ../backend/lib//BUX/DataModel/Repository.pm line 148 >
Subject: Re: [rt.cpan.org #116489] count on 'having' queries simply fail
Date: Tue, 26 Jul 2016 15:45:19 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <rabbit [...] rabbit.us>
Download (untitled) / with headers
text/plain 280b
On 07/26/2016 03:34 PM, Akzhan Abdulin via RT wrote: Show quoted text
> Subject: count on 'having' queries simply fail
This is a known issue (part of a larger set of the literal-scanner), a preliminary fix is near-ready but needs another pass before I am comfortable with it. More soon.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.