search

rails / arel

A Relational Algebra
2.06k stars 390 forks source link

ActiveRecord::StatementInvalid: OCIError: ORA-00904: when Model.order("MixedCase") #453

Closed yahonda closed 6 years ago

yahonda commented 7 years ago

This issue is originally reported at rsim/oracle-enhanced#1029

When the column name is mixed case, i.e. "SortIndex", Post.order("SortIndex").first gets ActiveRecord::StatementInvalid: OCIError: ORA-00904

begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'rails', '= 5.0.0.1'
  gem 'activerecord', '= 5.0.0.1'
  gem 'activerecord-oracle_enhanced-adapter', '~> 1.7.0'
  gem 'ruby-oci8'
  gem 'minitest'
  gem 'byebug'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'
require 'active_record/connection_adapters/oracle_enhanced_adapter'
require 'byebug'

# Ensure backward compatibility with Minitest 4
Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

# Set Oracle enhanced adapter specific connection parameters
DATABASE_NAME = ENV['DATABASE_NAME'] || 'orcl'
DATABASE_HOST = ENV['DATABASE_HOST']
DATABASE_PORT = ENV['DATABASE_PORT']
DATABASE_USER = ENV['DATABASE_USER'] || 'oracle_enhanced'
DATABASE_PASSWORD = ENV['DATABASE_PASSWORD'] || 'oracle_enhanced'
DATABASE_SYS_PASSWORD = ENV['DATABASE_SYS_PASSWORD'] || 'admin'

CONNECTION_PARAMS = {
  :adapter => "oracle_enhanced",
  :database => DATABASE_NAME,
  :host => DATABASE_HOST,
  :port => DATABASE_PORT,
  :username => DATABASE_USER,
  :password => DATABASE_PASSWORD
}

#ActiveRecord::Base.default_timezone = :local
ActiveRecord::Base.default_timezone = :utc
ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true

ActiveRecord::Schema.define do
  create_table :posts, :force => true do |t|
    t.string  "name",                       null: false
    t.integer "SortIndex"
  end
end

class Post < ActiveRecord::Base
end

class BugTest < Minitest::Test
  def test_post_order_by_mixed_case_column_name
    Post.create(name: 'Test 1', "SortIndex" => 200)
    Post.create(name: 'Test 2', "SortIndex" => 100)
    p = Post.order("SortIndex").first
    assert_equal p, Post.last
  end
end
$ ruby rep3.rb
Fetching gem metadata from https://rubygems.org/..........
Fetching version metadata from https://rubygems.org/..
Fetching dependency metadata from https://rubygems.org/.
Resolving dependencies...
Using rake 11.3.0
Using concurrent-ruby 1.0.2
Using i18n 0.7.0
Using minitest 5.9.1
Using thread_safe 0.3.5
Using builder 3.2.2
Using erubis 2.7.0
Using mini_portile2 2.1.0
Using rack 2.0.1
Using nio4r 1.2.1
Using websocket-extensions 0.1.2
Using mime-types-data 3.2016.0521
Using arel 7.1.4
Using ruby-plsql 0.6.0
Using bundler 1.13.6
Using byebug 9.0.6
Using method_source 0.8.2
Using thor 0.19.1
Using ruby-oci8 2.2.2
Using tzinfo 1.2.2
Using nokogiri 1.6.8.1
Using rack-test 0.6.3
Using sprockets 3.7.0
Using websocket-driver 0.6.4
Using mime-types 3.1
Using activesupport 5.0.0.1
Using loofah 2.0.3
Using mail 2.6.4
Using rails-dom-testing 2.0.1
Using globalid 0.3.7
Using activemodel 5.0.0.1
Using rails-html-sanitizer 1.0.3
Using activejob 5.0.0.1
Using activerecord 5.0.0.1
Using actionview 5.0.0.1
Using activerecord-oracle_enhanced-adapter 1.7.4
Using actionpack 5.0.0.1
Using actioncable 5.0.0.1
Using actionmailer 5.0.0.1
Using railties 5.0.0.1
Using sprockets-rails 3.2.0
Using rails 5.0.0.1
-- create_table(:posts, {:force=>true})
D, [2016-11-01T15:23:22.600988 #8288] DEBUG -- :    (38.2ms)  DROP TABLE "POSTS"
D, [2016-11-01T15:23:22.609945 #8288] DEBUG -- :    (8.0ms)  DROP SEQUENCE "POSTS_SEQ"
D, [2016-11-01T15:23:22.633175 #8288] DEBUG -- :    (22.2ms)  CREATE TABLE "POSTS" ("ID" NUMBER(38) NOT NULL PRIMARY KEY, "NAME" VARCHAR2(255) NOT NULL, "SortIndex" NUMBER(38))
D, [2016-11-01T15:23:22.649373 #8288] DEBUG -- :    (10.3ms)  CREATE SEQUENCE "POSTS_SEQ" START WITH 10000
   -> 0.1554s
D, [2016-11-01T15:23:24.783196 #8288] DEBUG -- :    (8.9ms)  SELECT cols.column_name AS name, cols.data_type AS sql_type, cols.data_default, cols.nullable, cols.virtual_column, cols.hidden_column, cols.data_type_owner AS sql_type_owner, DECODE(cols.data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale, comments.comments as column_comment FROM all_tab_cols cols, all_col_comments comments WHERE cols.owner = 'ORACLE_ENHANCED' AND cols.table_name = 'AR_INTERNAL_METADATA' AND cols.hidden_column = 'NO' AND cols.owner = comments.owner AND cols.table_name = comments.table_name AND cols.column_name = comments.column_name ORDER BY cols.column_id
D, [2016-11-01T15:23:24.795426 #8288] DEBUG -- :   ActiveRecord::InternalMetadata Load (3.0ms)  SELECT  "AR_INTERNAL_METADATA".* FROM "AR_INTERNAL_METADATA" WHERE "AR_INTERNAL_METADATA"."KEY" = :a1 FETCH FIRST :a2 ROWS ONLY  [["key", :environment], ["LIMIT", 1]]
Run options: --seed 6318

# Running:

D, [2016-11-01T15:23:24.824651 #8288] DEBUG -- :    (4.4ms)  SELECT cols.column_name AS name, cols.data_type AS sql_type, cols.data_default, cols.nullable, cols.virtual_column, cols.hidden_column, cols.data_type_owner AS sql_type_owner, DECODE(cols.data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale, comments.comments as column_comment FROM all_tab_cols cols, all_col_comments comments WHERE cols.owner = 'ORACLE_ENHANCED' AND cols.table_name = 'POSTS' AND cols.hidden_column = 'NO' AND cols.owner = comments.owner AND cols.table_name = comments.table_name AND cols.column_name = comments.column_name ORDER BY cols.column_id
D, [2016-11-01T15:23:24.905420 #8288] DEBUG -- :   Sequence (1.3ms)  select us.sequence_name from all_sequences us where us.sequence_owner = 'ORACLE_ENHANCED' and us.sequence_name = 'POSTS_SEQ'
D, [2016-11-01T15:23:24.908863 #8288] DEBUG -- :   Primary Key (3.0ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'ORACLE_ENHANCED' AND c.table_name = 'POSTS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2016-11-01T15:23:24.918810 #8288] DEBUG -- :   Sequence (1.0ms)  select us.sequence_name from all_sequences us where us.sequence_owner = 'ORACLE_ENHANCED' and us.sequence_name = 'POSTS_SEQ'
D, [2016-11-01T15:23:24.921189 #8288] DEBUG -- :   Primary Key (2.0ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'ORACLE_ENHANCED' AND c.table_name = 'POSTS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2016-11-01T15:23:24.922708 #8288] DEBUG -- :   Primary Key Trigger (1.3ms)            SELECT trigger_name
          FROM all_triggers
          WHERE owner = 'ORACLE_ENHANCED'
            AND trigger_name = 'POSTS_PKT'
            AND table_owner = 'ORACLE_ENHANCED'
            AND table_name = 'POSTS'
            AND status = 'ENABLED'

D, [2016-11-01T15:23:24.942528 #8288] DEBUG -- :   SQL (12.0ms)  INSERT INTO "POSTS" ("NAME", "SortIndex", "ID") VALUES (:a1, :a2, :a3)  [["name", "Test 1"], ["SortIndex", 200], ["id", 10000]]
D, [2016-11-01T15:23:24.948578 #8288] DEBUG -- :   SQL (0.8ms)  INSERT INTO "POSTS" ("NAME", "SortIndex", "ID") VALUES (:a1, :a2, :a3)  [["name", "Test 2"], ["SortIndex", 100], ["id", 10001]]
D, [2016-11-01T15:23:24.955114 #8288] DEBUG -- :   Post Load (3.6ms)  SELECT  "POSTS".* FROM "POSTS" ORDER BY SortIndex FETCH FIRST :a1 ROWS ONLY  [["LIMIT", 1]]
E

Finished in 0.139524s, 7.1672 runs/s, 0.0000 assertions/s.

  1) Error:
BugTest#test_post_order_by_mixed_case_column_name:
ActiveRecord::StatementInvalid: OCIError: ORA-00904: "SORTINDEX": invalid identifier: SELECT  "POSTS".* FROM "POSTS" ORDER BY SortIndex FETCH FIRST :a1 ROWS ONLY
    stmt.c:243:in oci8lib_230.so
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/ruby-oci8-2.2.2/lib/oci8/cursor.rb:126:in `exec'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.7.4/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:164:in `exec'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.7.4/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:39:in `block in exec_query'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activesupport-5.0.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.7.4/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1266:in `log'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.7.4/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:22:in `exec_query'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:373:in `select'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:41:in `select_all'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/querying.rb:39:in `find_by_sql'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation.rb:699:in `exec_queries'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation.rb:580:in `load'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation.rb:260:in `records'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation.rb:256:in `to_a'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation/finder_methods.rb:561:in `find_nth_with_limit'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation/finder_methods.rb:590:in `find_nth_with_limit_and_offset'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation/finder_methods.rb:543:in `find_nth'
    /home/yahonda/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/relation/finder_methods.rb:122:in `first'
    rep3.rb:65:in `test_post_order_by_mixed_case_column_name'

1 runs, 0 assertions, 0 failures, 1 errors, 0 skips
$

Oracle database identifier is upper case by default. If users would like to have case sensitive one, they need to quote. Oracle enhanced adapter supports case-sensitive identifier, such as table name, column name. Then I've found order by clause is created by Arel

https://github.com/rails/arel/blob/7-1-stable/lib/arel/visitors/to_sql.rb#L219-L226

        unless o.orders.empty?
          collector << ORDER_BY
          len = o.orders.length - 1
          o.orders.each_with_index { |x, i|
            collector = visit(x, collector)
            collector << COMMA unless len == i
          }
        end

Environment

matthewd commented 6 years ago

Per #523, Arel development is moving to rails/rails.

If this issue is still relevant, please consider reopening it over there. (Note that the Rails repository does not accept feature request issues, and requires reproduction steps that rely on Active Record's documented API.)