Subscribe to this thread
Home - General / All posts - Bitwise logic and types
tjhb
10,094 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 even

CAST(1 AS UINT8)

BITOR

CAST(2 AS UINT8)

returns

float64: 3

Here'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.)

tjhb
10,094 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.

adamw


10,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.

adamw


10,447 post(s)
#29-Oct-18 13:33

Forgot to say:

FLOAT64 holds all INT32 values precisely. So, you can do <value> 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.

tjhb
10,094 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.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.