query_optimizer/tests/execution_generator/Insert.test

# 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.

INSERT INTO test VALUES (100, 2, 1.1, 1.3, 'foo');
INSERT INTO test VALUES (100, 3, 1.2, 1.4, 'foofoo');
INSERT INTO test VALUES (NULL, 3, 1.2, NULL, 'foofoo');
SELECT * FROM test WHERE int_col = 100;
--
+-----------+--------------------+---------------+------------------------+--------------------+
|int_col    |long_col            |float_col      |double_col              |char_col            |
+-----------+--------------------+---------------+------------------------+--------------------+
|        100|                   2|     1.10000002|                     1.3|                 foo|
|        100|                   3|     1.20000005|      1.3999999999999999|              foofoo|
+-----------+--------------------+---------------+------------------------+--------------------+
==

CREATE TABLE bar1 (x INT NULL, y DOUBLE);

INSERT INTO bar1
SELECT int_col, long_col
FROM test;

SELECT *
FROM bar1
WHERE x = 100;
--
+-----------+------------------------+
|x          |y                       |
+-----------+------------------------+
|        100|                       2|
|        100|                       3|
+-----------+------------------------+
==

INSERT INTO bar1
SELECT int_col, long_col
FROM test;

SELECT *
FROM bar1
WHERE x = 100;
--
+-----------+------------------------+
|x          |y                       |
+-----------+------------------------+
|        100|                       2|
|        100|                       3|
|        100|                       2|
|        100|                       3|
+-----------+------------------------+
==

CREATE TABLE bar2 (x INT, y INT, z FLOAT);

WITH s(i) AS
  ( SELECT *
   FROM generate_series(1, 5)),
     t(j) AS
  ( SELECT *
   FROM generate_series(1, 5))
INSERT INTO bar2
SELECT i, j, i*j
FROM s, t
WHERE i < j;

SELECT * FROM bar2;
--
+-----------+-----------+---------------+
|x          |y          |z              |
+-----------+-----------+---------------+
|          1|          2|              2|
|          1|          3|              3|
|          1|          4|              4|
|          1|          5|              5|
|          2|          3|              6|
|          2|          4|              8|
|          2|          5|             10|
|          3|          4|             12|
|          3|          5|             15|
|          4|          5|             20|
+-----------+-----------+---------------+
==

CREATE TABLE bar3 (x INT, y DATE, z DOUBLE NULL, w VARCHAR(32));

INSERT INTO bar3
SELECT i, DATE '2016-1-1', NULL, 'abc'
FROM generate_series(1, 5) AS gs(i);

SELECT * FROM bar3;
--
+-----------+------------+------------------------+--------------------------------+
|x          |y           |z                       |w                               |
+-----------+------------+------------------------+--------------------------------+
|          1|  2016-01-01|                    NULL|                             abc|
|          2|  2016-01-01|                    NULL|                             abc|
|          3|  2016-01-01|                    NULL|                             abc|
|          4|  2016-01-01|                    NULL|                             abc|
|          5|  2016-01-01|                    NULL|                             abc|
+-----------+------------+------------------------+--------------------------------+
==

CREATE TABLE bar4 (x INT, y DATETIME, z DOUBLE NULL, w VARCHAR(32));

INSERT INTO bar4
SELECT i, DATETIME '2016-1-1T00:00:00', NULL, 'abc'
FROM generate_series(1, 5) AS gs(i);

SELECT * FROM bar4;
--
+-----------+-----------------------------------------+------------------------+--------------------------------+
|x          |y                                        |z                       |w                               |
+-----------+-----------------------------------------+------------------------+--------------------------------+
|          1|                      2016-01-01T00:00:00|                    NULL|                             abc|
|          2|                      2016-01-01T00:00:00|                    NULL|                             abc|
|          3|                      2016-01-01T00:00:00|                    NULL|                             abc|
|          4|                      2016-01-01T00:00:00|                    NULL|                             abc|
|          5|                      2016-01-01T00:00:00|                    NULL|                             abc|
+-----------+-----------------------------------------+------------------------+--------------------------------+
==

CREATE TABLE bar5 (x INT NULL, y INT);

INSERT INTO bar5 VALUES (1,2),(3,4),(5,6);

SELECT * FROM bar5;
--
+-----------+-----------+
|x          |y          |
+-----------+-----------+
|          1|          2|
|          3|          4|
|          5|          6|
+-----------+-----------+
==