[MDEV-12028] Different result header when using CTE Created: 2017-02-08  Updated: 2017-02-09  Resolved: 2017-02-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2.3
Fix Version/s: 10.2.4

Type: Bug Priority: Minor
Reporter: Vicențiu Ciorbaru Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
Labels: 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 |
 
+-----------+-------------+--------+------------+



 Comments   
Comment by Igor Babaev [ 2017-02-09 ]

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      |
 
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

Comment by Igor Babaev [ 2017-02-09 ]

See my comments.

Generated at Thu Feb 08 07:54:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.