search

knizhnik / imcs

In-Memory Columnar Store extension for PostgreSQL
Apache License 2.0
201 stars 33 forks source link

ERROR: group by sequence doesn't match values sequence #30

Closed amutu closed 10 years ago

amutu commented 10 years ago

cs_hash_dup_count(y.uin,y.revision) report ERROR: group by sequence doesn't match values sequence

crash=> select clientversion,t.agg_value,t.group_by from (select wx_version.clientversion,x.uin,x.revision from wx_version,crashlog_get(clientversion,now()::timestamp - '1 days'::interval,now()::timestamp) as x where x is not null) y,cs_project_agg(cs_hash_dup_count(y.uin,y.revision)) as t(agg_value,group_by) order by 2 desc nulls last limit 10; NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg NOTICE: IMCS command: hash_dup_count NOTICE: IMCS command: project_agg ERROR: group by sequence doesn't match values sequence Time: 2045.859 ms

knizhnik commented 10 years ago

Sorry, but I can not reproduce the problem with the schema and data you have sent to me. First of all I am not able to execute your query as it is, so I have runt the following query:

select * from crashlog_get('{604176597}'::int[],now()::timestamp - '10 days'::interval,now()::timestamp) as y,cs_project_agg(cs_hash_dup_count(y.uin,y.revision)) as t(agg_value,group_by) order by 2 desc nulls last limit 10;

It returns some data and doesn't report any error.

amutu commented 10 years ago

after update to git head,the sql run fine. Thank you for your help!

knizhnik commented 10 years ago

Still not be able to reproduce the crash - I assume it is because of date range (which is specified in this query relative to current time). If I execute original query it returns nothing:

postgres=# select clientversion,t.agg_value,t.group_by from (select wx_version.clientversion,x.uin,x.revision from wx_version,crashlog_get(clientversion,now()::timestamp - '1 days'::interval,now()::timestamp) as x where x is not null) y,cs_project_agg(cs_hash_dup_count(y.uin,y.revision)) as t(agg_value,group_by) order by 2 desc nulls last limit 10; clientversion | agg_value | group_by ---------------+-----------+---------- (0 rows)

If I change data range to -10 days it returns something: postgres=# select clientversion,t.agg_value,t.group_by from (select wx_version.clientversion,x.uin,x.revision from wx_version,crashlog_get(clientversion,now()::timestamp - '10 days'::interval,now()::timestamp) as x where x is not null) y,cs_project_agg(cs_hash_dup_count(y.uin,y.revision)) as t(agg_value,group_by) order by 2 desc nulls last limit 10; clientversion | agg_value | group_by
---------------+-----------+-------------------------------------------------------------------- 620888113 | 859398 | \x3631333232380000000000000000000000000000000000000000000000000000 620888369 | 651386 | \x3634373939350000000000000000000000000000000000000000000000000000 352452881 | 386858 | \x3064346263353966303931323333343938393033383135356339363661366638 620888368 | 247547 | \x3634373736360000000000000000000000000000000000000000000000000000 620888112 | 170943 | \x3631323936380000000000000000000000000000000000000000000000000000 604307749 | 151902 | \x3000000000000000000000000000000000000000000000000000000000000000 620757816 | 138824 | \x3535303732390000000000000000000000000000000000000000000000000000 620822576 | 108227 | \x3538323236330000000000000000000000000000000000000000000000000000 620757252 | 102509 | \x3531313038360000000000000000000000000000000000000000000000000000 620888132 | 101342 | \x3632333338350000000000000000000000000000000000000000000000000000 (10 rows)

knizhnik commented 10 years ago

O, great! I am adding now to IMSC dictionary: a way to map varying size strings into integer identifiers. I suspect that it will help ti address issue #29