1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test arithmetic statements", "[arithmetic]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
15
16 // comparisons involving arithmetic
17 // these are interesting because these will be folded by optimizers
18 // so we test if the optimizers work correctly
19 // addition is unordered (i.e. i+2=2+i)
20 // i+2=5 => i=3
21 result = con.Query("SELECT i+2=5, 5=i+2 FROM integers ORDER BY i");
22 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
23 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
24 // 2+i=5 => i=3
25 result = con.Query("SELECT 2+i=5, 5=2+i FROM integers ORDER BY i");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
27 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
28 // multiplication is unordered
29 // i*2=6 => i=3
30 result = con.Query("SELECT i*2=6, 6=i*2 FROM integers ORDER BY i");
31 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
32 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
33 // 2*i=6 => i=3
34 result = con.Query("SELECT 2*i=6, 6=2*i FROM integers ORDER BY i");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
36 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
37 // i*2=5 (this comparison is always FALSE, except if i is NULL in which case it is NULL)
38 result = con.Query("SELECT i*2=5 FROM integers ORDER BY i");
39 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, false}));
40 // i*0=5
41 result = con.Query("SELECT i*0=5 FROM integers ORDER BY i");
42 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, false}));
43 // -i>-2 => i<2
44 result = con.Query("SELECT -i>-2 FROM integers ORDER BY i");
45 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, false, false}));
46 // subtraction is ordered
47 // i-2=1 => i=3
48 result = con.Query("SELECT i-2=1, 1=i-2 FROM integers ORDER BY i");
49 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
50 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
51 // 3-i=1 => i=2
52 result = con.Query("SELECT 3-i=1, 1=3-i FROM integers ORDER BY i");
53 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true, false}));
54 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, false}));
55 // non-equality comparisons should also be flipped in this case
56 // 3-i<2 => i>2
57 result = con.Query("SELECT 3-i<2, 2>3-i FROM integers ORDER BY i");
58 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true, true}));
59 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
60 result = con.Query("SELECT 3-i<=1, 1>=3-i FROM integers ORDER BY i");
61 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true, true}));
62 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
63 // division is ordered
64 // i/2=1 => i>=2 or i<=3
65 result = con.Query("SELECT i/2=1, 1=i/2 FROM integers ORDER BY i");
66 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true, true}));
67 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
68 // 2/i=1 => i=2
69 result = con.Query("SELECT 2/i=1, 1=2/i FROM integers ORDER BY i");
70 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true, false}));
71 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, false}));
72 // 3/i=2 => i=2
73 result = con.Query("SELECT 2/i=1, 1=2/i FROM integers ORDER BY i");
74 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true, false}));
75 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, false}));
76}
77
78TEST_CASE("SQLogicTest inspired arithmetic tests", "[arithmetic]") {
79 unique_ptr<QueryResult> result;
80 DuckDB db(nullptr);
81 Connection con(db);
82 con.EnableQueryVerification();
83
84 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER);"));
85 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tab1(col0 INTEGER, col1 INTEGER, col2 INTEGER);"));
86 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tab2(col0 INTEGER, col1 INTEGER, col2 INTEGER);"));
87 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab0 VALUES(97,1,99);"));
88 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab0 VALUES(15,81,47);"));
89 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab0 VALUES(87,21,10);"));
90 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab1 VALUES(51,14,96);"));
91 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab1 VALUES(85,5,59);"));
92 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab1 VALUES(91,47,68);"));
93 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab2 VALUES(64,77,40);"));
94 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab2 VALUES(75,67,58);"));
95 REQUIRE_NO_FAIL(con.Query("INSERT INTO tab2 VALUES(46,51,23);"));
96
97 result = con.Query("SELECT DISTINCT - col2 AS col2 FROM tab1 WHERE NOT 18 BETWEEN NULL AND ( + col0 * + CAST ( "
98 "NULL AS INTEGER ) + - 3 / col2 ) OR NOT col0 BETWEEN col2 + + col1 AND NULL ORDER BY 1 DESC;");
99 REQUIRE(CHECK_COLUMN(result, 0, {-68, -96}));
100
101 result = con.Query("SELECT MIN ( DISTINCT + CAST ( NULL AS INTEGER ) ) * COUNT ( * ) * - + 16 * CASE + + AVG ( ALL "
102 "97 ) WHEN ( + NULLIF ( SUM ( CAST ( NULL AS REAL ) ), 6 ) ) THEN 51 * 31 + - 6 WHEN + 48 * - "
103 "34 THEN NULL WHEN 91 * + ( SUM ( CAST ( NULL AS INTEGER ) ) ) THEN NULL END * - 4 + - 67;");
104 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
105}
106