Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.2.3
-
None
Description
CREATE TABLE transactions (timestamp int, customer_id int, amount int); |
|
|
SELECT timestamp, customer_id, amount, ( |
SELECT sum(amount) from transactions as t2 where t2.customer_id = t1.customer_id and t2.timestamp <= t1.timestamp) |
FROM transactions AS t1 |
ORDER BY customer_id, timestamp; |
Header results looks like this:
+-----------+-------------+--------+------------+
|
| timestamp | customer_id | amount | SELECT sum(amount) from transactions as t2 where t2.customer_id = t1.customer_id and t2.timestamp <= t1.timestamp |
|
+-----------+-------------+--------+------------+
|
WITH result as ( |
SELECT timestamp, customer_id, amount, ( |
SELECT sum(amount) from transactions as t2 where t2.customer_id = t1.customer_id and t2.timestamp <= t1.timestamp) |
FROM transactions AS t1 |
ORDER BY customer_id, timestamp) |
SELECT * from result; |
Header result looks different for the same definition.
+-----------+-------------+--------+------------+
|
| timestamp | customer_id | amount | Name_exp_4 |
|
+-----------+-------------+--------+------------+
|
We have same with views:
ariaDB [test]> CREATE VIEW v1 AS-> SELECT timestamp, customer_id, amount, (-> SELECT sum(amount) from transactions as t2 where t2.customer_id = t1.customer_id and t2.timestamp <= t1.timestamp)-> FROM transactions AS t1-> ORDER BY customer_id, timestamp;Query OK, 0 rows affected (0.01 sec)MariaDB [test]> show create table v1;+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| View | Create View | character_set_client | collation_connection |+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`timestamp` AS `timestamp`,`t1`.`customer_id` AS `customer_id`,`t1`.`amount` AS `amount`,(select sum(`t2`.`amount`) from `transactions` `t2` where `t2`.`customer_id` = `t1`.`customer_id` and `t2`.`timestamp` <= `t1`.`timestamp`) AS `Name_exp_4` from `transactions` `t1` order by `t1`.`customer_id`,`t1`.`timestamp` | utf8 | utf8_general_ci |+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+