JSqlParser/src/test/java/net/sf/jsqlparser/expression/CaseExpressionTest.java at b185370e50658ddec3b792434c2f5d87cf180a41 · amigalev/JSqlParser · GitHub

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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
/*-
 * #%L
 * JSQLParser library
 * %%
 * Copyright (C) 2004 - 2022 JSQLParser
 * %%
 * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
 * #L%
 */
package net.sf.jsqlparser.expression;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.test.TestUtils;
import org.junit.jupiter.api.Test;

/**
 * @author Mathieu Goeminne
 */
public class CaseExpressionTest {
    @Test
    public void testSimpleCase() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseBinaryAndWhen() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN true & false THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseBinaryOrWhen() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN true | false THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseExclamationWhen() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN !true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseNotWhen() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN NOT true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseAndWhen() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN true AND false THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseOrWhen() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true WHEN true OR false THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseExclamationSwitch() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE !true WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseNotSwitch() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE NOT true WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseBinaryAndSwitch() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true & false WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseBinaryOrSwitch() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true | false WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseAndSwitch() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true AND false WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testCaseOrSwitch() throws JSQLParserException {
        TestUtils.assertExpressionCanBeParsedAndDeparsed("CASE true OR false WHEN true THEN 1 ELSE 2 END", true);
    }

    @Test
    public void testInnerCaseWithConcatInElsePart() throws JSQLParserException {
        String query = "SELECT \n" +
                "CASE \n" +
                "   WHEN 1 = 1 \n" +
                "   THEN \n" +
                "       CASE \n" +
                "           WHEN 2 = 2 \n" +
                "           THEN '2a' \n" +
                "           ELSE \n" +
                "               CASE \n" +
                "                   WHEN 1 = 1 \n" +
                "                   THEN \n" +
                "                       CASE \n" +
                "                           WHEN 2 = 2 \n" +
                "                           THEN '2a' \n" +
                "                           ELSE '' \n" +
                "                       END \n" +
                "                   ELSE 'b' \n" +
                "               END || 'z'\n" +
                "       END \n" +
                "   ELSE 'b' \n" +
                "END AS tmp\n" +
                "FROM test_table";
        TestUtils.assertSqlCanBeParsedAndDeparsed(query, true);
    }

    @Test
    public void testCaseInsideBrackets() throws JSQLParserException {
        String sqlStr = "SELECT ( CASE\n"
                        + "            WHEN something\n"
                        + "                THEN CASE\n"
                        + "                     WHEN something2\n"
                        + "                         THEN 1\n"
                        + "                     ELSE 0\n"
                        + "                     END + 1\n"
                        + "            ELSE 0\n"
                        + "        END ) + 1 \n"
                        + "FROM test";

        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);

        sqlStr = "SELECT\n"
                 + "(CASE WHEN FIELD_A=0 THEN FIELD_B\n"
                 + "WHEN FIELD_C >FIELD_D  THEN (CASE WHEN FIELD_A>0 THEN\n"
                 + "(FIELD_B)/(FIELD_A/(DATEDIFF(DAY,:started,:end)+1))\n"
                 + "ELSE 0 END)-FIELD_D ELSE 0 END)*FIELD_A/(DATEDIFF(DAY,:started,:end)+1)  AS UNNECESSARY_COMPLEX_EXPRESSION\n"
                 + "FROM TEST";

        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }
}