FIX: Incorrect DB query for `AddUploadsToCategories` migration.

FIX: Incorrect DB query for AddUploadsToCategories migration.

This is a regression as a result of 7896c74c2bd161f85c976fcd8468797721df30d9. Most instances would have ran the migrations and some might have run this migration with the incorrect query. Impact of this is small for now but I’m fixing this for correctness purposes.

diff --git a/db/migrate/20161202034856_add_uploads_to_categories.rb b/db/migrate/20161202034856_add_uploads_to_categories.rb
index 7aa3b64..92522f7 100644
--- a/db/migrate/20161202034856_add_uploads_to_categories.rb
+++ b/db/migrate/20161202034856_add_uploads_to_categories.rb
@@ -4,19 +4,19 @@ class AddUploadsToCategories < ActiveRecord::Migration[4.2]
     add_column :categories, :uploaded_background_id, :integer, index: true
 
     execute <<~SQL
-    UPDATE categories
+    UPDATE categories c1
     SET uploaded_logo_id = u.id
-    FROM categories c
-    LEFT JOIN uploads u ON u.url = c.logo_url
-    WHERE u.url IS NOT NULL
+    FROM categories c2
+    INNER JOIN uploads u ON u.url = c2.logo_url
+    WHERE c1.id = c2.id
     SQL
 
     execute <<~SQL
-    UPDATE categories
+    UPDATE categories c1
     SET uploaded_background_id = u.id
-    FROM categories c
-    LEFT JOIN uploads u ON u.url = c.background_url
-    WHERE u.url IS NOT NULL
+    FROM categories c2
+    INNER JOIN uploads u ON u.url = c2.background_url
+    WHERE c1.id = c2.id
     SQL
   end
 end
diff --git a/spec/db/migrate/add_uploads_to_categories_spec.rb b/spec/db/migrate/add_uploads_to_categories_spec.rb
new file mode 100644
index 0000000..b349a31
--- /dev/null
+++ b/spec/db/migrate/add_uploads_to_categories_spec.rb
@@ -0,0 +1,44 @@
+require 'rails_helper'
+require 'migration/column_dropper'
+require_relative '../../../db/migrate/20161202034856_add_uploads_to_categories'
+
+RSpec.describe AddUploadsToCategories do
+  before do
+    %i{logo_url background_url}.each do |column|
+      DB.exec("ALTER TABLE categories ADD COLUMN #{column} VARCHAR;")
+    end
+
+    %i{uploaded_logo_id uploaded_background_id}.each do |column|
+      DB.exec("ALTER TABLE categories DROP COLUMN IF EXISTS #{column}")
+    end
+  end
+
+  it "should migrate the data properly" do
+    upload1 = Fabricate(:upload)
+    upload2 = Fabricate(:upload)
+
+    category1 = Fabricate(:category,
+      logo_url: upload1.url,
+      background_url: upload2.url
+    )
+
+    category2 = Fabricate(:category,
+      logo_url: upload2.url,
+      background_url: upload1.url
+    )
+
+    silence_stdout { described_class.new.up }
+
+    Discourse.reset_active_record_cache
+
+    category1.reload
+
+    expect(category1.uploaded_logo_id).to eq(upload1.id)
+    expect(category1.uploaded_background_id).to eq(upload2.id)
+
+    category2.reload
+
+    expect(category2.uploaded_logo_id).to eq(upload2.id)
+    expect(category2.uploaded_background_id).to eq(upload1.id)
+  end
+end

GitHub sha: c9f6beba