1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
# name: test/sql/copy/csv/csv_decimal_separator.test
# description: Support decimal separators
# group: [csv]
statement ok
PRAGMA enable_verification
# period-separated decimal doesn't parse
statement error
CREATE TABLE decimal_separators AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators.csv', column_types={'commas': 'double', 'periods': 'double'}, delim=';', decimal_separator=',')
----
Line: 2
statement ok
CREATE TABLE decimal_separators AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators.csv', column_types={'commas': 'double'}, delim=';', decimal_separator=',')
query II
SELECT commas, periods FROM decimal_separators;
----
1.1 1.1
0.25 0.25
15300.0 1.53e4
15300.0 +1.53e4
-15300.0 -1.53e4
query II
SELECT typeof(commas), typeof(periods) FROM decimal_separators limit 1;
----
DOUBLE VARCHAR
# reading the commas column as decimal fails when decimal separator is set to '.'
statement error
CREATE TABLE decimal_separators2 AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators.csv', column_types={'commas': 'decimal', 'periods': 'decimal'}, delim=';', decimal_separator='.')
----
Line: 2
# reading the commas column as float fails when decimal separator is set to '.'
statement error
CREATE TABLE decimal_separators2 AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators.csv', column_types={'commas': 'float', 'periods': 'decimal'}, delim=';', decimal_separator='.')
----
Line: 2
statement ok
CREATE TABLE decimal_separators2 AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators.csv', column_types={'commas': 'decimal'}, delim=';', decimal_separator=',')
query II
SELECT commas, periods FROM decimal_separators2;
----
1.100 1.1
0.250 0.25
15300.000 1.53e4
15300.000 +1.53e4
-15300.000 -1.53e4
query II
SELECT typeof(commas), typeof(periods) FROM decimal_separators2 limit 1;
----
DECIMAL(18,3) VARCHAR
# no separator specified => commas get read as varchar
statement ok
CREATE TABLE decimal_separators3 AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators.csv', column_types={'periods': 'decimal'}, delim=';')
query II
SELECT commas, periods FROM decimal_separators3;
----
1,1 1.100
0,25 0.250
1,53e4 15300.000
+1,53e4 +15300.000
-1,53e4 -15300.000
# in a comma-delimited file, comma as decimal separator is OK when quoted
statement ok
CREATE TABLE decimal_separators4 AS SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/decimal_separators_csv.csv', column_types={'commas': 'double'}, quote='"',delim=',',decimal_separator=',')
query II
SELECT commas, periods FROM decimal_separators4;
----
1.2345 1.2345
# auto-detection should read period-separated decimals as varchar
query II
SELECT typeof(commas), typeof(periods) FROM decimal_separators4 limit 1;
----
DOUBLE VARCHAR
# unsupported separator characters result in error
statement error
SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/invalid_char.csv', column_types={'foo': 'double'}, decimal_separator='รถ')
----
Binder Error: Unsupported parameter for DECIMAL_SEPARATOR: should be '.' or ','
# data with mixed separators will fail reading
statement error
SELECT * FROM read_csv_auto('{DATA_DIR}/csv/decimal_separators/mixed_format_fail.csv', column_types={'foo': 'double'}, decimal_separator=',', skip=0)
----
Line: 4
|
