1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test scalar printf", "[printf]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // printf without format specifiers
14 result = con.Query("SELECT printf('hello'), printf(NULL)");
15 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
16 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
17
18 // format strings
19 result = con.Query("SELECT printf('%s', 'hello'), printf('%s: %s', 'hello', 'world')");
20 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
21 REQUIRE(CHECK_COLUMN(result, 1, {"hello: world"}));
22
23 // format strings with NULL values
24 result = con.Query("SELECT printf('%s', NULL), printf(NULL, 'hello', 'world')");
25 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
26 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
27
28 // booleans
29 result = con.Query("SELECT printf('%d', TRUE)");
30 REQUIRE(CHECK_COLUMN(result, 0, {"1"}));
31
32 // integers
33 result = con.Query("SELECT printf('%d', 33), printf('%d + %d = %d', 3, 5, 3 + 5)");
34 REQUIRE(CHECK_COLUMN(result, 0, {"33"}));
35 REQUIRE(CHECK_COLUMN(result, 1, {"3 + 5 = 8"}));
36
37 // integers with special formatting specifiers
38 result = con.Query(
39 "SELECT printf('%04d', 33), printf('%s %02d:%02d:%02d %s', 'time', 12, 3, 16, 'AM'), printf('%10d', 1992)");
40 REQUIRE(CHECK_COLUMN(result, 0, {"0033"}));
41 REQUIRE(CHECK_COLUMN(result, 1, {"time 12:03:16 AM"}));
42 REQUIRE(CHECK_COLUMN(result, 2, {" 1992"}));
43
44 // different integer types
45 result = con.Query("SELECT printf('%hhd %hd %d %lld', 33::TINYINT, 12::SMALLINT, 40::INTEGER, 80::BIGINT)");
46 REQUIRE(CHECK_COLUMN(result, 0, {"33 12 40 80"}));
47 // ...but really any of these can be used
48 result = con.Query("SELECT printf('%d %lld %hhd %hd', 33::TINYINT, 12::SMALLINT, 40::INTEGER, 80::BIGINT)");
49 REQUIRE(CHECK_COLUMN(result, 0, {"33 12 40 80"}));
50
51 // octal hex etc
52 result = con.Query("SELECT printf('%d %x %o %#x %#o', 100, 100, 100, 100, 100)");
53 REQUIRE(CHECK_COLUMN(result, 0, {"100 64 144 0x64 0144"}));
54
55 // ascii characters
56 result = con.Query("SELECT printf('%c', 65)");
57 REQUIRE(CHECK_COLUMN(result, 0, {"A"}));
58
59 // width trick
60 result = con.Query("SELECT printf('%*d', 5, 10)");
61 REQUIRE(CHECK_COLUMN(result, 0, {" 10"}));
62
63 // floating point numbers
64 result = con.Query("SELECT printf('%.2f', 10.0::FLOAT), printf('%.4f', 0.5)");
65 REQUIRE(CHECK_COLUMN(result, 0, {"10.00"}));
66 REQUIRE(CHECK_COLUMN(result, 1, {"0.5000"}));
67
68 // weird float stuff
69 result = con.Query("SELECT printf('floats: %4.2f %+.0e %E', 3.1416, 3.1416, 3.1416)");
70 REQUIRE(CHECK_COLUMN(result, 0, {"floats: 3.14 +3e+00 3.141600E+00"}));
71
72 // incorrect number of parameters
73 // too few parameters
74 REQUIRE_FAIL(con.Query("SELECT printf('%s')"));
75 REQUIRE_FAIL(con.Query("SELECT printf('%s %s', 'hello')"));
76 // excess parameters are ignored
77 result = con.Query("SELECT printf('%s', 'hello', 'world')");
78 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
79 // incorrect types
80 REQUIRE_FAIL(con.Query("SELECT printf('%s', 42)"));
81 REQUIRE_FAIL(con.Query("SELECT printf('%d', 'hello')"));
82}
83
84TEST_CASE("Test printf with vectors", "[printf]") {
85 unique_ptr<QueryResult> result;
86 DuckDB db(nullptr);
87 Connection con(db);
88 con.EnableQueryVerification();
89
90 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(idx INTEGER, fmt STRING, pint INTEGER, pstring STRING)"));
91 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES (1, '%d: %s', 10, 'hello')"));
92 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES (2, 'blabla %d blabla %s', 20, 'blabla')"));
93 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES (3, NULL, 30, 'abcde')"));
94
95 // printf without format specifiers: too few parameters
96 REQUIRE_FAIL(con.Query("SELECT printf(fmt) FROM strings ORDER BY idx"));
97
98 result = con.Query("SELECT printf(CASE WHEN pint < 15 THEN NULL ELSE pint END) FROM strings ORDER BY idx");
99 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "20", "30"}));
100
101 // standard vectorized printf
102 result = con.Query("SELECT printf(fmt, pint, pstring) FROM strings ORDER BY idx");
103 REQUIRE(CHECK_COLUMN(result, 0, {"10: hello", "blabla 20 blabla blabla", Value()}));
104
105 // printf with constants in format arguments
106 result = con.Query("SELECT printf(fmt, 10, pstring) FROM strings ORDER BY idx");
107 REQUIRE(CHECK_COLUMN(result, 0, {"10: hello", "blabla 10 blabla blabla", Value()}));
108
109 // printf with constant format string
110 result = con.Query("SELECT printf('%s: %s', pstring, pstring) FROM strings ORDER BY idx");
111 REQUIRE(CHECK_COLUMN(result, 0, {"hello: hello", "blabla: blabla", "abcde: abcde"}));
112
113 // printf with selection vector
114 result = con.Query("SELECT printf('%s: %s', pstring, pstring) FROM strings WHERE idx <> 2 ORDER BY idx");
115 REQUIRE(CHECK_COLUMN(result, 0, {"hello: hello", "abcde: abcde"}));
116}
117
118TEST_CASE("Test scalar format", "[printf]") {
119 unique_ptr<QueryResult> result;
120 DuckDB db(nullptr);
121 Connection con(db);
122 con.EnableQueryVerification();
123
124 // format without format specifiers
125 result = con.Query("SELECT format('hello'), format(NULL)");
126 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
127 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
128
129 // format strings
130 result = con.Query("SELECT format('{}', 'hello'), format('{}: {}', 'hello', 'world')");
131 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
132 REQUIRE(CHECK_COLUMN(result, 1, {"hello: world"}));
133
134 // format strings with NULL values
135 result = con.Query("SELECT format('{}', NULL), format(NULL, 'hello', 'world')");
136 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
137 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
138
139 // booleans
140 result = con.Query("SELECT format('{} {}', TRUE, FALSE)");
141 REQUIRE(CHECK_COLUMN(result, 0, {"true false"}));
142
143 // integers
144 result = con.Query("SELECT format('{}', 33), format('{} + {} = {}', 3, 5, 3 + 5)");
145 REQUIRE(CHECK_COLUMN(result, 0, {"33"}));
146 REQUIRE(CHECK_COLUMN(result, 1, {"3 + 5 = 8"}));
147
148 // integers with special formatting specifiers
149 result = con.Query("SELECT format('{:04d}', 33), format('{} {:02d}:{:02d}:{:02d} {}', 'time', 12, 3, 16, 'AM'), "
150 "format('{:10d}', 1992)");
151 REQUIRE(CHECK_COLUMN(result, 0, {"0033"}));
152 REQUIRE(CHECK_COLUMN(result, 1, {"time 12:03:16 AM"}));
153 REQUIRE(CHECK_COLUMN(result, 2, {" 1992"}));
154
155 // numeric input of arguments
156 result = con.Query("SELECT format('{1} {1} {0} {0}', 1, 2)");
157 REQUIRE(CHECK_COLUMN(result, 0, {"2 2 1 1"}));
158
159 // incorrect number of parameters
160 // too few parameters
161 REQUIRE_FAIL(con.Query("SELECT format('{}')"));
162 REQUIRE_FAIL(con.Query("SELECT format('{} {}', 'hello')"));
163 // excess parameters are ignored
164 result = con.Query("SELECT format('{}', 'hello', 'world')");
165 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
166 // incorrect types
167 REQUIRE_FAIL(con.Query("SELECT format('{:s}', 42)"));
168 REQUIRE_FAIL(con.Query("SELECT format('{:d}', 'hello')"));
169}
170