Hi,
When applying a sort the syntax in ibis.cpp is as follows:
[SELECT ...] [FROM ...] WHERE ... [ORDER BY ... [ASC | DESC]] [LIMIT ...]
In essence fastbit only supports ASC or DESC at the end of a list of
COLUMNS, however there are many times where one column might need to be
sorted in an ASC fasion and in conjunction with another column in a DESC
fashion.
In fastbit is non-standard as it sorts both mycol1 AND mycol2 in a DESC
fashion thus when I run
FASTBIT select mycol1, mycol2 from sorttest order by mycol1 , mycol2
DESC;
vs.
INFORMIX select mycol1, mycol2 from sorttest order by mycol1 , mycol2
DESC;
I get two (2) different answers or orderings - what fast bit does is the
following:
INFORMIX select mycol1, mycol2 from sorttest order by mycol1 DESC,
mycol2 DESC;
but this is different from
INFORMIX select mycol1, mycol2 from sorttest order by mycol1 mycol2
DESC;
INFORMIX select mycol1, mycol2 from sorttest order by mycol1 ASC,
mycol2 DESC;
If an update is made IT WOULD IMPACT ALL EXISTING deployments, as such the
update should be dependent on a configure flag for backward compatibility.
Consider:
A) All legal fastbit permutations on one column order by's (just three)
select mycol1, mycol2 from sorttest order by mycol1 ;
select mycol1, mycol2 from sorttest order by mycol1 ASC;
select mycol1, mycol2 from sorttest order by mycol1 DESC;
B) All legal fastbit permutations on one column order by's (once again juse
three !!!)
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 ;
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 ASC;
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 DESC;
C) In standard SQL (informix and others) with a table with nine rows with
all permutations from 1 to 3 for both columns I many more legal permutations
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 ;
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 ASC;
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 DESC;
select mycol1, mycol2 from sorttest order by mycol1 ASC, mycol2 ;
select mycol1, mycol2 from sorttest order by mycol1 ASC, mycol2 ASC;
select mycol1, mycol2 from sorttest order by mycol1 ASC, mycol2 DESC;
select mycol1, mycol2 from sorttest order by mycol1 DESC, mycol2 ;
select mycol1, mycol2 from sorttest order by mycol1 DESC, mycol2 ASC;
select mycol1, mycol2 from sorttest order by mycol1 DESC, mycol2 DESC;
I would expect fastbit to be able to support all of the above standard sort
requests - example from informix
========================================
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 ;
Database selected.
mycol1 mycol2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 ASC;
Database selected.
mycol1 mycol2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 , mycol2 DESC;
Database selected.
mycol1 mycol2
1 3
1 2
1 1
2 3
2 2
2 1
3 3
3 2
3 1
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 ASC, mycol2 ;
Database selected.
mycol1 mycol2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 ASC, mycol2 ASC;
Database selected.
mycol1 mycol2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 ASC, mycol2 DESC;
Database selected.
mycol1 mycol2
1 3
1 2
1 1
2 3
2 2
2 1
3 3
3 2
3 1
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 DESC, mycol2 ;
Database selected.
mycol1 mycol2
3 1
3 2
3 3
2 1
2 2
2 3
1 1
1 2
1 3
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 DESC, mycol2 ASC;
Database selected.
mycol1 mycol2
3 1
3 2
3 3
2 1
2 2
2 3
1 1
1 2
1 3
9 row(s) retrieved.
Database closed.
========================================
select mycol1, mycol2 from sorttest order by mycol1 DESC, mycol2 DESC;
Database selected.
mycol1 mycol2
3 3
3 2
3 1
2 3
2 2
2 1
1 3
1 2
1 1
9 row(s) retrieved.
Database closed.
_______________________________________________
FastBit-users mailing list
[email protected]
https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
