MySQL - Comparing two dates in the WHERE clause

Question

I have a table, links that links two categories (parent & child) together. The table has five fields:

• autoinc
• parent_category_name year(4) NOT NULL
• parent_category_year varchar(255) NOT NULL
• child_category_name year(4) NOT NULL
• child_category_year varchar(255) NOT NULL

I am trying to write an INSERT SELECT that grabs both the parent & child category_id, and inserts it into a temp table.

INSERT INTO temp (parent_category_id, child_category_id)
SELECT parent.parent_category_id, child.child_category_id
  FROM links
  JOIN categories AS parent
       ON   parent.name = link.parent_category_name
       AND  parent.year = link.parent_category_year
  JOIN categories AS child
       ON   child.name = link.child_category_name
       AND  child.year = link.child_category_year

This query works fine, but I need to apply some business rules. The rules are:

• The parent year must be the same as the child year

OR

• The parent year must be one year less than the child year

I've added a WHERE clause to my query:

WHERE link.child_category_year = link.parent_category_year
   OR link.child_category_year - link.parent_category_year = 1 

When this INSERT statement executes in my Perl code, I get the following exception:

DBI Exception: DBD::mysql::db do failed: BIGINT UNSIGNED value is out of range in '(`my_database`.`links`.`child_category_year` - `my_database`.`links`.`parent_category_year`)' [for Statement "

So, I take it that the INSERT does not like my date subtraction in the WHERE clause. I explored using the DATEDIFF function, but I am not simply looking for a one year difference in dates, but rather one year less on the parent than the child.

How can I accomplish this without the insert error?

Answer 1

Totally unclear why "year" would be stored as an unsigned bigint. That is way overkill for my understanding of "year".

In any case, why not rephrase the logic to:

WHERE link.parent_category_year = link.child_category_year
      link.parent_category_year = link.child_category_year + 1 

In general, you should not have a problem adding 1 to an unsigned value.