aboutsummaryrefslogtreecommitdiffstats
path: root/activerecord
diff options
context:
space:
mode:
authorDmytro Shteflyuk <kpumuk@kpumuk.info>2018-11-15 14:49:55 -0500
committerRafael França <rafaelmfranca@gmail.com>2018-11-15 14:49:55 -0500
commitb5302d5a820b078b6488104dd695a679e5a49623 (patch)
treea8f393036fd1b72c33d9ed448f6ada379b2dd429 /activerecord
parentd1c76dd4b0d308db432afb56c7dafcbefbf4ee3a (diff)
downloadrails-b5302d5a820b078b6488104dd695a679e5a49623.tar.gz
rails-b5302d5a820b078b6488104dd695a679e5a49623.tar.bz2
rails-b5302d5a820b078b6488104dd695a679e5a49623.zip
Arel: Implemented DB-aware NULL-safe comparison (#34451)
* Arel: Implemented DB-aware NULL-safe comparison * Fixed where clause inversion for NULL-safe comparison * Renaming "null_safe_eq" to "is_not_distinct_from", "null_safe_not_eq" to "is_distinct_from" [Dmytro Shteflyuk + Rafael Mendonça França]
Diffstat (limited to 'activerecord')
-rw-r--r--activerecord/lib/active_record/relation/where_clause.rb4
-rw-r--r--activerecord/lib/arel/nodes/equality.rb7
-rw-r--r--activerecord/lib/arel/predications.rb8
-rw-r--r--activerecord/lib/arel/visitors/depth_first.rb2
-rw-r--r--activerecord/lib/arel/visitors/dot.rb2
-rw-r--r--activerecord/lib/arel/visitors/ibm_db.rb6
-rw-r--r--activerecord/lib/arel/visitors/mssql.rb25
-rw-r--r--activerecord/lib/arel/visitors/mysql.rb11
-rw-r--r--activerecord/lib/arel/visitors/oracle.rb6
-rw-r--r--activerecord/lib/arel/visitors/oracle12.rb6
-rw-r--r--activerecord/lib/arel/visitors/postgresql.rb12
-rw-r--r--activerecord/lib/arel/visitors/sqlite.rb12
-rw-r--r--activerecord/lib/arel/visitors/to_sql.rb33
-rw-r--r--activerecord/test/cases/arel/visitors/ibm_db_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/informix_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/mssql_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/mysql_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/oracle12_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/oracle_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/postgres_test.rb39
-rw-r--r--activerecord/test/cases/arel/visitors/sqlite_test.rb45
-rw-r--r--activerecord/test/cases/arel/visitors/to_sql_test.rb37
-rw-r--r--activerecord/test/cases/relation/where_clause_test.rb4
23 files changed, 492 insertions, 1 deletions
diff --git a/activerecord/lib/active_record/relation/where_clause.rb b/activerecord/lib/active_record/relation/where_clause.rb
index a502713e56..e225628bae 100644
--- a/activerecord/lib/active_record/relation/where_clause.rb
+++ b/activerecord/lib/active_record/relation/where_clause.rb
@@ -125,6 +125,10 @@ module ActiveRecord
raise ArgumentError, "Invalid argument for .where.not(), got nil."
when Arel::Nodes::In
Arel::Nodes::NotIn.new(node.left, node.right)
+ when Arel::Nodes::IsNotDistinctFrom
+ Arel::Nodes::IsDistinctFrom.new(node.left, node.right)
+ when Arel::Nodes::IsDistinctFrom
+ Arel::Nodes::IsNotDistinctFrom.new(node.left, node.right)
when Arel::Nodes::Equality
Arel::Nodes::NotEqual.new(node.left, node.right)
when String
diff --git a/activerecord/lib/arel/nodes/equality.rb b/activerecord/lib/arel/nodes/equality.rb
index 2aa85a977e..551d56c2ff 100644
--- a/activerecord/lib/arel/nodes/equality.rb
+++ b/activerecord/lib/arel/nodes/equality.rb
@@ -7,5 +7,12 @@ module Arel # :nodoc: all
alias :operand1 :left
alias :operand2 :right
end
+
+ %w{
+ IsDistinctFrom
+ IsNotDistinctFrom
+ }.each do |name|
+ const_set name, Class.new(Equality)
+ end
end
end
diff --git a/activerecord/lib/arel/predications.rb b/activerecord/lib/arel/predications.rb
index e83a6f162f..77502dd199 100644
--- a/activerecord/lib/arel/predications.rb
+++ b/activerecord/lib/arel/predications.rb
@@ -18,6 +18,14 @@ module Arel # :nodoc: all
Nodes::Equality.new self, quoted_node(other)
end
+ def is_not_distinct_from(other)
+ Nodes::IsNotDistinctFrom.new self, quoted_node(other)
+ end
+
+ def is_distinct_from(other)
+ Nodes::IsDistinctFrom.new self, quoted_node(other)
+ end
+
def eq_any(others)
grouping_any :eq, others
end
diff --git a/activerecord/lib/arel/visitors/depth_first.rb b/activerecord/lib/arel/visitors/depth_first.rb
index 8f65d303ac..92d309453c 100644
--- a/activerecord/lib/arel/visitors/depth_first.rb
+++ b/activerecord/lib/arel/visitors/depth_first.rb
@@ -95,6 +95,8 @@ module Arel # :nodoc: all
alias :visit_Arel_Nodes_NotEqual :binary
alias :visit_Arel_Nodes_NotIn :binary
alias :visit_Arel_Nodes_NotRegexp :binary
+ alias :visit_Arel_Nodes_IsNotDistinctFrom :binary
+ alias :visit_Arel_Nodes_IsDistinctFrom :binary
alias :visit_Arel_Nodes_Or :binary
alias :visit_Arel_Nodes_OuterJoin :binary
alias :visit_Arel_Nodes_Regexp :binary
diff --git a/activerecord/lib/arel/visitors/dot.rb b/activerecord/lib/arel/visitors/dot.rb
index 9054f0159b..6389c875cb 100644
--- a/activerecord/lib/arel/visitors/dot.rb
+++ b/activerecord/lib/arel/visitors/dot.rb
@@ -195,6 +195,8 @@ module Arel # :nodoc: all
alias :visit_Arel_Nodes_JoinSource :binary
alias :visit_Arel_Nodes_LessThan :binary
alias :visit_Arel_Nodes_LessThanOrEqual :binary
+ alias :visit_Arel_Nodes_IsNotDistinctFrom :binary
+ alias :visit_Arel_Nodes_IsDistinctFrom :binary
alias :visit_Arel_Nodes_Matches :binary
alias :visit_Arel_Nodes_NotEqual :binary
alias :visit_Arel_Nodes_NotIn :binary
diff --git a/activerecord/lib/arel/visitors/ibm_db.rb b/activerecord/lib/arel/visitors/ibm_db.rb
index 0a06aef60b..73166054da 100644
--- a/activerecord/lib/arel/visitors/ibm_db.rb
+++ b/activerecord/lib/arel/visitors/ibm_db.rb
@@ -10,6 +10,12 @@ module Arel # :nodoc: all
collector = visit o.expr, collector
collector << " ROWS ONLY"
end
+
+ def is_distinct_from(o, collector)
+ collector << "DECODE("
+ collector = visit [o.left, o.right, 0, 1], collector
+ collector << ")"
+ end
end
end
end
diff --git a/activerecord/lib/arel/visitors/mssql.rb b/activerecord/lib/arel/visitors/mssql.rb
index d564e19089..fdd864b40d 100644
--- a/activerecord/lib/arel/visitors/mssql.rb
+++ b/activerecord/lib/arel/visitors/mssql.rb
@@ -12,6 +12,31 @@ module Arel # :nodoc: all
private
+ def visit_Arel_Nodes_IsNotDistinctFrom(o, collector)
+ right = o.right
+
+ if right.nil?
+ collector = visit o.left, collector
+ collector << " IS NULL"
+ else
+ collector << "EXISTS (VALUES ("
+ collector = visit o.left, collector
+ collector << ") INTERSECT VALUES ("
+ collector = visit right, collector
+ collector << "))"
+ end
+ end
+
+ def visit_Arel_Nodes_IsDistinctFrom(o, collector)
+ if o.right.nil?
+ collector = visit o.left, collector
+ collector << " IS NOT NULL"
+ else
+ collector << "NOT "
+ visit_Arel_Nodes_IsNotDistinctFrom o, collector
+ end
+ end
+
def visit_Arel_Visitors_MSSQL_RowNumber(o, collector)
collector << "ROW_NUMBER() OVER (ORDER BY "
inject_join(o.children, collector, ", ") << ") as _row_num"
diff --git a/activerecord/lib/arel/visitors/mysql.rb b/activerecord/lib/arel/visitors/mysql.rb
index 4e7b2456aa..dd77cfdf66 100644
--- a/activerecord/lib/arel/visitors/mysql.rb
+++ b/activerecord/lib/arel/visitors/mysql.rb
@@ -37,6 +37,17 @@ module Arel # :nodoc: all
collector
end
+ def visit_Arel_Nodes_IsNotDistinctFrom(o, collector)
+ collector = visit o.left, collector
+ collector << " <=> "
+ visit o.right, collector
+ end
+
+ def visit_Arel_Nodes_IsDistinctFrom(o, collector)
+ collector << "NOT "
+ visit_Arel_Nodes_IsNotDistinctFrom o, collector
+ end
+
# In the simple case, MySQL allows us to place JOINs directly into the UPDATE
# query. However, this does not allow for LIMIT, OFFSET and ORDER. To support
# these, we must use a subquery.
diff --git a/activerecord/lib/arel/visitors/oracle.rb b/activerecord/lib/arel/visitors/oracle.rb
index 30a1529d46..f96bf65ee5 100644
--- a/activerecord/lib/arel/visitors/oracle.rb
+++ b/activerecord/lib/arel/visitors/oracle.rb
@@ -148,6 +148,12 @@ module Arel # :nodoc: all
def visit_Arel_Nodes_BindParam(o, collector)
collector.add_bind(o.value) { |i| ":a#{i}" }
end
+
+ def is_distinct_from(o, collector)
+ collector << "DECODE("
+ collector = visit [o.left, o.right, 0, 1], collector
+ collector << ")"
+ end
end
end
end
diff --git a/activerecord/lib/arel/visitors/oracle12.rb b/activerecord/lib/arel/visitors/oracle12.rb
index 7061f06087..b092aa95e0 100644
--- a/activerecord/lib/arel/visitors/oracle12.rb
+++ b/activerecord/lib/arel/visitors/oracle12.rb
@@ -56,6 +56,12 @@ module Arel # :nodoc: all
def visit_Arel_Nodes_BindParam(o, collector)
collector.add_bind(o.value) { |i| ":a#{i}" }
end
+
+ def is_distinct_from(o, collector)
+ collector << "DECODE("
+ collector = visit [o.left, o.right, 0, 1], collector
+ collector << ")"
+ end
end
end
end
diff --git a/activerecord/lib/arel/visitors/postgresql.rb b/activerecord/lib/arel/visitors/postgresql.rb
index c5110fa89c..920776b4dc 100644
--- a/activerecord/lib/arel/visitors/postgresql.rb
+++ b/activerecord/lib/arel/visitors/postgresql.rb
@@ -77,6 +77,18 @@ module Arel # :nodoc: all
grouping_parentheses o, collector
end
+ def visit_Arel_Nodes_IsNotDistinctFrom(o, collector)
+ collector = visit o.left, collector
+ collector << " IS NOT DISTINCT FROM "
+ visit o.right, collector
+ end
+
+ def visit_Arel_Nodes_IsDistinctFrom(o, collector)
+ collector = visit o.left, collector
+ collector << " IS DISTINCT FROM "
+ visit o.right, collector
+ end
+
# Used by Lateral visitor to enclose select queries in parentheses
def grouping_parentheses(o, collector)
if o.expr.is_a? Nodes::SelectStatement
diff --git a/activerecord/lib/arel/visitors/sqlite.rb b/activerecord/lib/arel/visitors/sqlite.rb
index cb1d2424ad..af6f7e856a 100644
--- a/activerecord/lib/arel/visitors/sqlite.rb
+++ b/activerecord/lib/arel/visitors/sqlite.rb
@@ -22,6 +22,18 @@ module Arel # :nodoc: all
def visit_Arel_Nodes_False(o, collector)
collector << "0"
end
+
+ def visit_Arel_Nodes_IsNotDistinctFrom(o, collector)
+ collector = visit o.left, collector
+ collector << " IS "
+ visit o.right, collector
+ end
+
+ def visit_Arel_Nodes_IsDistinctFrom(o, collector)
+ collector = visit o.left, collector
+ collector << " IS NOT "
+ visit o.right, collector
+ end
end
end
end
diff --git a/activerecord/lib/arel/visitors/to_sql.rb b/activerecord/lib/arel/visitors/to_sql.rb
index 7efd74dbc9..f9fe4404eb 100644
--- a/activerecord/lib/arel/visitors/to_sql.rb
+++ b/activerecord/lib/arel/visitors/to_sql.rb
@@ -641,6 +641,26 @@ module Arel # :nodoc: all
end
end
+ def visit_Arel_Nodes_IsNotDistinctFrom(o, collector)
+ if o.right.nil?
+ collector = visit o.left, collector
+ collector << " IS NULL"
+ else
+ collector = is_distinct_from(o, collector)
+ collector << " = 0"
+ end
+ end
+
+ def visit_Arel_Nodes_IsDistinctFrom(o, collector)
+ if o.right.nil?
+ collector = visit o.left, collector
+ collector << " IS NOT NULL"
+ else
+ collector = is_distinct_from(o, collector)
+ collector << " = 1"
+ end
+ end
+
def visit_Arel_Nodes_NotEqual(o, collector)
right = o.right
@@ -873,6 +893,19 @@ module Arel # :nodoc: all
collector
end
end
+
+ def is_distinct_from(o, collector)
+ collector << "CASE WHEN "
+ collector = visit o.left, collector
+ collector << " = "
+ collector = visit o.right, collector
+ collector << " OR ("
+ collector = visit o.left, collector
+ collector << " IS NULL AND "
+ collector = visit o.right, collector
+ collector << " IS NULL)"
+ collector << " THEN 0 ELSE 1 END"
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/ibm_db_test.rb b/activerecord/test/cases/arel/visitors/ibm_db_test.rb
index 7163cb34d3..2ddbec3266 100644
--- a/activerecord/test/cases/arel/visitors/ibm_db_test.rb
+++ b/activerecord/test/cases/arel/visitors/ibm_db_test.rb
@@ -29,6 +29,45 @@ module Arel
sql = compile(stmt)
sql.must_be_like "UPDATE \"users\" WHERE \"users\".\"id\" IN (SELECT \"users\".\"id\" FROM \"users\" FETCH FIRST 1 ROWS ONLY)"
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ DECODE("users"."name", 'Aaron Patterson', 0, 1) = 0
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ DECODE("users"."first_name", "users"."last_name", 0, 1) = 0
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ DECODE("users"."first_name", "users"."last_name", 0, 1) = 1
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/informix_test.rb b/activerecord/test/cases/arel/visitors/informix_test.rb
index b0b031cca3..b6c2dd6ae7 100644
--- a/activerecord/test/cases/arel/visitors/informix_test.rb
+++ b/activerecord/test/cases/arel/visitors/informix_test.rb
@@ -54,6 +54,45 @@ module Arel
sql = compile(stmt)
sql.must_be_like 'SELECT FROM "posts" INNER JOIN "comments"'
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ CASE WHEN "users"."name" = 'Aaron Patterson' OR ("users"."name" IS NULL AND 'Aaron Patterson' IS NULL) THEN 0 ELSE 1 END = 0
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ CASE WHEN "users"."first_name" = "users"."last_name" OR ("users"."first_name" IS NULL AND "users"."last_name" IS NULL) THEN 0 ELSE 1 END = 0
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ CASE WHEN "users"."first_name" = "users"."last_name" OR ("users"."first_name" IS NULL AND "users"."last_name" IS NULL) THEN 0 ELSE 1 END = 1
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/mssql_test.rb b/activerecord/test/cases/arel/visitors/mssql_test.rb
index 340376c3d6..74f34b4dad 100644
--- a/activerecord/test/cases/arel/visitors/mssql_test.rb
+++ b/activerecord/test/cases/arel/visitors/mssql_test.rb
@@ -94,6 +94,45 @@ module Arel
sql = compile(stmt)
sql.must_be_like "SELECT COUNT(1) as count_id FROM (SELECT _t.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY ) as _row_num) as _t WHERE _row_num BETWEEN 1 AND 10) AS subquery"
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ EXISTS (VALUES ("users"."name") INTERSECT VALUES ('Aaron Patterson'))
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ EXISTS (VALUES ("users"."first_name") INTERSECT VALUES ("users"."last_name"))
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ NOT EXISTS (VALUES ("users"."first_name") INTERSECT VALUES ("users"."last_name"))
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/mysql_test.rb b/activerecord/test/cases/arel/visitors/mysql_test.rb
index dae98b0583..5f37587957 100644
--- a/activerecord/test/cases/arel/visitors/mysql_test.rb
+++ b/activerecord/test/cases/arel/visitors/mysql_test.rb
@@ -65,6 +65,45 @@ module Arel
}
end
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ "users"."name" <=> 'Aaron Patterson'
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ "users"."first_name" <=> "users"."last_name"
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" <=> NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ NOT "users"."first_name" <=> "users"."last_name"
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ NOT "users"."name" <=> NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/oracle12_test.rb b/activerecord/test/cases/arel/visitors/oracle12_test.rb
index 83a2ee36ca..4ce5cab4db 100644
--- a/activerecord/test/cases/arel/visitors/oracle12_test.rb
+++ b/activerecord/test/cases/arel/visitors/oracle12_test.rb
@@ -56,6 +56,45 @@ module Arel
}
end
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ DECODE("users"."name", 'Aaron Patterson', 0, 1) = 0
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ DECODE("users"."first_name", "users"."last_name", 0, 1) = 0
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ DECODE("users"."first_name", "users"."last_name", 0, 1) = 1
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/oracle_test.rb b/activerecord/test/cases/arel/visitors/oracle_test.rb
index e1dfe40cf9..893edc7f74 100644
--- a/activerecord/test/cases/arel/visitors/oracle_test.rb
+++ b/activerecord/test/cases/arel/visitors/oracle_test.rb
@@ -192,6 +192,45 @@ module Arel
}
end
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ DECODE("users"."name", 'Aaron Patterson', 0, 1) = 0
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ DECODE("users"."first_name", "users"."last_name", 0, 1) = 0
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ DECODE("users"."first_name", "users"."last_name", 0, 1) = 1
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/postgres_test.rb b/activerecord/test/cases/arel/visitors/postgres_test.rb
index f7f2c76b6f..0f9efb20b4 100644
--- a/activerecord/test/cases/arel/visitors/postgres_test.rb
+++ b/activerecord/test/cases/arel/visitors/postgres_test.rb
@@ -276,6 +276,45 @@ module Arel
}
end
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ "users"."name" IS NOT DISTINCT FROM 'Aaron Patterson'
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ "users"."first_name" IS NOT DISTINCT FROM "users"."last_name"
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT DISTINCT FROM NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ "users"."first_name" IS DISTINCT FROM "users"."last_name"
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS DISTINCT FROM NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/sqlite_test.rb b/activerecord/test/cases/arel/visitors/sqlite_test.rb
index 6650b6ff3a..ee4e07a675 100644
--- a/activerecord/test/cases/arel/visitors/sqlite_test.rb
+++ b/activerecord/test/cases/arel/visitors/sqlite_test.rb
@@ -6,7 +6,11 @@ module Arel
module Visitors
class SqliteTest < Arel::Spec
before do
- @visitor = SQLite.new Table.engine.connection_pool
+ @visitor = SQLite.new Table.engine.connection
+ end
+
+ def compile(node)
+ @visitor.accept(node, Collectors::SQLString.new).value
end
it "defaults limit to -1" do
@@ -27,6 +31,45 @@ module Arel
node = Nodes::False.new()
assert_equal "0", @visitor.accept(node, Collectors::SQLString.new).value
end
+
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ "users"."name" IS 'Aaron Patterson'
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ "users"."first_name" IS "users"."last_name"
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ "users"."first_name" IS NOT "users"."last_name"
+ }
+ end
+
+ it "should handle nil" do
+ @table = Table.new(:users)
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
end
end
end
diff --git a/activerecord/test/cases/arel/visitors/to_sql_test.rb b/activerecord/test/cases/arel/visitors/to_sql_test.rb
index 680c26a9ab..4bfa799a96 100644
--- a/activerecord/test/cases/arel/visitors/to_sql_test.rb
+++ b/activerecord/test/cases/arel/visitors/to_sql_test.rb
@@ -155,6 +155,43 @@ module Arel
end
end
+ describe "Nodes::IsNotDistinctFrom" do
+ it "should construct a valid generic SQL statement" do
+ test = Table.new(:users)[:name].is_not_distinct_from "Aaron Patterson"
+ compile(test).must_be_like %{
+ CASE WHEN "users"."name" = 'Aaron Patterson' OR ("users"."name" IS NULL AND 'Aaron Patterson' IS NULL) THEN 0 ELSE 1 END = 0
+ }
+ end
+
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_not_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ CASE WHEN "users"."first_name" = "users"."last_name" OR ("users"."first_name" IS NULL AND "users"."last_name" IS NULL) THEN 0 ELSE 1 END = 0
+ }
+ end
+
+ it "should handle nil" do
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsNotDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NULL }
+ end
+ end
+
+ describe "Nodes::IsDistinctFrom" do
+ it "should handle column names on both sides" do
+ test = Table.new(:users)[:first_name].is_distinct_from Table.new(:users)[:last_name]
+ compile(test).must_be_like %{
+ CASE WHEN "users"."first_name" = "users"."last_name" OR ("users"."first_name" IS NULL AND "users"."last_name" IS NULL) THEN 0 ELSE 1 END = 1
+ }
+ end
+
+ it "should handle nil" do
+ val = Nodes.build_quoted(nil, @table[:active])
+ sql = compile Nodes::IsDistinctFrom.new(@table[:name], val)
+ sql.must_be_like %{ "users"."name" IS NOT NULL }
+ end
+ end
+
it "should visit string subclass" do
[
Class.new(String).new(":'("),
diff --git a/activerecord/test/cases/relation/where_clause_test.rb b/activerecord/test/cases/relation/where_clause_test.rb
index 8703d238a0..0b06cec40b 100644
--- a/activerecord/test/cases/relation/where_clause_test.rb
+++ b/activerecord/test/cases/relation/where_clause_test.rb
@@ -92,12 +92,16 @@ class ActiveRecord::Relation
original = WhereClause.new([
table["id"].in([1, 2, 3]),
table["id"].eq(1),
+ table["id"].is_not_distinct_from(1),
+ table["id"].is_distinct_from(2),
"sql literal",
random_object
])
expected = WhereClause.new([
table["id"].not_in([1, 2, 3]),
table["id"].not_eq(1),
+ table["id"].is_distinct_from(1),
+ table["id"].is_not_distinct_from(2),
Arel::Nodes::Not.new(Arel::Nodes::SqlLiteral.new("sql literal")),
Arel::Nodes::Not.new(random_object)
])