Behavior of FULL JOIN differs depending on whether an index exists
This was the simplest test program I could put together to demonstrate the problem:
package main
import (
"github.com/cznic/ql"
)
func main() {
db, err := ql.OpenMem()
if err != nil {
panic(err)
}
initDB(db)
createBuggyIndex(db) // Comment out this line and the program runs successfully
pid1 := createPerson(db, "alice")
aid1 := createAward(db, "awesome")
aid2 := createAward(db, "best")
zeroFullJoinCount := countFullJoin(db, pid1)
if zeroFullJoinCount != 0 {
println(zeroFullJoinCount)
panic("Incorrect full join count before creating records")
}
insertPersonAward(db, pid1, aid1)
insertPersonAward(db, pid1, aid2)
initialFullJoinCount := countFullJoin(db, pid1)
if initialFullJoinCount != 2 {
println(initialFullJoinCount)
panic("Incorrect full join count before deleting records")
}
initialNumJoinRecords := countJoinRecords(db)
if initialNumJoinRecords != 2 {
println(initialNumJoinRecords)
panic("Incorrect number of join records before deleting records")
}
deletePersonAwards(db, pid1)
afterNumJoinRecords := countJoinRecords(db)
if afterNumJoinRecords != 0 {
println(afterNumJoinRecords)
panic("Incorrect number of join records after deleting records")
}
afterFullJoinCount := countFullJoin(db, pid1)
if afterFullJoinCount != 0 {
println(afterFullJoinCount)
panic("Incorrect full join count after deleting records")
}
println("Everything looks okay.")
}
func initDB(db *ql.DB) {
setup := `BEGIN TRANSACTION;
CREATE TABLE people (name string NOT NULL);
CREATE TABLE awards (name string NOT NULL);
CREATE TABLE people_awards (person_id int NOT NULL, award_id int NOT NULL);
COMMIT;
`
ctx := ql.NewRWCtx()
_, _, err := db.Run(ctx, setup)
if err != nil {
panic(err)
}
}
func createBuggyIndex(db *ql.DB) {
setup := `BEGIN TRANSACTION;
CREATE INDEX people_awards_person_id ON people_awards (person_id);
COMMIT;
`
ctx := ql.NewRWCtx()
_, _, err := db.Run(ctx, setup)
if err != nil {
panic(err)
}
}
func createPerson(db *ql.DB, name string) int64 {
ctx := ql.NewRWCtx()
_, _, err := db.Run(ctx, `BEGIN TRANSACTION; INSERT INTO people(name) VALUES ($1); COMMIT`, name)
if err != nil {
panic(err)
}
return ctx.LastInsertID
}
func createAward(db *ql.DB, name string) int64 {
ctx := ql.NewRWCtx()
_, _, err := db.Run(ctx, `BEGIN TRANSACTION; INSERT INTO awards(name) VALUES ($1); COMMIT`, name)
if err != nil {
panic(err)
}
return ctx.LastInsertID
}
func insertPersonAward(db *ql.DB, personID, awardID int64) {
ctx := ql.NewRWCtx()
_, _, err := db.Run(ctx, `BEGIN TRANSACTION; INSERT INTO people_awards(person_id, award_id) VALUES ($1, $2); COMMIT`, personID, awardID)
if err != nil {
panic(err)
}
}
func countFullJoin(db *ql.DB, personID int64) int {
stmt := `SELECT award.id, award.name FROM (SELECT id() as id, name FROM awards) AS award FULL JOIN people_awards ON award.id == people_awards.award_id WHERE people_awards.person_id == $1`
rs, _, err := db.Run(nil, stmt, personID)
if err != nil {
panic(err)
}
rows, err := rs[0].Rows(-1, 0)
if err != nil {
panic(err)
}
return len(rows)
}
func countJoinRecords(db *ql.DB) int64 {
stmt := `SELECT count() FROM people_awards`
rs, _, err := db.Run(nil, stmt)
if err != nil {
panic(err)
}
row, err := rs[0].FirstRow()
if err != nil {
panic(err)
}
return row[0].(int64)
}
func deletePersonAwards(db *ql.DB, personID int64) {
ctx := ql.NewRWCtx()
_, _, err := db.Run(ctx, `BEGIN TRANSACTION; DELETE FROM people_awards WHERE person_id == $1; COMMIT`, personID)
if err != nil {
panic(err)
}
if ctx.RowsAffected != 2 {
panic("Did not delete rows as expected")
}
}
If you comment out the call to createBuggyIndex
near the top of main
, the program completes successfully; when the index is created, the behavior of the FULL JOIN statement in countFullJoin
differs.
I'll admit that my SQL is a bit rusty and that I'm not sure which mode is the correct behavior, but I'm fairly certain that the behavior shouldn't change depending on whether an index has been created.