query_optimizer/tests/execution_generator/Union.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.

SELECT int_col AS result FROM test
WHERE int_col < 5
UNION
SELECT int_col + 3 AS result FROM test
WHERE int_col < 5;
--
+-----------+
|result     |
+-----------+
|          2|
|          5|
|          0|
|          7|
|         -2|
|         -4|
|         -6|
|         -8|
|        -10|
|        -12|
|        -14|
|        -16|
|        -18|
|        -20|
|         -1|
|         -3|
|          4|
|         -5|
|         -7|
|         -9|
|        -11|
|        -13|
|        -15|
|        -17|
|        -19|
|        -21|
|        -23|
+-----------+
==

SELECT i + 1 AS result
FROM generate_series(1, 5) AS gs(i)
INTERSECT
SELECT i * 2 AS result
FROM generate_series(1, 5) AS gs(i)
--
+-----------+
|result     |
+-----------+
|          2|
|          4|
|          6|
+-----------+
==

SELECT float_col FROM test
WHERE int_col < 5
ORDER BY int_col DESC
LIMIT 5
UNION ALL
SELECT float_col FROM test
WHERE int_col < 5
ORDER BY int_col DESC
LIMIT 10
--
+---------------+
|float_col      |
+---------------+
|              2|
|     1.41421354|
|              1|
|     1.73205078|
|     2.23606801|
|     2.64575124|
|              3|
|     3.31662488|
|     3.60555124|
|     3.87298346|
|              2|
|     1.41421354|
|              1|
|     1.73205078|
|     2.23606801|
+---------------+