sqlmysqlmariadbsea-ormsql-rank

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

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?

Solution

  • 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;