query_optimizer/tests/execution_generator/Insert.test

#   Copyright 2011-2015 Quickstep Technologies LLC.
#   Copyright 2015 Pivotal Software, Inc.
#
#   Licensed 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-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|
+-----------+-----------------------------------------+------------------------+--------------------------------+
==