Re: More pain and sufferring with Tropashko's materialized path...

boston103_at_hotmail.com (VC) wrote in message news:<31e0625e.0310181132.61cecc11_at_posting.google.com>...
> vadimtro_at_yahoo.com (Vadim Tropashko) wrote in message news:<22d2e427.0309171245.534732eb@posting.google.com>...
> > "Robin Tucker" <r.tucker_at_thermoteknix.com> wrote in message news:<bk6os9$hrt$1$8302bc10_at_news.demon.co.uk>...
> > > The error is "Arithmetic overflow error converting expression to data type
> > > float". This, I assume, is for the POWER function. So even when I use
> > > BIGINT or DECIMAL(12,0), the problem is still there.
> >
> > I suggest posting the function and invocation context at microsoft
> > forum, as there must be definitely a simple solution to your overflow
> > problem.
> >
> > Otherwise, I'm inclined to add the following paragraph into my next
> > article
> > <quote>
> > An immediate property of the above labeling schema is density: we have
> > all integer positive numbers utilized. Since some database
> > implementations have integers of limited range only, density might
> > become an attractive feature, because one would be able to store and
> > manipulate bigger trees before hitting arithmetic overflow exception.
> > Today, the problem of 16-bit overflow is ridiculous, of course, but
> > that is nevertheless the practical limitation that database developer
> > have to live with in some RDBMS implementations.
> > </quote>
> >
> > I'm suspecting once again, however, that the blank statement at the
> > very end that I had drawn from your experience is wrong. I would be
> > happy to learn that the overflow is a non-issue and remove the last
> > statement.
>
>
> Hello,
>
> I'll have to defend SQL Server. COnsider this in Oracle:
>
> select rownum, path, path_numer(path), path_denom(path) from (
> select '1' path from dual
> union all
> select '1.'||rownum from all_objects
> )
>
> Result:
> rownum path
> ...............................................
> 119 1.118 6.6461E+35 6.6461E+35
> 120 1.119 1.3292E+36 1.3292E+36
>
> ERROR:
> ORA-06502: PL/SQL: numeric or value error: number precision too large
> ORA-06512: at "STARGUS.CHILD_NUMER", line 5
> ORA-06512: at "STARGUS.PATH_NUMER", line 19
> ORA-06512: at line 1
>
> ... only after 120 rows.
>

This is correct, the coding numbers are growing exponentially. Therefore, any fixed precision ariphmetics would break at some point. This is why the new "dense" integer coding schema.

My question, however, was if this problem manifests itself much earlier in SQL Server. I cannot possibly believe (though I didn't check) that 16 bit and/or 32 bit ariphmetics is the only option in Transact SQL.

Once again, every symbolic algebra system on the market has implemented true mathematical numbers that don't have this ridiculous precision limit. Perhaps, the (unlimited precision) natural number and the rational number are the best candidatures to be implemented as User Defined Types. Received on Mon Oct 20 2003 - 21:44:04 CEST