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:
- Oracle SQL Developer 23.1.1.345.2114
- Java(TM) Platform 11.0.21.0.2
- MySQL JDBC 9.0.0
- MariaDB 11.4.2
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).
