jeudi 23 avril 2015

nodejs sqlite3 query colomn returns SlowBuffer

Here is my situation, I'm executing a similar SQLite query using the sqlite3 module in a Nodejs (actual query a larger with 4-6 left joins on other tables).

select a.ID, a.Name, case when fixed is NULL then 0 else fixed end as fixed, case when failed is NULL then 0 else failed end as failed, case when pending is NULL then 0 else pending end as pending, case when total is NULL then 0 else total end as total
from ACTIONS a 
left join (select 
    deploymentId, ROUND(CAST(SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS float) / count(*) * 100, 2) AS fixed, ROUND(CAST(SUM(CASE WHEN status = 4 OR status < 0 THEN 1 ELSE 0 END) AS float) / count(*) * 100, 2) AS failed, ROUND(CAST(SUM(CASE WHEN status = 7 OR status = 1 OR status = 8 OR status = 2 OR status = 19 OR status = 6 THEN 1 ELSE 0 END) AS float) / count(*) * 100, 2) AS pending, count(*) AS total 
from RESULTS
group by deploymentId) r on a.id = r.deploymentId
where a.parentID is NULL

Basically, the issue is when I execute this query, I get the correct results where "fixed" is 0 when I run into a row where "fixed" is NULL.

HOWEVER!!! When I apply the following filter/contraint on the query:

select a.ID, a.Name, case when fixed is NULL then 0 else fixed end as fixed, case when failed is NULL then 0 else failed end as failed, case when pending is NULL then 0 else pending end as pending, case when total is NULL then 0 else total end as total
from ACTIONS a 
left join (select 
    deploymentId, ROUND(CAST(SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS float) / count(*) * 100, 2) AS fixed, ROUND(CAST(SUM(CASE WHEN status = 4 OR status < 0 THEN 1 ELSE 0 END) AS float) / count(*) * 100, 2) AS failed, ROUND(CAST(SUM(CASE WHEN status = 7 OR status = 1 OR status = 8 OR status = 2 OR status = 19 OR status = 6 THEN 1 ELSE 0 END) AS float) / count(*) * 100, 2) AS pending, count(*) AS total 
from RESULTS
group by deploymentId) gr on a.id = gr.deploymentId
where a.parentID is NULL AND fixed >= 2

The following returns for fixed where fixed is originally NULL: fixed:< SlowBuffer 70 61 74 63 68 >

When I apply any other filters to this query, I don't get this! I'm not sure what is happening and when I look this up, I see that people are actually trying to utilize SlowBuffer. I'm really stuck and not how to start debugging this, any help is appreciated! Thanks!!!!

Aucun commentaire:

Enregistrer un commentaire