37 ms·

So whose bug was this? Did SQLite fail to round off a DECIMAL(n, 0) column, or did ActiveRecord make the scale value optional without guaranteeing that you alwa

So whose bug was this? Did SQLite fail to round off a DECIMAL(n, 0) column, or did ActiveRecord make the scale value optional without guaranteeing that you always get zero (or some other reliable value) by default?

nbpoole 15y ago
The developer's ;-) SQLite doesn't have strict types (see http://www.sqlite.org/datatype3.html http://www.sqlite.org/datatype3.html). MySQL does. It's not SQLite's fault or ActiveRecord's fault: it's one of the many issues you have to take into account when developing software that runs on two very different database systems. Edit: ActiveRecord specifically notes this behavior in the documentation: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html#method-i-column http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAd... Please be aware of different RDBMS implementations behavior with :decimal columns: The SQL standard says the default scale should be 0, :scale <= :precision, and makes no comments about the requirements of :precision. MySQL: :precision [1..63], :scale [0..30]. Default is (10,0). PostgreSQL: :precision [1..infinity], :scale [0..infinity]. No default. SQLite2: Any :precision and :scale may be used. Internal storage as strings. No default. SQLite3: No restrictions on :precision and :scale, but the maximum supported :precision is 16. No default. Oracle: :precision [1..38], :scale [-84..127]. Default is (38,0). DB2: :precision [1..63], :scale [0..62]. Default unknown. Firebird: :precision [1..18], :scale [0..18]. Default (9,0). Internal types NUMERIC and DECIMAL have different storage rules, decimal being better. FrontBase?: :precision [1..38], :scale [0..38]. Default (38,0). WARNING Max :precision/:scale for NUMERIC is 19, and DECIMAL is 38. SqlServer?: :precision [1..38], :scale [0..38]. Default (38,0). Sybase: :precision [1..38], :scale [0..38]. Default (38,0). OpenBase?: Documentation unclear. Claims storage in double.
- prodigal_erik 15y ago
  SQLite should reject DDL that specifies DECIMAL(n, 0) unless that type is implemented correctly. Until now I only expected MySQL to display reckless disregard for users' stated requirements in favor of guessing what they might want.

nbpoole 15y ago

The developer's ;-) SQLite doesn't have strict types (see http://www.sqlite.org/datatype3.html http://www.sqlite.org/datatype3.html). MySQL does. It's not SQLite's fault or ActiveRecord's fault: it's one of the many issues you have to take into account when developing software that runs on two very different database systems. Edit: ActiveRecord specifically notes this behavior in the documentation: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html#method-i-column http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAd... Please be aware of different RDBMS implementations behavior with :decimal columns: The SQL standard says the default scale should be 0, :scale <= :precision, and makes no comments about the requirements of :precision. MySQL: :precision [1..63], :scale [0..30]. Default is (10,0). PostgreSQL: :precision [1..infinity], :scale [0..infinity]. No default. SQLite2: Any :precision and :scale may be used. Internal storage as strings. No default. SQLite3: No restrictions on :precision and :scale, but the maximum supported :precision is 16. No default. Oracle: :precision [1..38], :scale [-84..127]. Default is (38,0). DB2: :precision [1..63], :scale [0..62]. Default unknown. Firebird: :precision [1..18], :scale [0..18]. Default (9,0). Internal types NUMERIC and DECIMAL have different storage rules, decimal being better. FrontBase?: :precision [1..38], :scale [0..38]. Default (38,0). WARNING Max :precision/:scale for NUMERIC is 19, and DECIMAL is 38. SqlServer?: :precision [1..38], :scale [0..38]. Default (38,0). Sybase: :precision [1..38], :scale [0..38]. Default (38,0). OpenBase?: Documentation unclear. Claims storage in double.

prodigal_erik 15y ago
SQLite should reject DDL that specifies DECIMAL(n, 0) unless that type is implemented correctly. Until now I only expected MySQL to display reckless disregard for users' stated requirements in favor of guessing what they might want.