dimagi / django-cte

Common Table Expressions (CTE) for Django
Other
334 stars 46 forks source link

it's wrong number when i wnat to get total number by using count() #88

Closed fuuhoo closed 8 months ago

fuuhoo commented 8 months ago

environment:

Django                    3.2.16     
django-mysql              3.9.0      
django-cte                1.3.1      

code:

        cte = With(flowTaskDealRecordModel.objects.filter(conn).annotate(
            rw=models.Window(
                expression=window.RowNumber(),
                partition_by=[models.F('taskID_id')],
                order_by=[models.F('cTime').desc()],
            )
        ))

        qs = cte.queryset().with_cte(cte).select_related("flowID","taskID").filter(Q(rw=1)).order_by("-cTime")
        print(qs.query)
        count = qs.count()
        print("count",count)
        count2 = len(qs)
        print("count2",count2)
        count3 = qs.count()
        print("count3",count3)

output:


count 18143
count2 17906

image

sql

WITH RECURSIVE `cte` AS (
    SELECT
        `flow_taskDealRecord`.`id`,
        `flow_taskDealRecord`.`flowID_id`,
        `flow_taskDealRecord`.`taskID_id`,
        `flow_taskDealRecord`.`index`,
        `flow_taskDealRecord`.`accountID`,
        `flow_taskDealRecord`.`accountIDName`,
        `flow_taskDealRecord`.`personFactName`,
        `flow_taskDealRecord`.`company`,
        `flow_taskDealRecord`.`companyName`,
        `flow_taskDealRecord`.`result`,
        `flow_taskDealRecord`.`processFlag`,
        `flow_taskDealRecord`.`reason`,
        `flow_taskDealRecord`.`validFlag`,
        `flow_taskDealRecord`.`cTime`,
        `flow_taskDealRecord`.`detail`,
        ROW_NUMBER() OVER ( PARTITION BY `flow_taskDealRecord`.`taskID_id` ORDER BY `flow_taskDealRecord`.`cTime` DESC ) AS `rw` 
    FROM
        `flow_taskDealRecord` 
    ) SELECT
    `cte`.`id`,
    `cte`.`flowID_id`,
    `cte`.`taskID_id`,
    `cte`.`index`,
    `cte`.`accountID`,
    `cte`.`accountIDName`,
    `cte`.`personFactName`,
    `cte`.`company`,
    `cte`.`companyName`,
    `cte`.`result`,
    `cte`.`processFlag`,
    `cte`.`reason`,
    `cte`.`validFlag`,
    `cte`.`cTime`,
    `cte`.`detail`,
    `cte`.`rw` AS `rw`,
    `flow_flow`.`id`,
    `flow_flow`.`typeID_id`,
    `flow_flow`.`name`,
    `flow_flow`.`flowDescribe`,
    `flow_task`.`id`,
    `flow_task`.`taskName`,
    `flow_task`.`taskObjID`,
    `flow_task`.`taskObjCode`,
    `flow_task`.`taskObjName`,
    `flow_task`.`taskObjCompanyID`,
    `flow_task`.`taskObjCompanyName`,
    `flow_task`.`taskObjTeamID`,
    `flow_task`.`taskObjTeamName`,
    `flow_task`.`flowType_id`,
    `flow_task`.`flowID_id`,
    `flow_task`.`currentIndex`,
    `flow_task`.`resultCallbackURL`,
    `flow_task`.`detailCallbackURL`,
    `flow_task`.`status`,
    `flow_task`.`reason`,
    `flow_task`.`cTime`,
    `flow_task`.`mTime`,
    `flow_task`.`callBackSuccess` 
FROM
    `cte`
    INNER JOIN `flow_flow` ON ( `cte`.`flowID_id` = `flow_flow`.`id` )
    INNER JOIN `flow_task` ON ( `cte`.`taskID_id` = `flow_task`.`id` ) 
WHERE
    `cte`.`rw` = 1 
ORDER BY
    `cte`.`cTime` DESC

i get 18143 when use count() first, i get 17096 when i use len(). and then i get 17096 when using count() second. i get 17096 when execute sql directly.so why i can get 18143? so is there some bugs for count() when using django-cte?