| Bug #118846 | column name change cased by where | ||
|---|---|---|---|
| Submitted: | 15 Aug 8:50 | Modified: | 19 Aug 0:02 |
| Reporter: | cl hl | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 9.3.0 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | column where | ||
[18 Aug 9:02]
MySQL Verification Team
Hi, I am not sure what you mean by "column name should be same" ? mysql> SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE (s333.name < 'sample_63') ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; +----------------+----------------+------------------+------------+-----------------+ | avg_created_at | dense_rank_col | avg_created_at_1 | concat_col | date_format_col | +----------------+----------------+------------------+------------+-----------------+ | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | +----------------+----------------+------------------+------------+-----------------+ 22 rows in set (0.001 sec) mysql> SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; +----------------+----------------+------------------+------------+-----------------+ | avg_created_at | dense_rank_col | avg_created_at_1 | concat_col | date_format_col | +----------------+----------------+------------------+------------+-----------------+ | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | +----------------+----------------+------------------+------------+-----------------+
[18 Aug 9:33]
cl hl
hi, try to use these DDL
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NULL,
age INT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id INT NOT NULL,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO users (id, name, email, age, created_at) VALUES (9344, 'sample_556', 'sample_707', 35, '2025-07-05');
INSERT INTO users (id, name, email, age, created_at) VALUES (1546, 'sample_562', 'sample_282', 12, '2025-07-15');
INSERT INTO users (id, name, email, age, created_at) VALUES (6539, 'sample_235', 'sample_842', 92, '2025-06-12');
INSERT INTO users (id, name, email, age, created_at) VALUES (7704, 'sample_500', 'sample_984', 97, '2025-04-06');
INSERT INTO users (id, name, email, age, created_at) VALUES (4255, 'sample_360', 'sample_890', 51, '2024-10-07');
INSERT INTO users (id, name, email, age, created_at) VALUES (5919, 'sample_354', 'sample_924', 98, '2025-03-11');
INSERT INTO users (id, name, email, age, created_at) VALUES (6690, 'sample_69', 'sample_949', 96, '2024-09-27');
INSERT INTO users (id, name, email, age, created_at) VALUES (2213, 'sample_734', 'sample_404', 27, '2025-02-20');
INSERT INTO users (id, name, email, age, created_at) VALUES (5676, 'sample_488', 'sample_100', 50, '2025-08-10');
INSERT INTO users (id, name, email, age, created_at) VALUES (2221, 'sample_371', 'sample_630', 13, '2024-08-27');
INSERT INTO users (id, name, email, age, created_at) VALUES (8125, 'sample_440', 'sample_810', 50, '2025-01-01');
INSERT INTO users (id, name, email, age, created_at) VALUES (6856, 'sample_172', 'sample_596', 65, '2025-01-31');
INSERT INTO users (id, name, email, age, created_at) VALUES (4043, 'sample_905', 'sample_943', 75, '2025-05-20');
INSERT INTO users (id, name, email, age, created_at) VALUES (6476, 'sample_390', 'sample_777', 70, '2025-02-06');
INSERT INTO users (id, name, email, age, created_at) VALUES (8529, 'sample_187', 'sample_773', 86, '2025-03-30');
INSERT INTO users (id, name, email, age, created_at) VALUES (5461, 'sample_785', 'sample_452', 1, '2025-02-21');
INSERT INTO users (id, name, email, age, created_at) VALUES (8543, 'sample_443', 'sample_633', 97, '2025-06-14');
INSERT INTO users (id, name, email, age, created_at) VALUES (7136, 'sample_826', 'sample_783', 88, '2025-04-16');
INSERT INTO users (id, name, email, age, created_at) VALUES (226, 'sample_760', 'sample_589', 47, '2025-01-21');
INSERT INTO users (id, name, email, age, created_at) VALUES (2289, 'sample_598', 'sample_359', 91, '2025-08-10');
INSERT INTO users (id, name, email, age, created_at) VALUES (6515, 'sample_920', 'sample_590', 37, '2025-04-25');
INSERT INTO users (id, name, email, age, created_at) VALUES (4469, 'sample_53', 'sample_464', 40, '2024-09-23');
INSERT INTO users (id, name, email, age, created_at) VALUES (1401, 'sample_248', 'sample_940', 83, '2025-07-10');
INSERT INTO users (id, name, email, age, created_at) VALUES (2682, 'sample_256', 'sample_260', 56, '2025-05-16');
INSERT INTO users (id, name, email, age, created_at) VALUES (2047, 'sample_613', 'sample_998', 45, '2024-09-25');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2049, 4043, 656.59, 'sample_819', '2025-07-21');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5644, 6476, 956.07, 'sample_325', '2025-01-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8206, 8125, 312.71, 'sample_350', '2025-05-27');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (271, 6476, 839.37, 'sample_512', '2024-11-11');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4368, 2221, 85.61, 'sample_915', '2025-06-27');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2446, 2682, 124.09, 'sample_161', '2025-01-02');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4631, 6539, 555.33, 'sample_385', '2025-05-20');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8992, 6476, 213.83, 'sample_592', '2024-09-03');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6304, 4043, 955.86, 'sample_334', '2025-02-16');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2468, 2047, 203.50, 'sample_821', '2024-09-04');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7461, 5676, 187.70, 'sample_4', '2025-05-28');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (426, 6856, 730.22, 'sample_465', '2025-02-12');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4527, 1401, 61.14, 'sample_835', '2025-04-27');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4784, 4469, 795.86, 'sample_337', '2025-01-16');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (690, 6690, 990.22, 'sample_461', '2025-05-10');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8390, 5919, 566.32, 'sample_310', '2025-07-28');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (473, 4469, 423.08, 'sample_957', '2024-09-24');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5850, 7704, 127.19, 'sample_289', '2025-06-14');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8416, 7704, 341.16, 'sample_52', '2025-03-26');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2275, 7704, 183.29, 'sample_634', '2025-06-11');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3684, 6856, 217.54, 'sample_970', '2025-04-25');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1896, 7704, 622.62, 'sample_977', '2024-10-16');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (883, 4469, 895.90, 'sample_423', '2024-12-28');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6902, 8543, 584.86, 'sample_181', '2024-08-23');
And the column1's name of sql2's result should be avg_created_at,but it's avg_created_at_1
mysql> SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC;
+------------------+----------------+------------------+------------+-----------------+
| avg_created_at_1 | dense_rank_col | avg_created_at_1 | concat_col | date_format_col |
+------------------+----------------+------------------+------------+-----------
[19 Aug 0:02]
MySQL Verification Team
Hi, Thank you for the report, weird, verified. mysql> SELECT subq.avg_created_at AS xx, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; +------------------+----------------+------------------+------------+-----------------+ | avg_created_at_1 | dense_rank_col | avg_created_at_1 | concat_col | date_format_col | +------------------+----------------+------------------+------------+-----------------+ | 1 | 1 | 1 | 1 | 1 | ... +------------------+----------------+------------------+------------+-----------------+ 24 rows in set (0.001 sec) mysql> SELECT subq.avg_created_at xx, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; +------------------+----------------+------------------+------------+-----------------+ | avg_created_at_1 | dense_rank_col | avg_created_at_1 | concat_col | date_format_col | +------------------+----------------+------------------+------------+-----------------+ | 1 | 1 | 1 | 1 | 1 | ... +------------------+----------------+------------------+------------+-----------------+ 24 rows in set (0.001 sec) mysql> SELECT 1 xx, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; +----+----------------+------------------+------------+-----------------+ | xx | dense_rank_col | avg_created_at_1 | concat_col | date_format_col | +----+----------------+------------------+------------+-----------------+ | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | +----+----------------+------------------+------------+-----------------+ 24 rows in set (0.001 sec) mysql> select @@version; +-----------+ | @@version | +-----------+ | 9.4.0 | +-----------+ 1 row in set (0.000 sec) mysql>
Description: In theory, the result of sql1 ⊆ the result of sql2: SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE (s333.name < 'sample_63') ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; -- sql1 SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; -- sql2 However, the first column name changed after changing 'WHERE (s333.name < 'sample_63')' to 'WHERE '1' = '1'', seems like a logical bug: How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE users ( id INT NOT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NULL, age INT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ); CREATE TABLE orders ( id INT NOT NULL, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(50) NOT NULL, order_date DATE NOT NULL, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id) ); INSERT INTO users (id, name, email, age, created_at) VALUES (9344, 'sample_556', 'sample_707', 35, '2025-07-05'); INSERT INTO users (id, name, email, age, created_at) VALUES (1546, 'sample_562', 'sample_282', 12, '2025-07-15'); INSERT INTO users (id, name, email, age, created_at) VALUES (6539, 'sample_235', 'sample_842', 92, '2025-06-12'); INSERT INTO users (id, name, email, age, created_at) VALUES (7704, 'sample_500', 'sample_984', 97, '2025-04-06'); INSERT INTO users (id, name, email, age, created_at) VALUES (4255, 'sample_360', 'sample_890', 51, '2024-10-07'); INSERT INTO users (id, name, email, age, created_at) VALUES (5919, 'sample_354', 'sample_924', 98, '2025-03-11'); INSERT INTO users (id, name, email, age, created_at) VALUES (6690, 'sample_69', 'sample_949', 96, '2024-09-27'); INSERT INTO users (id, name, email, age, created_at) VALUES (2213, 'sample_734', 'sample_404', 27, '2025-02-20'); INSERT INTO users (id, name, email, age, created_at) VALUES (5676, 'sample_488', 'sample_100', 50, '2025-08-10'); INSERT INTO users (id, name, email, age, created_at) VALUES (2221, 'sample_371', 'sample_630', 13, '2024-08-27'); INSERT INTO users (id, name, email, age, created_at) VALUES (8125, 'sample_440', 'sample_810', 50, '2025-01-01'); INSERT INTO users (id, name, email, age, created_at) VALUES (6856, 'sample_172', 'sample_596', 65, '2025-01-31'); INSERT INTO users (id, name, email, age, created_at) VALUES (4043, 'sample_905', 'sample_943', 75, '2025-05-20'); INSERT INTO users (id, name, email, age, created_at) VALUES (6476, 'sample_390', 'sample_777', 70, '2025-02-06'); INSERT INTO users (id, name, email, age, created_at) VALUES (8529, 'sample_187', 'sample_773', 86, '2025-03-30'); INSERT INTO users (id, name, email, age, created_at) VALUES (5461, 'sample_785', 'sample_452', 1, '2025-02-21'); INSERT INTO users (id, name, email, age, created_at) VALUES (8543, 'sample_443', 'sample_633', 97, '2025-06-14'); INSERT INTO users (id, name, email, age, created_at) VALUES (7136, 'sample_826', 'sample_783', 88, '2025-04-16'); INSERT INTO users (id, name, email, age, created_at) VALUES (226, 'sample_760', 'sample_589', 47, '2025-01-21'); INSERT INTO users (id, name, email, age, created_at) VALUES (2289, 'sample_598', 'sample_359', 91, '2025-08-10'); INSERT INTO users (id, name, email, age, created_at) VALUES (6515, 'sample_920', 'sample_590', 37, '2025-04-25'); INSERT INTO users (id, name, email, age, created_at) VALUES (4469, 'sample_53', 'sample_464', 40, '2024-09-23'); INSERT INTO users (id, name, email, age, created_at) VALUES (1401, 'sample_248', 'sample_940', 83, '2025-07-10'); INSERT INTO users (id, name, email, age, created_at) VALUES (2682, 'sample_256', 'sample_260', 56, '2025-05-16'); INSERT INTO users (id, name, email, age, created_at) VALUES (2047, 'sample_613', 'sample_998', 45, '2024-09-25'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2049, 4043, 656.59, 'sample_819', '2025-07-21'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5644, 6476, 956.07, 'sample_325', '2025-01-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8206, 8125, 312.71, 'sample_350', '2025-05-27'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (271, 6476, 839.37, 'sample_512', '2024-11-11'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4368, 2221, 85.61, 'sample_915', '2025-06-27'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2446, 2682, 124.09, 'sample_161', '2025-01-02'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4631, 6539, 555.33, 'sample_385', '2025-05-20'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8992, 6476, 213.83, 'sample_592', '2024-09-03'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6304, 4043, 955.86, 'sample_334', '2025-02-16'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2468, 2047, 203.50, 'sample_821', '2024-09-04'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7461, 5676, 187.70, 'sample_4', '2025-05-28'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (426, 6856, 730.22, 'sample_465', '2025-02-12'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4527, 1401, 61.14, 'sample_835', '2025-04-27'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4784, 4469, 795.86, 'sample_337', '2025-01-16'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (690, 6690, 990.22, 'sample_461', '2025-05-10'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8390, 5919, 566.32, 'sample_310', '2025-07-28'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (473, 4469, 423.08, 'sample_957', '2024-09-24'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5850, 7704, 127.19, 'sample_289', '2025-06-14'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8416, 7704, 341.16, 'sample_52', '2025-03-26'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2275, 7704, 183.29, 'sample_634', '2025-06-11'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3684, 6856, 217.54, 'sample_970', '2025-04-25'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1896, 7704, 622.62, 'sample_977', '2024-10-16'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (883, 4469, 895.90, 'sample_423', '2024-12-28'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6902, 8543, 584.86, 'sample_181', '2024-08-23'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3836, 6476, 969.27, 'sample_533', '2024-09-26'); SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE (s333.name < 'sample_63') ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; SELECT subq.avg_created_at AS avg_created_at, 1 AS dense_rank_col, subq.avg_created_at AS avg_created_at_1, 1 AS concat_col, 1 AS date_format_col FROM (SELECT s333.id AS id, 1 AS avg_created_at FROM users AS s333 WHERE '1' = '1' ORDER BY s333.id DESC) AS subq INNER JOIN orders AS ord ON (ord.user_id = subq.id) WHERE (subq.avg_created_at <= 95) ORDER BY subq.id ASC; Suggested fix: their column name should be the same