Stupid SQL, Second Edition - handling the empty string

So it turns out that Oracle has a unique flaw among databases. It treats the empty string exactly as if it were a NULL field. So, for instance, the relation '' = '' is actually false much in the same way that NULL is not equal to NULL.

I've seen some question the utility of empty strings at all, but I ran into this today entirely by accident. I had two tables, both with identical "Path" and "Language" columns, and I wanted to join them. In its simplest form, the query looked like this:

select * from TexturesA
left join TexturesB on TexturesA.Path = TexturesB.Path and TexturesA.Language = TexturesB.Language

Now, as luck would have it, we used an empty string to denote "all languages." This caused the join to fail for any all-languages texture asset, because the equality check on language would fail.

Solution? There are a couple, but I ended up with this gem:

select * from TexturesA
left join TexturesB on TexturesA.Path = TexturesB.Path and TexturesA.Language || '~' = TexturesB.Language || '~'

Not my proudest moment. Works great in practice, though. I suspect that this would keep Oracle from leveraging any indices on the column; if indices are critical, you could probably do the more long-winded:

select * from TexturesA
left join TexturesB on TexturesA.Path = TexturesB.Path and ((TexturesA.Language is null and TexturesB.language is null) or (TexturesA.Language = TexturesB.Language))

Or you could add a function-based index of Language || '~' to the table. Either way, feels like a kludge to me.

Stupid SQL, First Edition - now with support for negative numbers

This one handles negative numbers too, up to negative 2^64:

select
lpad(rawtohex(chr(floor(mod(MyNumber + 18446744073709551616, 18446744073709551616) / 4294967296))), 8, '0') ||
lpad(rawtohex(chr(mod(MyNumber + 18446744073709551616, 4294967296))), 8, '0')
from dual

Stupid SQL, First Edition - turning numbers into hex strings

The following Oracle SQL command will take a 64-bit positive number and emit a 16-character hexadecimal string.

select
lpad(rawtohex(chr(floor(MyNumber / 4294967296))), 8, '0') ||
lpad(rawtohex(chr(mod(MyNumber, 4294967296))), 8, '0')
from dual

I'll post an updated solution for negative numbers when I get a chance. (Basic idea: add 2^64, then mod by 2^64.)

_wtoi64 is broken for large values

I recently found a bug in _wtoi64 on Dev Studio 2005. When passed some values larger than 9,223,372,036,854,775,807 (i.e. the high bit of the 64-bit word is set), the returned value will have some bits flipped. This manifested itself in one of our internal tools.

Workaround? Use sscanf (the wide-char version, however you spell it) with %I64d or %I64u. For some strange reason, sscanf and _wtoi64 don't share any logic for translating strings to values. Go figure.

Good job, Microsoft. *golf clap*

Mission Statement

This blog is here so I can record things I've discovered, typically messed up technical stuff. I don't expect anyone to follow it, since there isn't really going to be a common theme, but I wouldn't be surprised if people find it via Google.