Re: Our next adventure: Types and conversions

[Tom Metcalf <tom.metcalf@opendocument.us>]

 On 5/21/06, David A. Wheeler <dwheeler@dwheeler.com> wrote: 
A. Logical is REQUIRED to be distinct from Number.
    Inconsistent with OOo, Lotus 1-2-3, & many others.
B. Logical is REQUIRED to be the same as Number:
    Inconsistent with Excel, Gnumeric.
C. "Logical" used as a notional type (so that we can easily 
   identify functions that take/return logicals), but we
   explicitly permit EITHER of the above.
I recommend "C".  If implementations can co-exist
with this variance, I think we can too. 
 
 
 I think "C" is OK.  Given the current diversity of applications, it may be the only realistic choice.
  
 

 
Next up: how do they interact? In particular: if a function
expects a Number but gets a Text value, then what? 
A. Text auto-converted to Number.  Excel & Gnumeric
    do this.  There is a BIG PROBLEM here involving
    locale... a conversion can work in one locale, & fail
    in another.
    A1. Conversions usually work using the 
    "current locale" this is a problem -- the SAME spreadsheet
    would work in one place and fail in another.  Yes, many
    implementations (e.g., Excel) really do this.
    A2. An alternative would be to save the 
    locale of a spreadsheet on creation, and ALWAYS use that
    "sheet locale" on an automatic conversion. But note that
    nobody does that, so we have the risk of invention
    without experience. 
B. Text converted to 0.  Lotus 1-2-3 does this.
C. Text converted to 0 if via reference, and auto-converted
    to number if in-line.  OOo 2.0 does this.
D. Text converted to error.  Eike thinks this would be safest, 
    since it would force spreadsheet authors to notice.
    I know of no one who does this.
E. Allow some set of the above.  Spreadsheet users could
    add VALUE() calls where they wanted conversions.
    One problem: VALUE() doesn't have a fixed locale, it 
    uses the current locale (usually).  We could modify the
    definition of VALUE, or create VALUEL(v;locale).
    (Creating a new function as a standards invention
    seems less evil to me, though it's still not ideal.) 
I recommend (E) with "all of the above except A2".
I'm not sure we want to create VALUEL(), though
it'd be nice to have.
 
 
 A2 might be OK if the locale is not allowed to vary and the formula representation in the file would always use some particular locale (C, I suppose).  After reading the file, the application would convert to the user's locale, but the formula is always stored in the same way everywhere.  That would require a conversion on both output and input, though.
 
 My primary concern is that my spreadsheets be readable anwhere.  It is very important that valid implementations get the same answer.   D would not work if a spreadsheet works for me, but fails with an error for someone else.   Is that possible?
 
 E might work, but would that require care on the part of the author to use the VALUEL function to guarantee that a spreadsheet is portable?  Or could that be automated?
 
 I don't like B or C, though in the end we might be stuck with supporting something like that, give that some applications already do this.
 
 Tom