SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)

[mensanator at aol.com]

Marc 'BlackJack' Rintsch wrote:
> In <1157749692.092338.240820 at m73g2000cwd.googlegroups.com>,
> mensanator at aol.com wrote:
>
> > Ben Sizer wrote:
> >> I don't think so... it doesn't take much to say that the module
> >> implements a subset of SQL but stores ignores data types.
> >
> > So I can't just say
> >
> > WHERE qty=12
> >
> > I have to say
> >
> > WHERE (qty=12) OR (qty="12")
>
> No you just write the first and don't stuff strings into that column.
> It's your choice after all.

Not when I don't control the source of the data.
Despite the laboratory having been given a static
type definition for data deliverables, I sporadically
find text in my numeric fields.

And surely you don't think I write INSERT statements
for each of the over 500,000 records I have? The records
are inserted programatically.

Static types mean the exceptions...raise exceptions.
How am I supposed to identify the exceptions if SQLite
simply quietly converts the non-numeric data to text?

>
> > Do you know what INNER JOIN means?
> >
> > Do you know how important it is to a relational database?
> >
> > Can you explain how an INNER JOIN can even work, in theory,
> > with dynamic data types?
>
> The same as with static types!?  Where's the problem?
>
> table_a:
>
> id    name
> ----- ----------
> 42    bob
> 'foo' methusalem
> '42'  gaylord
>
> table_b:
>
> id    age
> ----- ------
> 42    23
> 'foo' 1000
> 0.5   None
>
> Join on `id`:
>
> id    name       age
> ----- ---------- ----
> 42    bob        23
> 'foo' methusalem 1000

Numbers JOINing numbers and text JOINing text doesn't illustrate
the problem. The problem is when SQLite allows bad data to be
inserted. The problem isn't that fields JOIN when they are not
supposed to, it's when they fail to JOIN when they are supposed
to.

>
> > The implications of violating the SQL Language Specification
> > are much more serious than you think.
>
> Or not so serious than you think.

Well, I can make up examples also.

Bill for customer:        Tom Smith
------------------------------------------------------------------
0003 Olsen's Standard Book of British Birds (Expurgated)  $ 99.95


Bill for customer:       Dick Smith
------------------------------------------------------------------
0002 A Sale of Two Titties                                $ 20.00
0005 David Coperfield                                     $  3.95


Bill for customer:      Harry Smith
------------------------------------------------------------------
0001 101 Ways to Start A Fight                            $ 19.95


Hmm...looks like item 4 isn't being billed to anyone. That's no
way to run a business. If I do a LEFT JOIN instead of an INNER JOIN:


Bill for customer:       None None
------------------------------------------------------------------
0004 Ethel the Aardvark Goes Quantity Surveying           $  9.99


Bill for customer:        Tom Smith
------------------------------------------------------------------
0003 Olsen's Standard Book of British Birds (Expurgated)  $ 99.95


Bill for customer:       Dick Smith
------------------------------------------------------------------
0002 A Sale of Two Titties                                $ 20.00
0005 David Coperfield                                     $  3.95


Bill for customer:      Harry Smith
------------------------------------------------------------------
0001 101 Ways to Start A Fight                            $ 19.95


I see the missing item, but still don't know who to bill it to.
What if I dumped the invoice table?

4
54
Ethel the Aardvark Goes Quantity Surveying
None
1975
9.99

3
001
Olsen's Standard Book of British Birds (Expurgated)
Olsen
None
99.95

2
032
A Sale of Two Titties
Charles Dikkens
1855
20.0

5
032
David Coperfield
Edmund Wells
1955
3.95

1
066
101 Ways to Start A Fight
some Irish gentleman
1919
19.95

Aha, it was sold to customer 54, so now I just need to dump
the customer table:

001
Tom
Smith
42

032
Dick
Smith
28

066
Harry
Smith
66

only to discover there is no customer 54! How can that happen?

invoices = [(1,'066','101 Ways to Start A Fight','some Irish
gentleman',1919,19.95), \
(2,'032','A Sale of Two Titties','Charles Dikkens',1855,20.00), \
(3,'001',"Olsen's Standard Book of British Birds
(Expurgated)","Olsen",None,99.95), \
(4,066,'Ethel the Aardvark Goes Quantity Surveying',None,1975,9.99), \
(5,'032','David Coperfield','Edmund Wells',1955,3.95)]

Oops! Forgot the quotes around the customer id for item 4.
But why didn't it become 66? Because the leading 0 made
it octal! A little static typing would have helped here.

Now, IF the same error is repeated in EVERY table that's JOINed,
THEN the dynamic typing won't be a problem. But that never happens
in practice. I populate one table and ensure it's correct. Someone
else populates the other table and screws it up so the JOIN never
works.

Sure, errors happen with static typing. After all, the values still
have to match. Dynamic typing allows for more potential errors and,
thanks to Murpy's Law, I will have a much bigger problem with data
integrity.

> 
> Ciao,
> 	Marc 'BlackJack' Rintsch