adding Distinct ON node

8 files changed, 29 insertions, 36 deletions

diff --git a/lib/arel/nodes.rb b/lib/arel/nodes.rb
index cf2aeb2ddc..9576930a54 100644
--- a/lib/arel/nodes.rb
+++ b/lib/arel/nodes.rb
@@ -5,6 +5,10 @@ require 'arel/nodes/select_core'
 require 'arel/nodes/insert_statement'
 require 'arel/nodes/update_statement'
 
+# terminal
+
+require 'arel/nodes/terminal'
+
 # unary
 require 'arel/nodes/unary'
 require 'arel/nodes/unqualified_column'
diff --git a/lib/arel/nodes/terminal.rb b/lib/arel/nodes/terminal.rb
new file mode 100644
index 0000000000..c6b4f4e1e2
--- /dev/null
+++ b/lib/arel/nodes/terminal.rb
@@ -0,0 +1,6 @@
+module Arel
+  module Nodes
+    class Distinct < Arel::Nodes::Node
+    end
+  end
+end
diff --git a/lib/arel/nodes/unary.rb b/lib/arel/nodes/unary.rb
index e6e40e6b13..5c4add4792 100644
--- a/lib/arel/nodes/unary.rb
+++ b/lib/arel/nodes/unary.rb
@@ -20,14 +20,9 @@ module Arel
       On
       Top
       Lock
+      DistinctOn
     }.each do |name|
       const_set(name, Class.new(Unary))
     end
-
-    class Distinct < Unary
-      def initialize expr = nil
-        super
-      end
-    end
   end
 end
diff --git a/lib/arel/visitors/postgresql.rb b/lib/arel/visitors/postgresql.rb
index c423dc6fc6..377a65a216 100644
--- a/lib/arel/visitors/postgresql.rb
+++ b/lib/arel/visitors/postgresql.rb
@@ -6,25 +6,6 @@ module Arel
         visit o.expr
       end
 
-      def visit_Arel_Nodes_SelectStatement o
-        if !o.orders.empty? && using_distinct_on?(o)
-          subquery        = o.dup
-          subquery.orders = []
-          subquery.limit  = nil
-          subquery.offset = nil
-
-          sql = super(subquery)
-          [
-            "SELECT * FROM (#{sql}) AS id_list",
-            "ORDER BY #{aliased_orders(o.orders).join(', ')}",
-            (visit(o.limit) if o.limit),
-            (visit(o.offset) if o.offset),
-          ].compact.join ' '
-        else
-          super
-        end
-      end
-
       def visit_Arel_Nodes_Matches o
         "#{visit o.left} ILIKE #{visit o.right}"
       end
@@ -33,12 +14,8 @@ module Arel
         "#{visit o.left} NOT ILIKE #{visit o.right}"
       end
 
-      def using_distinct_on?(o)
-        o.cores.any? do |core|
-          core.projections.any? do |projection|
-            /DISTINCT ON/ === projection
-          end
-        end
+      def visit_Arel_Nodes_DistinctOn o
+        "DISTINCT ON ( #{visit o.expr} )"
       end
 
       def aliased_orders orders
diff --git a/lib/arel/visitors/to_sql.rb b/lib/arel/visitors/to_sql.rb
index 6aba31d94d..5319aeb418 100644
--- a/lib/arel/visitors/to_sql.rb
+++ b/lib/arel/visitors/to_sql.rb
@@ -126,8 +126,8 @@ key on UpdateManager using UpdateManager#key=
           "SELECT",
           (visit(o.top) if o.top),
           (visit(o.set_quantifier) if o.set_quantifier),
-          "#{o.projections.map { |x| visit x }.join ', '}",
-          visit(o.source),
+          ("#{o.projections.map { |x| visit x }.join ', '}" unless o.projections.empty?),
+          (visit(o.source) if o.source),
           ("WHERE #{o.wheres.map { |x| visit x }.join ' AND ' }" unless o.wheres.empty?),
           ("GROUP BY #{o.groups.map { |x| visit x }.join ', ' }" unless o.groups.empty?),
           (visit(o.having) if o.having),
diff --git a/test/test_select_manager.rb b/test/test_select_manager.rb
index 351d8d321b..8de1520b6f 100644
--- a/test/test_select_manager.rb
+++ b/test/test_select_manager.rb
@@ -54,7 +54,7 @@ module Arel
     def test_join_sources
       manager = Arel::SelectManager.new Table.engine
       manager.join_sources << Arel::Nodes::StringJoin.new('foo')
-      assert_equal "SELECT  FROM 'foo'", manager.to_sql
+      assert_equal "SELECT FROM 'foo'", manager.to_sql
     end
 
     describe 'backwards compatibility' do
diff --git a/test/visitors/test_oracle.rb b/test/visitors/test_oracle.rb
index 87b94409e3..eaf68013a7 100644
--- a/test/visitors/test_oracle.rb
+++ b/test/visitors/test_oracle.rb
@@ -101,7 +101,7 @@ module Arel
             sql.must_be_like %{
               SELECT * FROM (
                 SELECT raw_sql_.*, rownum raw_rnum_
-                FROM (SELECT ) raw_sql_
+                FROM (SELECT) raw_sql_
                 WHERE rownum <= 20
               )
               WHERE raw_rnum_ > 10
@@ -126,7 +126,7 @@ module Arel
             sql.must_be_like %{
               SELECT * FROM (
                 SELECT raw_sql_.*, rownum raw_rnum_
-                FROM (SELECT ) raw_sql_
+                FROM (SELECT) raw_sql_
               )
               WHERE raw_rnum_ > 10
             }
diff --git a/test/visitors/test_postgres.rb b/test/visitors/test_postgres.rb
index 74446c23ba..446eae0c4a 100644
--- a/test/visitors/test_postgres.rb
+++ b/test/visitors/test_postgres.rb
@@ -32,6 +32,17 @@ module Arel
         assert_equal 1, sql.scan(/LIMIT/).length, 'should have one limit'
       end
 
+      it 'should support DISTINCT ON' do
+        core = Arel::Nodes::SelectCore.new
+        core.set_quantifier = Arel::Nodes::DistinctOn.new(Arel.sql('aaron'))
+        assert_match 'DISTINCT ON ( aaron )', @visitor.accept(core)
+      end
+
+      it 'should support DISTINCT' do
+        core = Arel::Nodes::SelectCore.new
+        core.set_quantifier = Arel::Nodes::Distinct.new
+        assert_equal 'SELECT DISTINCT', @visitor.accept(core)
+      end
     end
   end
 end