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?

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?

• sql
• mysql
• mariadb
• sea-orm
• sql-rank

• 1 I'd run the query in mysql --column-type-info. – Bill Karwin Commented Mar 22 at 16:10
• Why is it important to know the exact data type? Prepare for much bigger incompatibilities to overcome. – jarlh Commented Mar 22 at 18:28
• try cast(rank()... as unsigned)? but dealing with return types is something your ORM should be doing for you, from the type data provided with the select results; can you show your code? – ysth Commented Mar 23 at 2:35

2 Answers 2

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;

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.