500 error (invalid SQL) when creating a tag

[cellio]

meta:292502

A user created a tag, filling out all fields including synonyms and a parent, and got a 500 error. (See the meta report for the exact inputs.) The user was able to create the tag piecemeal, one field at a time.

The error code is a9113c80-d5fc-4c34-92c4-67826a90b0cf, which says:

 ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL
    SELECT t.id, t.parent_id
    FROM tags t
    INNER JOIN CTE ON t.p' at line 5

/var/apps/qpixel/app/models/tag.rb:44:in `all_children'
/var/apps/qpixel/app/models/tag.rb:70:in `parent_not_own_child'
/var/apps/qpixel/app/controllers/tags_controller.rb:73:in `create'

The problem seems to be arising somewhere in here:

  def all_children
    query = File.read(Rails.root.join('db/scripts/tag_children.sql'))
    query = query.gsub('$ParentId', id.to_s)
    ActiveRecord::Base.connection.execute(query).to_a.map(&:first)
  end

[ArtOfCode-]

Yeah that's a horrible query - child tags are recursive so the query for them is awful. It works if everything is set up right but when you're creating a tag it's not set up for it yet. We might be able to make it work but it's probably more robust to not allow adding parent/child tags until the tag is created.

[cellio]

Disallowing parent/child tags before the target tag is created sounds fine to me.

[cellio]

"New" and "edit" both render the same form. Would it make sense for the "new" path to start by just setting a name, creating that tag, and then popping up the edit form for the new tag to populate all the other fields?