sqlmariadboracle-sqldeveloperalias

Why are column aliases being ignored? Oracle SQL Developer - MySQL JDBC


I am trying to determine why a simple SQL select statement I executed with an alias assigned to a column is returning data but with the original column name and not the alias I assigned.

I am experiencing this while executing SQL statements in Oracle SQL Developer with a MariaDB database connected via a MySQL JDBC driver.

Versions for reference:

Sample SQL table/data:

-- Create the schema
CREATE SCHEMA example;

-- Use the schema
USE example;

-- Create the employee table
CREATE TABLE example.employee (
    name VARCHAR(20),
    phone_number CHAR(10),
    num0 INT,
    state_location CHAR(2)
);

-- Insert statements for 10 employees
INSERT INTO employee (name, phone_number, num0, state_location) VALUES
('Alice', '1234567890', 30, 'TX'),
('Bob', '2345678901', 25, 'CA'),
('Charlie', '3456789012', 28, 'NY'),
('David', '4567890123', 35, 'FL'),
('Eve', '5678901234', 22, 'WA'),
('Frank', '6789012345', 40, 'IL'),
('Grace', '7890123456', 27, 'GA'),
('Hank', '8901234567', 33, 'NV'),
('Ivy', '9012345678', 29, 'OR'),
('Jack', '0123456789', 31, 'AZ');

When I run a SQL statement such as:

select name, phone_number, num0, state_location from employee;

The resulting output is:

name                 phone_number num0       state_location 
-------------------- ------------ ---------- -------------- 
Alice                1234567890   30         TX             
Bob                  2345678901   25         CA             
Charlie              3456789012   28         NY             
David                4567890123   35         FL             
Eve                  5678901234   22         WA             
Frank                6789012345   40         IL             
Grace                7890123456   27         GA             
Hank                 8901234567   33         NV             
Ivy                  9012345678   29         OR             
Jack                 0123456789   31         AZ             

10 rows selected.

Updating this statement with some aliases like this, I get the exact same result above:

select name, phone_number, num0 as age, state_location as state_abbreviation from employee;

I am expecting to get the column names modified as is typical (or at least an error if the syntax was rejected by this database configuration).

Like this:

name                 phone_number age                 state_abbr 
-------------------- ------------ ------------------- ---------- 

If I apply some function or manipulation to a column before applying an alias, then the alias will be displayed.

Example modification:

SELECT name, phone_number, cast(num0 as int) as age, CAST(state_location AS VARCHAR(2)) AS state_abbr FROM employee;

Result:

name                 phone_number age                 state_abbr 
-------------------- ------------ ------------------- ---------- 
Alice                1234567890   30                  TX         
Bob                  2345678901   25                  CA         
Charlie              3456789012   28                  NY         
David                4567890123   35                  FL         
Eve                  5678901234   22                  WA         
Frank                6789012345   40                  IL         
Grace                7890123456   27                  GA         
Hank                 8901234567   33                  NV         
Ivy                  9012345678   29                  OR         
Jack                 0123456789   31                  AZ         

10 rows selected. 

Note: I attempted to define the column alias with the following styles and got the same results:

num0 as age
num0 "age"
num0 as "age"
num0 `age`
num0 as `age`

If I instead go to my command line and connect to the MariaDB directly and run the first alias attempt (ie num0 as age), it does successfully print the value with correct alias name.

There is something about this environment configuration between Oracle SQL Dev, the MySQL JDBC and the MariaDB underneath that is resulting this unexpected behavior (at least unexpected to me).


Solution

  • Yes, you're right, the issue is related to Oracle SQL Developer using MySQL JDBC driver. Oracle SQL Developer doesn’t always interpret results accurately when connected to non-Oracle databases due to differences in driver capabilities and display settings and so metadata like column aliases might not refresh properly due to how Oracle SQL Developer interacts with the MySQL JDBC driver.

    Switching to MariaDB Connector/J won’t resolve this issue either. I guess the best workaround is to use an alternative database management tool specifically designed for MySQL/MariaDB, like MySQL Workbench, DBeaver, or a native MariaDB CLI. These tools generally offer better compatibility with MariaDB’s metadata and handle column aliases and other features correctly.