Hello,

I'm having trouble discovering what's going wrong with my use of natural
joins via sqldf.

Following the instructions under 4i at http://code.google.com/p/sqldf/,
which discusses creating indices to speed joins, I have been only unreliably
able to get natural joins to work.

For example,

> Tid <- c('AES 01-01-02 10:58:00', 'AES 01-01-02 11:53:00', 'AES 01-01-05
10:58:00', 'AES 01-01-11 12:30:00')
> A <- data.frame(Tid, dfName = 'a')
> B <- data.frame(Tid = Tid[2:4], dfName = 'b')
> C <- data.frame(Tid = Tid[1:3], dfName = 'c')

# then use the sqldf library
> library(sqldf)
> sqldf()

# to create indices on the Tid variable shared across data.frames
> sqldf('create index indA on A(Tid)')
> sqldf('create index indB on B(Tid)')
> sqldf('create index indC on C(Tid)')

# check to make sure everything is there
> sqldf('select * from sqlite_master')

# doing a natural join (implicitly on Tid)
# does not give the expected joins
> sqldf('select * from main.A natural join main.B')
[1] Tid    dfName
<0 rows> (or 0-length row.names)
> sqldf('select * from main.A natural join main.C')
[1] Tid    dfName
<0 rows> (or 0-length row.names)
> sqldf('select * from main.B natural join main.C')
[1] Tid    dfName
<0 rows> (or 0-length row.names)

# even using a where clause (which doesn't have the efficiency qualities I
need the indexed natural joins for) is problematic, setting values of the
dfName variable incorrectly for the data from C
> sqldf('select * from main.B b, main.C c where b.Tid = c.Tid')
                    Tid dfName                   Tid dfName
1 AES 01-01-02 11:53:00      b AES 01-01-02 11:53:00      b
2 AES 01-01-05 10:58:00      b AES 01-01-05 10:58:00      b

I'm grateful for your guidance on what I'm doing wrong with the natural join
in sqldf.

many thanks,
Nick

	[[alternative HTML version deleted]]

