MariaDB functions cannot correctly retrieve data from INFORMATION_SCHEMA.COLUMNS

Question

I want to create a function in SQL that takes in a field name and field type and returns a boolean value. Based on the result, I will decide whether or not to update the field type. Here is the code for debug:

use teleport;
DELIMITER //
DROP FUNCTION IF EXISTS check_column_data_type;
CREATE FUNCTION check_column_data_type(table_name VARCHAR(255), column_name VARCHAR(255), data_type VARCHAR(255)) RETURNS longtext
BEGIN
    DECLARE column_type longtext;
    SELECT
            GROUP_CONCAT(DATA_TYPE)
            INTO column_type
            FROM
                INFORMATION_SCHEMA.COLUMNS 
            WHERE
                TABLE_SCHEMA = "teleport" 
                AND TABLE_NAME = "tp_order"   -- use special string for test
                AND COLUMN_NAME = "content";
    -- IF LOWER(column_type) = LOWER(data_type) THEN
    --     RETURN(TRUE);
    -- ELSE
    --     RETURN(FALSE);
    -- END IF;
    RETURN(column_type);   -- just show the content of column_type
END //

DELIMITER ;

select check_column_data_type('tp_order', 'content', 'varchar(10240)');

But the output is really puzzling, long and repeat:

MariaDB [teleport]> source /home/teleport/www/teleport/teleport_update.sql
... ... 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240),varchar(10240)
... ...

The strange thing is not only the results. When I query COLUMN_TYPE in the function, it return NULL, but it work normally when I query it in Navicat. In the function, I have to use DATA_TYPE. What could be the problem? Is my implementation approach for this functionality correct?

If I don't use function, just query using navicat. The result is correct:

SELECT
    GROUP_CONCAT( COLUMN_TYPE )
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    TABLE_SCHEMA = "teleport" 
    AND TABLE_NAME = "tp_order" 
    AND COLUMN_NAME = "content";

RESULT: varchar(1024)

Answer 1

As your query returns the datatype for one column in one table you do not need the GROUP_CONCAT.

The actual problem is that you are using parameter names that are the same as the column names. Use a prefix (like 'in_') to separate the parameters from the column names:

DELIMITER //
DROP FUNCTION IF EXISTS check_column_data_type
//
CREATE FUNCTION check_column_data_type(
in_table_name VARCHAR(255),
in_column_name VARCHAR(255),
in_data_type VARCHAR(255)
) 
RETURNS longtext
BEGIN
DECLARE column_type longtext;
SELECT DATA_TYPE INTO column_type
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE
  TABLE_SCHEMA = database() 
  AND TABLE_NAME = in_table_name   -- use special string for test
  AND COLUMN_NAME = in_column_name;
    -- IF LOWER(column_type) = LOWER(data_type) THEN
    --     RETURN(TRUE);
    -- ELSE
    --     RETURN(FALSE);
    -- END IF;
RETURN(column_type);   -- just show the content of column_type
END