Oracle database raises ORA-00904 if group by clause uses alias name

[yahonda]

This issue is originally reported to rsim/oracle-enhanced#1899 .

Steps to reproduce

$ cd activerecord
$ ARCONN=oracle bin/test test/cases/calculations_test.rb -n test_group_by_with_limit

Expected behavior

It should pass as Rails 5-2-stable branch does.

Actual behavior

$ ARCONN=oracle bin/test test/cases/calculations_test.rb -n test_group_by_with_limit
Using oracle
Run options: -n test_group_by_with_limit --seed 6204

# Running:

E

Error:
CalculationsTest#test_group_by_with_limit:
ActiveRecord::StatementInvalid: OCIError: ORA-00904: "POSTS_TYPE": invalid identifier
    stmt.c:265:in oci8lib_260.so
    /home/yahonda/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/ruby-oci8-2.2.7/lib/oci8/cursor.rb:131:in `exec'
    /home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:142:in `exec'
    /home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:41:in `block in exec_query'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:692:in `block (2 levels) in log'
    /home/yahonda/.rbenv/versions/2.6.3/lib/ruby/2.6.0/monitor.rb:230:in `mon_synchronize'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:691:in `block in log'
    /home/yahonda/git/rails/activesupport/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:682:in `log'
    /home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/dbms_output.rb:35:in `log'
    /home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:24:in `exec_query'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:481:in `select'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:70:in `select_all'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:107:in `select_all'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation/calculations.rb:346:in `block in execute_grouped_calculation'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation.rb:839:in `skip_query_cache_if_necessary'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation/calculations.rb:346:in `execute_grouped_calculation'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation/calculations.rb:250:in `perform_calculation'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation/calculations.rb:142:in `calculate'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation/calculations.rb:140:in `calculate'
    /home/yahonda/git/rails/activerecord/lib/active_record/relation/calculations.rb:49:in `count'
    /home/yahonda/git/rails/activerecord/test/cases/calculations_test.rb:778:in `test_group_by_with_limit'


bin/test test/cases/calculations_test.rb:776



Finished in 9.255141s, 0.1080 runs/s, 0.0000 assertions/s.
1 runs, 0 assertions, 0 failures, 1 errors, 0 skips
$

System configuration

Rails version: master and 6-0-stable branch

Ruby version:ruby 2.6.3p62 (2019-04-16 revision 67580) [x86_64-linux]

[yahonda]

ORA-00904 is due to GROUP BY posts_type. Oracle database itself does not allow alias name at group by clause.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Creating-Simple-Queries.html#GUID-DB044D5C-A960-4813-84DA-A1880C913339

The alias can be used in the ORDER BY clause, but not other clauses in the query.

Here are SQL statement differences between master branch and 5-2-stable branch.
master and 6-0-stable branch use GROUP BY posts_type , 5-2-stable uses GROUP BY "POSTS"."TYPE"

• SQL statement executed using master and 6-0-stable which raises ORA-00904

SELECT COUNT(DISTINCT comments.id) AS count_comments_id, "POSTS"."TYPE" AS posts_type FROM "POSTS" LEFT OUTER JOIN "COMMENTS" ON "COMMENTS"."POST_ID" = "POSTS"."ID" GROUP BY posts_type FETCH FIRST :a1 ROWS ONLY  [["LIMIT", 2]]

• SQL statement executed using 5-2-stable which runs successfully.

SELECT  COUNT(DISTINCT comments.id) AS count_comments_id, "POSTS"."TYPE" AS posts_type FROM "POSTS" LEFT OUTER JOIN "COMMENTS" ON "COMMENTS"."POST_ID" = "POSTS"."ID" GROUP BY "POSTS"."TYPE" FETCH FIRST :a1 ROWS ONLY  [["LIMIT", 2]]

[yahonda]

It looks like this behavior change is introduced at c9e4c84