Different type of data when comparing SQLite vs MySQL query result in Rails

Question

I'm looking for an explanation of the result of the following query with Active Record:

date_range = (Date.today - 7)..(Time.now.to_datetime)
r = Report.find(:all, :conditions => {:created_at => date_range},
                      :group => 'date(created_at)',
                      :select => 'date(created_at) as day, count(id) as counter')

Basically I'm just counting the results in a table "reports," but I got different types of values for the named field "counter", array of double quote strings vs array of only numbers.

MySQL

If using MySQL,

r.map(&:counter)

returns:
=> ["3", "3", "5", "4", "4"]

SQLite

If using SQLite,

r.map(&:counter)

returns:
=> [3, 3, 5, 4, 4]

Is it correct that MySQL returns the numbers with quotes (Strings) and SQLite numbers? I expected that both return just integers. Or am I missing some configuration with on MySQL side?

Edit:
Just in case, both DBs where created using normal migrations, so all fields are equivalent types.

I'm presuming that in the MySQL database it's stored as an integer rather than string? — joshhendo
– joshhendo, Commented Mar 1, 2011 at 4:54
Just in case I checked again, "id" is as usual integer in both cases. And count(id) isn't supposed to give you just a number...? — Christian
– Christian, Commented Mar 1, 2011 at 5:02

Winfield · Accepted Answer · 2011-03-01 14:12:57Z

1

This is a bug in the SQLite ActiveRecord adapter.

When you are synthesizing new column values using "SELECT expr AS col_name" col_name is cast to a String instead of the proper data type (int in this case).

See related Rails bug ticket: https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/4544-rails3-activerecord-sqlite3-lost-column-type-when-using-views#ticket-4544-2

Apparently SQLite can't return proper column type values for views, which is the mechanism used when you're synthesizing attributes like this.

answered Mar 1, 2011 at 14:12

Winfield

19.2k4 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2961443 · Accepted Answer · 2014-04-23 22:25:28Z

0

I've been having the same issue using the underlying connection to execute my SQL statements.

I have noticed, however, that if you are using binds in exec_sql statement, the data types come back as expected.

E.g.

ActiveRecord::Base.connection.exec_sql("select * from users where id = 35")

results in

ActiveRecord::Result:0x7fa2c6ede200 @columns=["id", "name"], @hash_rows=nil, @rows=[["35", "FOO"]]

However,

ActiveRecord::Base.connection.exec_sql("select * from users where id = ?", nil, [[nil,35]])

results in

ActiveRecord::Result:0x7fa2c6ede200 @columns=["id", "name"], @hash_rows=nil, @rows=[[35, "FOO"]]

Note that datatype of 35 is integer! Hope that helps.

answered Apr 23, 2014 at 22:25

user2961443

4095 silver badges9 bronze badges

Collectives™ on Stack Overflow

Different type of data when comparing SQLite vs MySQL query result in Rails

MySQL

SQLite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

MySQL

SQLite

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related