Home - General / All posts - Bitwise logic and types
 tjhb8,657 post(s) #18-Oct-18 22:22 It is currently a bit of a drag to use bitwise logic in SQL9.The result of every bitwise operator is returned as FLOAT64, regardless of the input types.So for example evenCAST(1 AS UINT8)BITORCAST(2 AS UINT8)returnsfloat64: 3Here's an example, packing several pieces of data into one UINT32 value. (The objective is to group large amounts of data on a single field, which is given an atomic index. It does prove faster than grouping by multiple fields with a composite index.)SELECT    CAST(        CAST(            -- bits 0..15: positive elevation            CAST(ABS([elevation]) AS UINT32)            -- mask bits 16..31 pro forma            BITAND                CAST(2^16 - 1 AS UINT32)            AS UINT32            )        BITOR            -- bit 16: sign            CAST(                CASE                    WHEN [elevation] < 0 THEN 2^16                    ELSE 0 -- positive or zero                END                AS UINT32                )        BITOR             -- bit 17: island            CAST(                CASE                    WHEN [island] = 1 THEN 2^17 -- north                    ELSE 0 -- south                END                AS UINT32                )        BITOR            -- bit 18: indefinite            CAST(                CASE                    WHEN [definition] = 'indefinite' THEN 2^18                    ELSE 0                END                AS UINT32                )        BITOR            -- bit 19: supplementary            CAST(                CASE                    WHEN [designated] = 'supplementary' THEN 2^19                    ELSE 0                END                AS UINT32                )        BITOR            -- bit 20: depression            CAST(                CASE                    WHEN [nat_form] = 'depression' THEN 2^20                    ELSE 0                END                AS UINT32                )        BITOR            -- bit 21: closed            CAST(                CASE                    WHEN GeomCoordXY([Geom], 0)                         = GeomCoordXY([Geom], GeomCoordCount([Geom]) - 1)                        THEN 2^21                    ELSE 0                END                AS UINT32                )            -- bits 22..31 unused        AS UINT32        ) AS [contour code]It's really nice having the bitwise operators, but using them feels clumsy.Would it not be possible for SQL9 functions to use the narrowest input type for their output?(Also it would be nice to have shift operators.)I could use a script function. Maybe that would be better. (Though I think the questions above would remain.)
 tjhb8,657 post(s) #18-Oct-18 22:44 ...OK that begs the question. If FLOAT64 is the native numeric type for SQL9, why not pack bitwise integer data into whole-number FLOAT64? It might be wasteful in principle--but perhaps not in practice, if FLOAT64 is always used anyway under the hood.Why not just trust the bitwise operators to give correct and robust integer results on FLOAT64 values? Especially since that trust is currently implicit regardless.I don't have a good answer to that. My understanding of binary representation is not good enough, especially for floats.
 adamw8,447 post(s) #29-Oct-18 13:27 You don't have to be casting to integer types to do bitwise math - all BITxxx operations already cast to INT32 by themselves.There are several improvements which we are planning to do here, with the most important one being:Propagate numeric types not to FLOAT64 but to the narrowest type appropriate for the result based on the types of operands.This is a variant of what you suggest in the first post. It has several important consequences to query writers, things basically get more complicated, but we have to do it sooner or later.
 adamw8,447 post(s) #29-Oct-18 13:33 Forgot to say:FLOAT64 holds all INT32 values precisely. So, you can do BITAND (2^8 - 1) and trust it all to just work. Just keep in mind that bit operations all use INT32. Not INT64 and not UINT32.
 tjhb8,657 post(s) #30-Oct-18 01:06 Two great answers. Thank you Adam.I think the changes to numeric return types will be welcome, but I imagine they will be a right pain for you to implement, and they can't be a huge priority--especially given this extra knowledge about what is happening under the hood.For what it's worth, I'll post timings for grouping by the bitwise composite attribute (after removing redundant casts), compared with grouping by multiple source fields with a composite index.