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

CREATE TABLE source (
  int_col INT NULL,
  date_col DATE NULL,
  char_col CHAR(16),
  varchar_col VARCHAR(16)
);

INSERT INTO source VALUES(1, '2000-01-01', 'aa', 'aaa');
INSERT INTO source VALUES(2, '2000-02-02', 'bb', 'bbb');
INSERT INTO source VALUES(3, '2000-03-03', 'cc', 'ccc');
INSERT INTO source VALUES(4, '2000-04-04', 'aa', 'ddd');
INSERT INTO source VALUES(5, '2000-05-05', 'bb', 'eee');
INSERT INTO source VALUES(6, '2000-06-06', 'cc', 'fff');

COPY source TO stderr WITH (DELIMITER '|');
--
1|2000-01-01|aa|aaa
2|2000-02-02|bb|bbb
3|2000-03-03|cc|ccc
4|2000-04-04|aa|ddd
5|2000-05-05|bb|eee
6|2000-06-06|cc|fff
==

COPY
  SELECT char_col, SUM(int_col)
  FROM source
  GROUP BY char_col
TO stderr;
--
aa	5
bb	7
cc	9
==

COPY
  SELECT * FROM (
    SELECT -int_col * 1000, 'Negative' FROM source
    UNION ALL
    SELECT int_col * 1000, 'Positive' FROM source
  ) AS t(VALUE, SIGN)
  ORDER BY value
TO stderr WITH (FORMAT 'CSV', DELIMITER e'\t');
--
VALUE	SIGN
-6000	Negative
-5000	Negative
-4000	Negative
-3000	Negative
-2000	Negative
-1000	Negative
1000	Positive
2000	Positive
3000	Positive
4000	Positive
5000	Positive
6000	Positive
==

# WITH clause.
WITH r(x, y) AS (
  SELECT i, i + 1
  FROM generate_series(0, 9) AS g(i)
)
COPY
  SELECT x * y AS value FROM r ORDER BY value
TO stderr;
--
0
2
6
12
20
30
42
56
72
90
==


# Test handling of NULL values and special characters.
DELETE FROM source;
INSERT INTO source VALUES(1, '2000-01-01', 'abc', 'def');
INSERT INTO source VALUES(2, '2000-02-02', e'a\ta', '|,|');
INSERT INTO source VALUES(NULL, NULL, e'b\nb', '"""');

COPY source TO stderr;
--
1	2000-01-01	abc	def
2	2000-02-02	a\ta	|,|
\N	\N	b\nb	"""
==

COPY source TO stderr WITH (FORMAT 'CSV', HEADER FALSE);
--
1,2000-01-01,abc,def
2,2000-02-02,a	a,"|,|"
,,"b
b",""""""""
==

COPY source TO stderr WITH (FORMAT 'CSV', DELIMITER '|', NULL_STRING '.na', HEADER TRUE);
--
int_col|date_col|char_col|varchar_col
1|2000-01-01|abc|def
2|2000-02-02|a	a|"|,|"
.na|.na|"b
b"|""""""""
==