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

Report information
The Basics
Id:
116489
Status:
open
Priority:
Low/Low
Queue:

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

BugTracker
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>
$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>
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>
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 runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

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