jeremyevans/sequel — Issue #410: class_table_inheritance plugin: unable to save grandchild due to ambiguous column
Author: yb66
Created: 2011-12-15 14:07:14+00:00
State: open

Hi,

This is for Sequel version 3.30.0 with a Sqlite3 v3.6.12 database on Ruby 1.9.2-p290 built via RVM.

We spoke the other day on the mailing list about this plugin. I didn't notice this problem initially because I added some data via migrations, but on using the classes to `create` or `save` a *new* model instance that is a grandchild subclass, an error message is thrown.

> Sequel::DatabaseError: SQLite3::SQLException: ambiguous column name: id

I checked in the sqlite console and it's the where clause that's the problem:

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from employees inner join managers using( id ) inner join executives using( id );
4|Mr T|Executive|3|1
sqlite> select * from employees inner join managers using( id ) inner join executives using( id ) where ( id = 4 ) limit 1;
SQL error: ambiguous column name: id
sqlite> select * from employees inner join managers using( id ) inner join executives using( id ) where ( employees.id = 4 ) limit 1;
4|Mr T|Executive|3|1
sqlite> .quit

Error output from a sequel console session with logging:

Executive.new name: "Billy"
=> #"Billy"}>
_.save
I, [Thu 15-12-2011 1316 #69348] INFO -- : (0.000116s) BEGIN
I, [Thu 15-12-2011 1316 #69348] INFO -- : (0.001541s) INSERT INTO `employees` (`name`, `kind`) VALUES ('Billy', 'Executive')
I, [Thu 15-12-2011 1316 #69348] INFO -- : (0.000186s) INSERT INTO `managers` (`id`) VALUES (9)
I, [Thu 15-12-2011 1316 #69348] INFO -- : (0.000233s) INSERT INTO `executives` (`id`) VALUES (9)
E, [Thu 15-12-2011 1316 #69348] ERROR -- : SQLite3::SQLException: ambiguous column name: id: SELECT * FROM `employees` INNER JOIN `managers` USING (`id`) INNER JOIN `executives` USING (`id`) WHERE (`id` = 9) LIMIT 1
I, [Thu 15-12-2011 1316 #69348] INFO -- : (0.034387s) ROLLBACK
Sequel::DatabaseError: SQLite3::SQLException: ambiguous column name: id
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:in `initialize'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:in `new'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:in `prepare'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:263:in `query'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/adapters/sqlite.rb:124:in `block (2 levels) in _execute'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/database/logging.rb:32:in `log_yield'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/adapters/sqlite.rb:124:in `block in _execute'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/connection_pool/threaded.rb:71:in `hold'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/database/connecting.rb:225:in `synchronize'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/adapters/sqlite.rb:117:in `_execute'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/adapters/sqlite.rb:82:in `execute'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/dataset/actions.rb:646:in `execute'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/adapters/sqlite.rb:296:in `fetch_rows'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/dataset/actions.rb:133:in `each'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/dataset/actions.rb:490:in `single_record'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/dataset/actions.rb:188:in `first'
... 4 levels...
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1474:in `_save'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1158:in `block (2 levels) in save'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1578:in `block in checked_transaction'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/database/query.rb:259:in `_transaction'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/database/query.rb:228:in `block in transaction'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/connection_pool/threaded.rb:84:in `hold'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/database/connecting.rb:225:in `synchronize'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/database/query.rb:226:in `transaction'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1578:in `checked_transaction'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1158:in `block in save'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1566:in `checked_save_failure'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/lib/sequel/model/base.rb:1158:in `save'
from (irb):9
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/gems/sequel-3.30.0/bin/sequel:204:in `'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/bin/sequel:19:in `load'
from /Users/iainuser/.rvm/gems/ruby-1.9.2-p290@test/bin/sequel:19:in `

'

Here is some from the instances added via the migrations. As you can see, existing data can be updated regardless of class, and new instances of child sub-classes can be created, but grandchild subclasses (Executive) cannot be saved:

I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.001400s) PRAGMA table_info('employees')
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000342s) SELECT * FROM `staff` LIMIT 1
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000333s) SELECT * FROM `employees` INNER JOIN `staff` USING (`id`) LIMIT 1
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000238s) PRAGMA table_info('staff')
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000315s) SELECT * FROM `managers` LIMIT 1
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000334s) SELECT * FROM `employees` INNER JOIN `managers` USING (`id`) LIMIT 1
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000272s) PRAGMA table_info('managers')
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000302s) SELECT * FROM `executives` LIMIT 1
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000442s) SELECT * FROM `employees` INNER JOIN `managers` USING (`id`) INNER JOIN `executives` USING (`id`) LIMIT 1
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000327s) PRAGMA table_info('executives')
Your database is stored in DB...
sue = Manager.first
I, [Thu 15-12-2011 1313 #69348] INFO -- : (0.000548s) SELECT * FROM `employees` INNER JOIN `managers` USING (`id`) LIMIT 1
=> #3, :name=>"Sue", :kind=>"Manager", :num_staff=>2}>
sue.num_staff = 3
=> 3
sue.save
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000331s) SELECT sqlite_version() LIMIT 1
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000101s) BEGIN
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.001016s) UPDATE `employees` SET `name` = 'Sue', `kind` = 'Manager' WHERE (`id` = 3)
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000193s) UPDATE `managers` SET `num_staff` = 3 WHERE (`id` = 3)
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000976s) COMMIT
=> #3, :name=>"Sue", :kind=>"Manager", :num_staff=>3}>
mr_t = Executive.first
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000639s) SELECT * FROM `employees` INNER JOIN `managers` USING (`id`) INNER JOIN `executives` USING (`id`) LIMIT 1
=> #4, :name=>"Mr T", :kind=>"Executive", :num_staff=>3, :num_managers=>1}>
mr_t.num_staff
=> 3
mr_t.num_staff = 4
=> 4
mr_t.save
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000109s) BEGIN
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.001349s) UPDATE `employees` SET `name` = 'Mr T', `kind` = 'Executive' WHERE (`id` = 4)
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000302s) UPDATE `managers` SET `num_staff` = 4 WHERE (`id` = 4)
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.000357s) UPDATE `executives` SET `num_managers` = 1 WHERE (`id` = 4)
I, [Thu 15-12-2011 1314 #69348] INFO -- : (0.001357s) COMMIT
=> #4, :name=>"Mr T", :kind=>"Executive", :num_staff=>4, :num_managers=>1}>
mr_t.name
=> "Mr T"
mr_t.name = "Mr. T"
=> "Mr. T"
mr_t.save
I, [Thu 15-12-2011 1323 #69348] INFO -- : (0.000095s) BEGIN
I, [Thu 15-12-2011 1323 #69348] INFO -- : (0.001406s) UPDATE `employees` SET `name` = 'Mr. T', `kind` = 'Executive' WHERE (`id` = 4)
I, [Thu 15-12-2011 1323 #69348] INFO -- : (0.000327s) UPDATE `managers` SET `num_staff` = 4 WHERE (`id` = 4)
I, [Thu 15-12-2011 1323 #69348] INFO -- : (0.000571s) UPDATE `executives` SET `num_managers` = 1 WHERE (`id` = 4)
I, [Thu 15-12-2011 1323 #69348] INFO -- : (0.028869s) COMMIT
=> #4, :name=>"Mr. T", :kind=>"Executive", :num_staff=>4, :num_managers=>1}>
Manager.create name: "Darcy", num_staff: 1I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000100s) BEGINI, [Thu 15-12-2011 1324 #69348] INFO -- : (0.001130s) INSERT INTO `employees` (`name`, `kind`) VALUES ('Darcy', 'Manager')
I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000142s) INSERT INTO `managers` (`id`, `num_staff`) VALUES (9, 1)
I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000249s) SELECT * FROM `employees` INNER JOIN `managers` USING (`id`) WHERE (`id` = 9) LIMIT 1
I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.041085s) COMMIT
=> #9, :name=>"Darcy", :kind=>"Manager", :num_staff=>1}>
Staff.create name: "James", manager_id: 7I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000107s) BEGINI, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000559s) INSERT INTO `employees` (`name`, `kind`) VALUES ('James', 'Staff')
I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000166s) INSERT INTO `staff` (`id`, `manager_id`) VALUES (10, 7)
I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000308s) SELECT * FROM `employees` INNER JOIN `staff` USING (`id`) WHERE (`id` = 10) LIMIT 1
I, [Thu 15-12-2011 1324 #69348] INFO -- : (0.000877s) COMMIT
=> #10, :name=>"James", :kind=>"Staff", :manager_id=>7}>

Here are the model and schema I've used, I tried the schema with and without foreign keys to see if it made a difference but it didn't, and also with and without associations on the models:

employee.rb

# encoding: UTF-8

require "sequel"

class Employee < Sequel::Model(DB[:employees])
plugin :class_table_inheritance, :key=>:kind, :table_map=>{ :Staff=>:staff,:Manager=>:managers,:Executive=>:executives}
end

class Staff < Employee
many_to_one :manager
end

class Manager < Employee
one_to_many :staff_members, :class=>:Staff
end

class Executive < Manager
end

001_cti.rb

Sequel.migration do
up do
create_table(:employees) do
primary_key :id, type: Integer, null: false
String :name, size: 250, null: false
String :kind, size: 100
end

create_table(:staff) do
foreign_key :id, :employees
Integer :manager_id
end

create_table(:managers) do
foreign_key :id, :employees
Integer :num_staff
end

create_table(:executives) do
foreign_key :id, :employees
Integer :num_managers
end

DB[:employees].multi_insert([
{name: "Bob", kind: "Staff"},
{name: "Rita", kind: "Staff"},
{name: "Sue", kind: "Manager"},
{name: "Mr T", kind: "Executive"}
])

DB[:staff].multi_insert([
{id: 1, manager_id: 3},
{id: 2, manager_id: 3}
])

DB[:managers].multi_insert([
{id: 3, num_staff: 2},
{id: 4, num_staff: 3}
])

DB[:executives].multi_insert([
{id: 4, num_managers: 1}
])
end # up

down do
drop_table(:executives, :managers, :staff, :employees)
end # down
end # migration

I've taken a look in the code, but I can't work out yet where the call to `self` at the end of `Model#_save` on line 1490 in base.rb becomes the building of the select statement. I also saw that the specs for this plugin aren't testing for this, and as soon as I can I'll try and come up with something to at least fail in this kind of event.

Do let me know if there's something I've left out or need to try.

Regards,
Iain