Conversions (BIN2DEC and friends) - how much to spec?

["David A. Wheeler" <dwheeler@dwheeler.com>]

There are a number of base conversion functions of the form xxx2yyy
whose standard semantics can charitably described as
"created by the incompetent".  I'm being nice, believe it or not;
I suspect their creators are sorry for their design!

Yet these crazy semantics seem to implemented identically
by many applications; I've already checked Excel, OpenOffice.org,
and Gnumeric, and they all agree on them (which is the usual
criteria for a standard, after all). I have every reason to
believe that people have important-to-them spreadsheets that
depend on these (crazy) semantics.

We have three choices that I can see:
1. Don't include them in the spec.  Clean, but then we don't
   provide an upgrade path for those with spreadsheets that
   currently use them.  I think we MUST provide an upgrade path
   for existing spreadsheet documents, so this sounds less appealing.
   The spec includes essentially all functions from
   Excel and OpenOffice.org, so that anyone using those programs
   should have an easy upgrade path.
2. Include them, but leave out the "gross" parts as
   "implementation-defined".  This has the same basic
   problem as #1... we're not helping people handle their
   existing spreadsheet documents.
3. Include them, including the weird semantics that everyone
   implements (and people probably depend on),
   but also include "nicer" functions with
   saner semantics.  In this case, BASE and DECIMAL are far
   saner and simpler, and are widely implemented
   (e.g., BASE is implemented by OpenOffice.org 2 / Sun StarOffice,
   GNOME Gnumeric, KDE KSpread, and Corel Quattro Pro).
   This gives people a transition plan.

As you can tell, I think we should do #3, though I think I need
to hold my nose to write down this particular nonsense.
I'm not even sure we can deprecate the xxx2yyy functions,
because for some circumstances they make sense.
We could deprecate some of their craziness later on, I suppose.

I'd like to hear comments on this.  Details below, so you
can see what I mean.

==== DETAILS ====

Let me give an example: BIN2DEC. BIN2DEC converts
binary numbers to decimal numbers.  Here are examples:
* BIN2DEC("101") is 5, and that's reasonable.
* BIN2DEC(101) is 5 - it re-interprets the decimal presentation.
  Weird, but okay.
* BIN2DEC("1100000000") is -256.  What's that, you say?
  Well, the 10th bit is the sign bit, no doubt to support all
  those people using 10-bit computers :-).

The hex functions, etc., all have sign bits, and in the
weirdest, most unjustifiable places you can imagine.
Hex allows 10 hex digits (40th bit is sign).. so it cannot
handle 64-bit registers (never mind 64-bit computers).
Do they actually allow negative numbers as input, or allow you to
specify a register width? Nah.

Yet because these are the ONLY base-conversion functions that
EVERYONE implements, people work around them, and I believe
even depend on this odd nonsense.  How sad.

Thankfully, the widely-used BASE and DECIMAL provide a
rational approach (they're both in OpenOffice.org; neither
are in Excel).  BASE(X;base) converts number X to text
representing the given base; DECIMAL(T;base) converts
text T of given base into Number.  Far more sensible.

So I think we should include the xxx2yyy functions as a
transition strategy, with their full nonsense (ugh!),
as long as we also include BASE and DECIMAL
as more rational/reasonably designed interfaces.

--- David A. Wheeler