Check if a (internal) function / feature / command exists in MariaDB / MySQL

Question

I use MEDIAN function in my app. When I deploy it on the production server, I get Syntax error because there is an older version of MariaDB.

Is there a way to check if a certain function exists? Someting like function_exists() in PHP?

I can check the version like this SELECT VERSION(); and maintain some list of functions and versions. But I'm looking for simpler and more universal solution.

Now I do it like this:

// PHP + NETTE framework

$sqlVersionOK = $this->checkSqlServerVersion([10,3,3]); 

if ($sqlVersionOK) { /* ... */ } else { /* ... */ }

public function checkSqlServerVersion(array $nums):bool {
    
    $version = $this->repository->query('SELECT VERSION() AS num;')->fetch();

    $version = explode('.', $version->num);
    $ver = array_map(function ($item) { return (int) filter_var($item, FILTER_SANITIZE_NUMBER_INT); }, $version);
    
    if ($ver[0] > $nums[0]) { return TRUE; } // major number is bigger
    elseif($ver[0]==$nums[0] and $ver[1] >= $nums[1]) { return TRUE; } // major number is the same, minor is bigger or same
    elseif($ver[0]==$nums[0] and $ver[1] == $nums[1] and $ver[2] >= $nums[1]) { return TRUE; } // major and minor numbers are the same, patch is bigger or same
    else { return FALSE; } 
    
}

Thanks.

Answer 1

10.6 added information_schema.keywords however its not early enough for you. (And I'm not sure it covers all functions).

Given MEDIAN is a 10.3+ Window function, and Window Functions where only added in 10.2 gives a very limited Window (pun intended) of when an alternate can be found.

Stored aggregate functions where only added in 10.3 also so that isn't a possibility to create a SQL function equivalent in 10.2.

MariaDB has a executable comment syntax where some function are only executed if the server is of sufficient version, e.g.:

/*M!100303 SELECT MEDIAN(star_rating) OVER (PARTITION BY name) FROM book_rating */

And this will be an empty query on 10.2 instances.

A more pragmatic answer however is:

• Run the same version in production as your test/development environment
• As even 10.3 just reached its end of upstream maintaince take the opportunity to upgrade to a newer version and gain all the benefits without spending too much time coding alternatives on unmaintained software.