How to find out exact return type of the RANK() window function in MySQL and MariaDB?

Question

I am accessing a MySQL and MariaDB database using Rust with SeaORM, and my query includes the RANK() window function.

In MySQL, I can fetch the rank column value using u32 (unsigned integer),
In MariaDB, this does not work. I have to use i32 (signed integer) instead.

Here’s an example query:

SELECT RANK() OVER (ORDER BY some_column DESC) AS rank_value
FROM some_table;

How can I find out the exact return type of RANK() in MySQL and MariaDB? Is there any SQL command by which I can see and confirm it myself?

Answer 1

You can create a temporary table with that value and find out what the type is:

CREATE TEMPORARY TABLE mycutelittlepiggy
SELECT RANK() OVER (ORDER BY some_column DESC) AS rank_value
FROM some_table;

and then either show create table mycutelittlepiggy; or desc mycutelittlepiggy;.

The advantage of temporary tables is that they exist only inside your current connection and they are auto-cleaned afterwards.

However, it's possible that you need the type inside your current script and then you would find show create table and/or desc less than adequate. An alternative for that case is to create a table and clean up afterwards:

create table mycutelittlepiggy select '123' as oint;

select COLUMN_TYPE from information_schema.columns where TABLE_NAME = 'mycutelittlepiggy' and COLUMN_NAME = 'oint';

And then clean up:

drop mycutelittlepiggy;

Answer 2

1. MySQL: RANK() likely returns an unsigned 64-bit integer (BIGINT UNSIGNED).

2. MariaDB: RANK() likely returns a signed 64-bit integer (BIGINT).

Check the Exact Type in MariaDB

MariaDB has typeof(), which can help:

SELECT TYPEOF(RANK() OVER (ORDER BY 1));

This will return bigint or another relevant type.