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

[Paul McNett]

mensanator at aol.com wrote:
> 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?

Let's stop the pissing contest and just see how it works. After all, 
this is Python and we can easily just try it out. Here's my example. 
Please tell me how this causes unexpected results, and why it isn't SQL. 
Please modify my example to get it to cause a catastrophe, and post it 
here so we can see the errors of our ways and be properly humbled.

#-- Preliminaries:
 >>> from pysqlite2 import dbapi2 as sqlite
 >>> con = sqlite.connect("test.db")
 >>> cur = con.cursor()

#-- Create 3 tables for a M:M relationship between customers
#-- and categories:
 >>> cur.execute("create table customers (id integer primary key 
autoincrement, name char)")
 >>> cur.execute("create table categories (id integer primary key 
autoincrement, name char)")
 >>> cur.execute("create table cust_cat (id integer primary key 
autoincrement, cust_id integer, cat_id integer)")

#-- Insert some test data into customer and categories:
 >>> cur.execute("insert into customers (name) values ('Ziggy Marley')")
 >>> cur.execute("insert into customers (name) values ('David Bowie')")
 >>> cur.execute("insert into categories (name) values ('Glam Rock')")
 >>> cur.execute("insert into categories (name) values ('Nuevo Reggae')")
 >>> cur.execute("insert into categories (name) values ('Male Singers')")
 >>> cur.execute("select * from customers")

#-- Take a look at the data (and retrieve the pk's):
 >>> cur.fetchall()
[(1, u'Ziggy Marley'), (2, u'David Bowie')]
 >>> cur.execute("select * from categories")
 >>> cur.fetchall()
[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]

#-- Relate some customers to some categories. Note how I send strings
#-- in some places and ints in others:
 >>> cur.execute("insert into cust_cat (cust_id, cat_id) values (1, 3)")
 >>> cur.execute("insert into cust_cat (cust_id, cat_id) values (1, '2')")
 >>> cur.execute("insert into cust_cat (cust_id, cat_id) values ('2', '1')")
 >>> cur.execute("insert into cust_cat (cust_id, cat_id) values ('2', 3)")

#-- Run some queries:
 >>> cur.execute("select customers.id as cust_id, customers.name as 
cust_name, categories.id as cat_id, categories.name as cat_name from 
customers inner join cust_cat on cust_cat.cust_id = customers.id inner 
join categories on categories.id = cust_cat.cat_id order by 2,4")
 >>> cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock'), (2, u'David Bowie', 3, u'Male 
Singers'), (1, u'Ziggy Marley', 3, u'Male Singers'), (1, u'Ziggy 
Marley', 2, u'Nuevo Reggae')]

 >>> cur.execute("select customers.id as cust_id, customers.name as 
cust_name, categories.id as cat_id, categories.name as cat_name from 
customers inner join cust_cat on cust_cat.cust_id = customers.id inner 
join categories on categories.id = cust_cat.cat_id where categories.id = 
1 order by 2,4")
 >>> cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock')]

 >>> cur.execute("select customers.id as cust_id, customers.name as 
cust_name, categories.id as cat_id, categories.name as cat_name from 
customers inner join cust_cat on cust_cat.cust_id = customers.id inner 
join categories on categories.id = cust_cat.cat_id where categories.id = 
'1' order by 2,4")
 >>> cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock')]

 >>> cur.execute("select customers.id as cust_id, customers.name as 
cust_name, categories.id as cat_id, categories.name as cat_name from 
customers inner join cust_cat on cust_cat.cust_id = customers.id inner 
join categories on categories.id = cust_cat.cat_id where categories.id = 
'2' order by 2,4")
 >>> cur.fetchall()
[(1, u'Ziggy Marley', 2, u'Nuevo Reggae')]

 >>> cur.execute("select customers.id as cust_id, customers.name as 
cust_name, categories.id as cat_id, categories.name as cat_name from 
customers inner join cust_cat on cust_cat.cust_id = customers.id inner 
join categories on categories.id = cust_cat.cat_id where categories.id = 
'3' order by 2,4")
 >>> cur.fetchall()
[(2, u'David Bowie', 3, u'Male Singers'), (1, u'Ziggy Marley', 3, u'Male 
Singers')]

 >>> cur.execute("select customers.id as cust_id, customers.name as 
cust_name, categories.id as cat_id, categories.name as cat_name from 
customers inner join cust_cat on cust_cat.cust_id = customers.id inner 
join categories on categories.id = cust_cat.cat_id where categories.id = 
3 order by 2,4")
 >>> cur.fetchall()
[(2, u'David Bowie', 3, u'Male Singers'), (1, u'Ziggy Marley', 3, u'Male 
Singers')]

If I have skipped the test case that will fail, please enlighten me.

-- 
Paul McNett
http://paulmcnett.com
http://dabodev.com