Venus: VTSetReviewsLookupTrigger fails on non-numeric EmployeeID values

Summary

The VTSetReviewsLookupTrigger trigger on venus (staging) causes 500 errors when inserting reviews with non-numeric EmployeeID values. This trigger does not exist on nathan-testing or jupiter, which is why tests pass there but fail on venus.

Error

ERROR: Error in submit_review: 1292 (22007): Truncated incorrect DOUBLE value: 'EMP_159833'

Root Cause

The trigger contains this comparison:

IF (NEW.EmployeeID = 0 OR NEW.EmployeeID = '' OR NEW.EmployeeID IS NULL) THEN

The problem is NEW.EmployeeID = 0 - this compares a VARCHAR column to an integer. When EmployeeID contains a non-numeric string like 'EMP_159833', MySQL attempts to cast it to a number for comparison and fails.

Affected Tests

• test_set_reviews_integration.py::TestSetReviewsSubmission::test_submit_review_with_employee_id
• test_set_reviews_integration.py::TestGetEmployeeReviews::test_get_employee_reviews_statistics

Environment Comparison

| Server | Has Trigger | set_reviews tests |
|--------|-------------|-------------------|
| nathan-testing | ❌ No | ✅ Pass |
| jupiter | ❌ No | ✅ Pass |
| venus | ✅ Yes | ❌ Fail (500) |

Schema Reference

-- VT_set_reviews.EmployeeID is VARCHAR(20)
('EmployeeID', 'varchar(20)', 'YES', '', None, '')

Suggested Fix

Update the trigger to use string comparison instead of integer comparison:

-- Before (buggy)
IF (NEW.EmployeeID = 0 OR NEW.EmployeeID = '' OR NEW.EmployeeID IS NULL) THEN

-- After (fixed)
IF (NEW.EmployeeID = '0' OR NEW.EmployeeID = '' OR NEW.EmployeeID IS NULL) THEN

Full migration SQL:

-- Migration: Fix VTSetReviewsLookupTrigger for non-numeric EmployeeID values
-- Issue: Comparing VARCHAR to integer 0 causes "Truncated incorrect DOUBLE value" error

DROP TRIGGER IF EXISTS VTSetReviewsLookupTrigger;

DELIMITER //
CREATE TRIGGER VTSetReviewsLookupTrigger
BEFORE INSERT ON VT_set_reviews
FOR EACH ROW
BEGIN
    DECLARE emp_id VARCHAR(20) DEFAULT NULL;
    DECLARE tap_date_time DATETIME DEFAULT NULL;
    
    -- Use string '0' instead of integer 0 for comparison
    IF (NEW.EmployeeID = '0' OR NEW.EmployeeID = '' OR NEW.EmployeeID IS NULL) THEN
        SELECT EmployeeID, CONCAT(`Date Tap`, ' ', `Time Tap`) 
        INTO emp_id, tap_date_time
        FROM VT_taps
        WHERE ClientID = NEW.CustomerID
          AND CONCAT(`Date Tap`, ' ', `Time Tap`) <= NEW.Visit_Date_Time
        ORDER BY CONCAT(`Date Tap`, ' ', `Time Tap`) DESC
        LIMIT 1;
        
        IF emp_id IS NOT NULL THEN
            SET NEW.EmployeeID = emp_id;
        END IF;
    END IF;
END//
DELIMITER ;

Decision Needed

1. Fix the trigger on venus (recommended) - Makes the trigger work correctly with VARCHAR column
2. Remove the trigger from venus - To match nathan-testing and jupiter
3. Add the trigger to other servers - For consistency (but still needs the fix)

Labels

• bug
• database
• venus