That is very interesting! I'll evaluate this solution in the integration tests!
2013/6/24 Roger Thomas <[email protected]> > Just to complete the set of queries, the one below is the best I think I > can manage for doing 8,16 and 32 bit values. As you can see its not as > complicated as it does not have to do so many steps and so everything is > not duplicated. > > SELECT mod ( > ( > ( (results.myValue - ((results.myValue >> 1) & 3681400539) > - ((results.myValue >> 2) & 1227133513) ) + > ( (results.myValue - ((results.myValue >> 1) & 3681400539) > - ((results.myValue >> 2) & 1227133513) >> 3) ) > ) > & 3340530119) > ,63) > FROM > ( > SELECT power (2,32)-1 AS myValue > ) AS results > > I can also use power (2,32)-1 here as an example (the return value is 32). > With MySQL at least, power(2,64) returns a float which is not 2^64. > > > > > On Monday, June 24, 2013 7:00:19 AM UTC+1, Roger Thomas wrote: >> >> I've spent some time looking at other options and have now come up with >> the following >> >> SELECT mod ( >> ( >> (((results.myValue - ((results.myValue >> 1) & >> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401)) + >> ((results.myValue - ((results.myValue >> 1) & >> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401) >> >> 3)) & 8198552921648689607) >> + >> ((((results.myValue -((results.myValue >> 1) & >> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401)) + >> ((results.myValue - ((results.myValue >> 1) & >> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401) >> >> 3)) & 8198552921648689607) >> ) >> 6 >> ) >> ) & 8088187786164957247 >> ) >> ,4095) AS Result >> FROM >> ( >> SELECT 1256789 AS myValue >> ) AS results >> >> >> With some testing this seems to correctly count bits for 64bit long ints >> on at least MYSQL (currently my only test target). >> >> Its a version of a 1970's solution known as HAKMEM 169, which was >> orginally coded for the PDP10. Full background can be found on the web and >> there is some nice detail here >> >> http://blogs.msdn.com/b/jeuge/**archive/2005/06/08/hakmem-bit-** >> count.aspx<http://blogs.msdn.com/b/jeuge/archive/2005/06/08/hakmem-bit-count.aspx> >> >> A short background is that it works by suming up the number of bits in >> each block of 3 bits, it then combines the results of each set of 2 blocks >> and then finally uses MOD to create a sum of all the results. All of the >> long 'magic' numbers make far more sense if viewed as octal values as they >> are used as bitwise masks. The reason why the code is so small is because >> each operation is working on a total of 22 blocks (21 x 3 bit and 1 x 1 >> bit). Its not used nowadays as the MOD function is costly compaired to >> other coding options. >> >> For general ease of use I would say that the solution already included in >> jOOQ is good enough and far easier for people to understand if they look at >> the queries. Compaired to my original post, this solution should be better >> across platforms as it only reduces the 64 bit value so there is no >> problems with maths overflows. >> >> Roger >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
