Re: [sqlalchemy] strange behavior in count with custom mapper option

Wed, 15 Jul 2015 13:06:47 -0700

the interesting is, if i get the count() produced sql and put it into psql ... 

   SELECT count(*) AS count_1
   FROM system_unit, (
   SELECT system_unit.fk_updated_by AS system_unit_fk_updated_by,
   system_unit.fk_created_by AS system_unit_fk_created_by,
   system_unit.dt_created_on AS system_unit_dt_created_on,
   system_unit.dt_updated_on AS system_unit_updated_on,
   system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
   machine_unit.pk_fk_system_unit_id AS
   machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
   system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
   system_unit_fk_organization_id, system_unit.u_system_unit_name AS
   system_unit_u_system_unit_name, system_unit.in_ipv4 AS
   system_unit_in_ipv4, system_unit.p_system_unit_type AS
   system_unit_system_unit_type, system_unit.i_version AS
   system_unit_i_version, machine_unit.e_machine_type AS
   machine_unit_e_machine_type, machine_unit.e_printer_type AS
   machine_unit_e_printer_type
   FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id
   = machine_unit.pk_fk_system_unit_id
   WHERE machine_unit.pk_fk_system_unit_id = 24769797950537768 AND
   system_unit.bi_rm_timestamp = 0) AS anon_1
   WHERE system_unit.bi_rm_timestamp = 0


first, the inner sql:

   pollux=# SELECT system_unit.fk_updated_by AS
   system_unit_fk_updated_by, system_unit.fk_created_by AS
   system_unit_fk_created_by, system_unit.dt_created_on AS
   system_unit_dt_created_on, system_unit.dt_updated_on AS
   system_unit_updated_on,
   pollux-# system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
   machine_unit.pk_fk_system_unit_id AS
   machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
   system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
   pollux-# system_unit_fk_organization_id,
   system_unit.u_system_unit_name AS system_unit_u_system_unit_name,
   system_unit.in_ipv4 AS system_unit_in_ipv4,
   system_unit.p_system_unit_type AS system_unit_system_unit_type,
   system_unit.i_version AS
   pollux-# system_unit_i_version, machine_unit.e_machine_type AS
   machine_unit_e_machine_type, machine_unit.e_printer_type AS
   machine_unit_e_printer_type
   pollux-# FROM system_unit JOIN machine_unit ON
   system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
   pollux-# WHERE machine_unit.pk_fk_system_unit_id = 24769797950537768
   AND system_unit.bi_rm_timestamp = 0;
     system_unit_fk_updated_by | system_unit_fk_created_by |
   system_unit_dt_created_on   |    syst
   em_unit_updated_on     | system_unit_bi_rm_timestamp |
   machine_unit_pk_fk_system_unit_id | syst
   em_unit_pk_system_unit_id | system_unit_fk_organization_id |
   system_unit_u_system_unit_name
     | system_unit_in_ipv4 | system_unit_system_unit_type |
   system_unit_i_version | machine_unit_e_
   machine_type | machine_unit_e_printer_type
   
---------------------------+---------------------------+------------------------------+--------
   
-----------------------+-----------------------------+-----------------------------------+-----
   
--------------------------+--------------------------------+-----------------------------------
   
-+---------------------+------------------------------+-----------------------+----------------
   -------------+-----------------------------
                               |         24769797950537732 | 2015-07-15
   17:49:57.41029-03 | 2015-07
   -15 17:49:57.410305-03 |                           0
   |                 24769797950537768 |
            24769797950537768 |              24769797950537729 |
   Workstation Super Legal do Richard
     | 10.0.0.2            |                            2
   |                     1 |
              50 |                           0
   (1 row)


1 row. ok, so far it's good. now, the whole query ...

   pollux=# SELECT count(*) AS count_1
   pollux-# FROM system_unit, (
   pollux(# SELECT system_unit.fk_updated_by AS
   system_unit_fk_updated_by, system_unit.fk_created_by AS
   system_unit_fk_created_by, system_unit.dt_created_on AS
   system_unit_dt_created_on, system_unit.dt_updated_on AS
   system_unit_updated_on,
   pollux(# system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
   machine_unit.pk_fk_system_unit_id AS
   machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
   system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
   pollux(# system_unit_fk_organization_id,
   system_unit.u_system_unit_name AS system_unit_u_system_unit_name,
   system_unit.in_ipv4 AS system_unit_in_ipv4,
   system_unit.p_system_unit_type AS system_unit_system_unit_type,
   system_unit.i_version AS
   pollux(# system_unit_i_version, machine_unit.e_machine_type AS
   machine_unit_e_machine_type, machine_unit.e_printer_type AS
   machine_unit_e_printer_type
   pollux(# FROM system_unit JOIN machine_unit ON
   system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
   pollux(# WHERE machine_unit.pk_fk_system_unit_id = 24769797950537768
   AND system_unit.bi_rm_timestamp = 0) AS anon_1
   pollux-# WHERE system_unit.bi_rm_timestamp = 0;
     count_1
   ---------
           2
   (1 row)
count_1 == 2 ... even if I remove the where bi_rm_timestamp = 0 (from the two clauses), the result is the same: 

   pollux=# SELECT count(*) AS count_1
   pollux-# FROM system_unit, (
   pollux(# SELECT system_unit.fk_updated_by AS
   system_unit_fk_updated_by, system_unit.fk_created_by AS
   system_unit_fk_created_by, system_unit.dt_created_on AS
   system_unit_dt_created_on, system_unit.dt_updated_on AS
   system_unit_updated_on,
   pollux(# system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
   machine_unit.pk_fk_system_unit_id AS
   machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
   system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
   pollux(# system_unit_fk_organization_id,
   system_unit.u_system_unit_name AS system_unit_u_system_unit_name,
   system_unit.in_ipv4 AS system_unit_in_ipv4,
   system_unit.p_system_unit_type AS system_unit_system_unit_type,
   system_unit.i_version AS
   pollux(# system_unit_i_version, machine_unit.e_machine_type AS
   machine_unit_e_machine_type, machine_unit.e_printer_type AS
   machine_unit_e_printer_type
   pollux(# FROM system_unit JOIN machine_unit ON
   system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
   pollux(# WHERE machine_unit.pk_fk_system_unit_id =
   24769797950537768) AS anon_1;
     count_1
   ---------
           2
   (1 row)


faq? :)



On 07/15/2015 04:58 PM, Richard Gerd Kuesters wrote:

right! sorry, now here we go (again):




--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

<<attachment: richard.vcf>>

Reply via email to