This project is archived and is in readonly mode.

#4528 ✓stale
Richard Bunch

SQLiteAdapter::select_rows misses columns when column names are ambiguous

Reported by Richard Bunch | May 4th, 2010 @ 01:06 AM

When select_rows is used on a SQLite database and the selected columns include some with the same name in different tables, the last of the columns selected (not necessarily the ambiguous ones) will not appear in the result rows. The bug can be worked around by giving unambiguous aliases to the columns selected. I was using ruby 1.8.7 and rails 2.3.5.

An example follows.

config/schema.rb

ActiveRecord::Schema.define(:version => 20100503193703) do
  create_table "dogs", :force => true do |t|
    t.string   "name"
    t.string   "color"
    t.integer  "owner_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "people", :force => true do |t|
    t.string   "name"
    t.datetime "created_at"
    t.datetime "updated_at"
  end
end

app/models/dog.rb

class Dog < ActiveRecord::Base
  belongs_to :person
end

app/models/person.rb

class Person < ActiveRecord::Base
  has_many :dog
end

db/seeds.rb

bob = Person.create(:name => 'Bob')
Dog.create(:name => 'Fido', :owner_id => bob.id, :color => 'brown')

app/controllers/home_controller.rb

require 'pp'
class HomeController < ApplicationController
  def index
    @dog_show = ActiveRecord::Base::connection.select_rows 'SELECT people.name, dogs.name, dogs.color FROM people INNER JOIN dogs ON dogs.owner_id = people.id'
    pp @dog_show #=> [["Bob", "Fido"]]

    @dog_show = ActiveRecord::Base::connection.select_rows 'SELECT people.name, dogs.name as dogname, dogs.color FROM people INNER JOIN dogs ON dogs.owner_id = people.id'
    pp @dog_show #=> [["Bob", "Fido", "brown"]]
  end
end

The bug appears to be due to the implementation of select_rows in activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb

def select_rows(sql, name = nil)
  execute(sql, name).map do |row|
    (0...(row.size / 2)).map { |i| row[i] }
  end
end

With the ambiguous column names in the example, the value returned by execute is:

[{"name"=>"Fido", 0=>"Bob", 1=>"Fido", 2=>"brown", "color"=>"brown"}]

Due to the ambiguity, there is no "name"=>"Bob" pair, so the number of columns is not row.size/2 as is assumed.

The following monkey patch seemed to fix the problem, but I have not tested it properly and it seems that the code could be more "rubyish".

module ActiveRecord
  module ConnectionAdapters
    class SQLiteAdapter
        def select_rows(sql, name = nil)
          rows = execute(sql, name)
          pp rows
          rows.map do |row|
            (0...(row.size / 2)).map { |i| row[i] }
          end
        end
    end
  end
end

I have not tested the bug with other databases, but their code for select_rows looks totally different.

Comments and changes to this ticket

  • Neeraj Singh

    Neeraj Singh May 4th, 2010 @ 03:50 PM

    • Tag set to “rails3, sqlite”

    I have confirmed that it is indeed an issue with SQLite database. It works fine with MySQL.

    Will be looking into the difference between how MySQL handles it and how SQLite handles the query.

    I did my check on rails3 edge.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:43 PM

    • State changed from “new” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:43 PM

    • State changed from “open” to “stale”
  • Richard Bunch

    Richard Bunch February 6th, 2011 @ 09:34 PM

    • State changed from “stale” to “open”
    • Tag changed from “rails3, sqlite” to “bug sqlite”

    I have tested and this bug is still present with rails 3.0.3, ruby-1.9.2-p136, sqlite 3.7.5, Windows XP. Looking at the latest code on the git browser, it looks like the bug is still in there.

    I notice my "monkey patch" is nothing but a copy of the original code with debug printing. I clearly copied the wrong code. It should be:

    module ActiveRecord
  module ConnectionAdapters
    

    


    

    class SQLiteAdapter
  def select_rows(sql, name = nil)
    execute(sql, name).map do |row|
      new_row = []
      for i in (0...row.size)
        if row[i] then new_row &lt;&lt; row[i] else break end
      end
      new_row
    end
  end
end

    


    
  end
end

    I think making this change in the original sqlite_adapter.rb should solve the problem. I will try to make a patch, if I have time.

    [state:open]

  • Richard Bunch

    Richard Bunch February 6th, 2011 @ 09:35 PM

    • Tag changed from “bug sqlite” to “rails3 sqlite bug”
  • Richard Bunch

    Richard Bunch February 6th, 2011 @ 09:38 PM

    • Tag changed from “rails3 sqlite bug” to “rails3, rails3.0.3, sqlite_adapter”

    Sorry, my script blocker messed up the tags.

  • rails

    rails May 7th, 2011 @ 01:00 AM

    • Tag changed from “rails3, rails3.0.3, sqlite_adapter” to “rails3, rails303, sqlite_adapter”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • rails

    rails May 7th, 2011 @ 01:00 AM

    • State changed from “open” to “stale”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Tags

Pages