core/src/test/resources/sql/stream.iq

# stream.iq - streaming sql tests
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to you under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
!if (fixed.calcite6282) {
!use orinoco
!set outputformat mysql
SELECT * FROM TABLE(
  TUMBLE(
    DATA => TABLE ORDERS,
    TIMECOL => DESCRIPTOR(ROWTIME),
    SIZE => INTERVAL '1' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:16:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 10:59:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.000 | 2015-02-15 11:11:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

SELECT * FROM TABLE(TUMBLE(TABLE ORDERS, DESCRIPTOR(ROWTIME), INTERVAL '1' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:16:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 10:59:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.000 | 2015-02-15 11:11:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

SELECT * FROM TABLE(TUMBLE((SELECT * FROM ORDERS), DESCRIPTOR(ROWTIME), INTERVAL '1' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:16:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 10:59:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.000 | 2015-02-15 11:11:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

SELECT * FROM TABLE(TUMBLE((SELECT * FROM ORDERS), DESCRIPTOR(ROWTIME), INTERVAL '10' MINUTE, INTERVAL '3' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:13:00.000 | 2015-02-15 10:23:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:23:00.000 | 2015-02-15 10:33:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:23:00.000 | 2015-02-15 10:33:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:53:00.000 | 2015-02-15 11:03:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:03:00.000 | 2015-02-15 11:13:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

SELECT * FROM TABLE(HOP(TABLE ORDERS, DESCRIPTOR(ROWTIME), INTERVAL '5' MINUTE, INTERVAL '10' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:10:00.000 | 2015-02-15 10:20:00.000 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:20:00.000 | 2015-02-15 10:30:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:20:00.000 | 2015-02-15 10:30:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:50:00.000 | 2015-02-15 11:00:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:55:00.000 | 2015-02-15 11:05:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:05:00.000 | 2015-02-15 11:15:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.000 | 2015-02-15 11:20:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(10 rows)

!ok

SELECT * FROM TABLE(
  HOP(
    DATA => TABLE ORDERS,
    TIMECOL => DESCRIPTOR(ROWTIME),
    SLIDE => INTERVAL '5' MINUTE,
    SIZE => INTERVAL '10' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:10:00.000 | 2015-02-15 10:20:00.000 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:20:00.000 | 2015-02-15 10:30:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:20:00.000 | 2015-02-15 10:30:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:50:00.000 | 2015-02-15 11:00:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:55:00.000 | 2015-02-15 11:05:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:05:00.000 | 2015-02-15 11:15:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.000 | 2015-02-15 11:20:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(10 rows)

!ok

SELECT * FROM TABLE(HOP(TABLE ORDERS, DESCRIPTOR(ROWTIME), INTERVAL '5' MINUTE, INTERVAL '10' MINUTE, INTERVAL '2' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:07:00.000 | 2015-02-15 10:17:00.000 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:12:00.000 | 2015-02-15 10:22:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:17:00.000 | 2015-02-15 10:27:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:22:00.000 | 2015-02-15 10:32:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:17:00.000 | 2015-02-15 10:27:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:22:00.000 | 2015-02-15 10:32:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:52:00.000 | 2015-02-15 11:02:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:57:00.000 | 2015-02-15 11:07:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:02:00.000 | 2015-02-15 11:12:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:07:00.000 | 2015-02-15 11:17:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(10 rows)

!ok

SELECT * FROM TABLE(HOP((SELECT * FROM ORDERS), DESCRIPTOR(ROWTIME), INTERVAL '5' MINUTE, INTERVAL '10' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:10:00.000 | 2015-02-15 10:20:00.000 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:20:00.000 | 2015-02-15 10:30:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:15:00.000 | 2015-02-15 10:25:00.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:20:00.000 | 2015-02-15 10:30:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:50:00.000 | 2015-02-15 11:00:00.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:55:00.000 | 2015-02-15 11:05:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:05:00.000 | 2015-02-15 11:15:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.000 | 2015-02-15 11:20:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(10 rows)

!ok

SELECT * FROM TABLE(SESSION(TABLE ORDERS, DESCRIPTOR(ROWTIME), DESCRIPTOR(PRODUCT), INTERVAL '20' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:35:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15.000 | 2015-02-15 10:44:15.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45.000 | 2015-02-15 10:44:45.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 11:30:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 11:30:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

SELECT * FROM TABLE(
  SESSION(
    DATA => TABLE ORDERS,
    TIMECOL => DESCRIPTOR(ROWTIME),
    KEY => DESCRIPTOR(PRODUCT),
    SIZE => INTERVAL '20' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:35:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15.000 | 2015-02-15 10:44:15.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45.000 | 2015-02-15 10:44:45.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 11:30:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 11:30:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

SELECT * FROM TABLE(SESSION((SELECT * FROM ORDERS), DESCRIPTOR(ROWTIME), DESCRIPTOR(PRODUCT), INTERVAL '20' MINUTE));
+---------------------+----+---------+-------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.000 | 2015-02-15 10:35:00.000 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15.000 | 2015-02-15 10:44:15.000 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45.000 | 2015-02-15 10:44:45.000 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 11:30:00.000 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 10:58:00.000 | 2015-02-15 11:30:00.000 |
+---------------------+----+---------+-------+-------------------------+-------------------------+
(5 rows)

!ok

!}
# End of fixed.calcite6282