Error
Error Code:
3064
MySQL Error 3064: Incorrect Argument Type in Function
Description
MySQL Error 3064 indicates that an argument provided to a function in your SQL query has an incompatible data type. This prevents the function from executing successfully because it expects a different type, such as a number instead of a string, or a date instead of a boolean. It often occurs when data types do not align with function signatures or when implicit type conversion fails.
Error Message
Incorrect type for argument %s in function %s.
Known Causes
3 known causesMismatched Data Types
Passing a value with an incompatible data type (e.g., a string) to a function that strictly expects a different type (e.g., an integer, date, or specific numeric format).
Failed Implicit Type Conversion
MySQL attempts to convert data types automatically, but the conversion fails because the data itself is not convertible to the expected type (e.g., trying to convert 'hello' to a number).
Incorrect Function Signature Usage
Calling a function with arguments that do not match any of its supported overloads or expected parameter types and order, even if the data itself is valid in another context.
Solutions
3 solutions available1. Review Function Arguments and Data Types easy
Ensure the data types passed to a function match its expected input types.
1
Identify the specific function causing the error. The error message will usually provide the function name. For example, if the error mentions `CONCAT`, you'll know it's the `CONCAT` function.
Look for the function name in the error message. Example: `Incorrect type for argument %s in function CONCAT`2
Consult the MySQL documentation for the identified function to understand the expected data types for each argument. Pay close attention to implicit type conversions that might be failing.
Example: For `CONCAT(str1, str2, ...)` the documentation specifies that arguments should be strings or convertible to strings.3
Examine the SQL query or stored procedure that calls the function. Check the data types of the columns or variables being passed as arguments.
Consider a query like: `SELECT CONCAT(numeric_column, ' - ', string_column) FROM my_table;`. If `numeric_column` is an integer, `CONCAT` might expect a string.4
Explicitly cast arguments to the correct data type if they are not already compatible. Use `CAST()` or `CONVERT()` functions.
Corrected example: `SELECT CONCAT(CAST(numeric_column AS CHAR), ' - ', string_column) FROM my_table;`2. Verify Stored Procedure/Function Definition medium
Check the definition of custom stored procedures or functions for type mismatches.
1
If the error occurs within a stored procedure or a user-defined function, retrieve its definition. This is particularly important if you or someone else has recently modified it.
SHOW CREATE PROCEDURE your_procedure_name;
SHOW CREATE FUNCTION your_function_name;2
Carefully review the `CREATE PROCEDURE` or `CREATE FUNCTION` statement. Examine the data types of the parameters declared for the procedure/function.
Example: `CREATE PROCEDURE my_proc (IN input_param VARCHAR(100), IN num_param INT)`3
Inside the procedure/function body, check how these parameters are used in function calls. Ensure that the data types being passed to internal functions or operations are compatible with the parameter types.
If `num_param` is an `INT` but used in a string concatenation without explicit casting, it can lead to this error.4
Modify the stored procedure/function definition to correct any identified type mismatches, often by using `CAST()` or `CONVERT()` on parameters before using them in functions that expect a specific type.
Example: `SET @result = CONCAT(CAST(num_param AS CHAR), ' - ', input_param);`3. Address Implicit Type Conversion Issues medium
Understand and manage MySQL's implicit type conversions, especially in comparisons and function calls.
1
Be aware that MySQL attempts to perform implicit type conversions in various scenarios, such as comparing values of different data types or passing arguments to functions. Sometimes, these conversions fail, leading to Error 3064.
Example: `WHERE numeric_column = 'some_string'` - MySQL might try to convert 'some_string' to a number, which could fail.2
When comparing values, explicitly cast them to a common, appropriate type. For example, when comparing a numeric column with a string literal, cast the string to a number or the number to a string.
Prefer explicit casting: `WHERE numeric_column = CAST('123' AS UNSIGNED)` or `WHERE CAST(numeric_column AS CHAR) = '123'`3
When passing arguments to functions, especially string manipulation or date functions, ensure the arguments are of the expected type. If a function expects a string and you're passing a number, cast the number to a string.
Use `CAST(your_numeric_column AS CHAR)` or `CONVERT(your_numeric_column, CHAR)` when passing numbers to functions expecting strings.4
If you are using functions that accept variable arguments (like `CONCAT`, `COALESCE`), ensure all arguments are either of compatible types or are explicitly cast to a type that the function can handle uniformly.
For `CONCAT`, ensure all arguments are strings or can be readily converted to strings. `SELECT CONCAT(col1, col2, col3)` where `col1`, `col2`, `col3` are all numeric might require `SELECT CONCAT(CAST(col1 AS CHAR), CAST(col2 AS CHAR), CAST(col3 AS CHAR))`.