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.
#
!use orinoco
!set outputformat mysql

# Test case for [CALCITE-7410] TIMESTAMP type for TUMBLE and HOP is hardwired to TIMESTAMP(3)
# Since we cast the input column to TIMESTAMP(3), we expect window_start to have the same type.
WITH S AS (SELECT *, CAST(ROWTIME AS TIMESTAMP(3)) + INTERVAL '0.5' SECONDS AS TS FROM ORDERS)
SELECT * FROM TABLE(TUMBLE((SELECT * FROM S), DESCRIPTOR(TS), INTERVAL '20:10.525' MINUTE TO SECOND));
+---------------------+----+---------+-------+-------------------------+-------------------------+-------------------------+
| ROWTIME             | ID | PRODUCT | UNITS | TS                      | window_start            | window_end              |
+---------------------+----+---------+-------+-------------------------+-------------------------+-------------------------+
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00.500 | 2015-02-15 10:10:31.125 | 2015-02-15 10:30:41.650 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15.500 | 2015-02-15 10:10:31.125 | 2015-02-15 10:30:41.650 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45.500 | 2015-02-15 10:10:31.125 | 2015-02-15 10:30:41.650 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00.500 | 2015-02-15 10:50:52.175 | 2015-02-15 11:11:02.700 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00.500 | 2015-02-15 10:50:52.175 | 2015-02-15 11:11:02.700 |
+---------------------+----+---------+-------+-------------------------+-------------------------+-------------------------+
(5 rows)

!ok

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 | 2015-02-15 10:16:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 10:59:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00 | 2015-02-15 11:11:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:16:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 10:59:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00 | 2015-02-15 11:11:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:16:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 10:59:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00 | 2015-02-15 11:11:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:23:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:23:00 | 2015-02-15 10:33:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:23:00 | 2015-02-15 10:33:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:53:00 | 2015-02-15 11:03:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:03:00 | 2015-02-15 11:13:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:20:00 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:20:00 | 2015-02-15 10:30:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:20:00 | 2015-02-15 10:30:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:50:00 | 2015-02-15 11:00:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:55:00 | 2015-02-15 11:05:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:05:00 | 2015-02-15 11:15:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00 | 2015-02-15 11:20:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:20:00 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:20:00 | 2015-02-15 10:30:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:20:00 | 2015-02-15 10:30:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:50:00 | 2015-02-15 11:00:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:55:00 | 2015-02-15 11:05:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:05:00 | 2015-02-15 11:15:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00 | 2015-02-15 11:20:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:17:00 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:12:00 | 2015-02-15 10:22:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:17:00 | 2015-02-15 10:27:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:22:00 | 2015-02-15 10:32:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:17:00 | 2015-02-15 10:27:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:22:00 | 2015-02-15 10:32:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:52:00 | 2015-02-15 11:02:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:57:00 | 2015-02-15 11:07:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:02:00 | 2015-02-15 11:12:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:07:00 | 2015-02-15 11:17:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:20:00 |
| 2015-02-15 10:15:00 |  1 | paint   |    10 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:20:00 | 2015-02-15 10:30:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:15:00 | 2015-02-15 10:25:00 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:20:00 | 2015-02-15 10:30:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:50:00 | 2015-02-15 11:00:00 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:55:00 | 2015-02-15 11:05:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:05:00 | 2015-02-15 11:15:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 11:10:00 | 2015-02-15 11:20:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:35:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15 | 2015-02-15 10:44:15 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45 | 2015-02-15 10:44:45 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 11:30:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 11:30:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:35:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15 | 2015-02-15 10:44:15 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45 | 2015-02-15 10:44:45 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 11:30:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 11:30:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(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 | 2015-02-15 10:35:00 |
| 2015-02-15 10:24:15 |  2 | paper   |     5 | 2015-02-15 10:24:15 | 2015-02-15 10:44:15 |
| 2015-02-15 10:24:45 |  3 | brush   |    12 | 2015-02-15 10:24:45 | 2015-02-15 10:44:45 |
| 2015-02-15 10:58:00 |  4 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 11:30:00 |
| 2015-02-15 11:10:00 |  5 | paint   |     3 | 2015-02-15 10:58:00 | 2015-02-15 11:30:00 |
+---------------------+----+---------+-------+---------------------+---------------------+
(5 rows)

!ok