polyglot-sql 0.5.15

SQL parsing, validating, formatting, and dialect translation library
Documentation 
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

use polyglot_sql::{Dialect, DialectType, TranspileOptions};

fn pg_to_target(sql: &str, target: DialectType) -> String {
    Dialect::get(DialectType::PostgreSQL)
        .transpile_with(sql, target, TranspileOptions::strict())
        .unwrap_or_else(|err| panic!("transpile failed for {sql:?} to {target:?}: {err}"))
        .into_iter()
        .next()
        .expect("expected one generated statement")
}

#[test]
fn negated_boolean_tests_preserve_null_rows_in_predicate_context() {
    let cases = [
        (
            "SELECT d FROM t WHERE b IS NOT FALSE",
            "SELECT d FROM t WHERE b = 1 OR b IS NULL",
        ),
        (
            "SELECT d FROM t WHERE b IS NOT TRUE",
            "SELECT d FROM t WHERE b = 0 OR b IS NULL",
        ),
    ];

    for target in [DialectType::TSQL, DialectType::Fabric] {
        for (sql, expected) in cases {
            assert_eq!(pg_to_target(sql, target), expected, "failed for {target:?}");
        }
    }
}

#[test]
fn non_negated_boolean_tests_remain_simple_predicates() {
    let cases = [
        (
            "SELECT d FROM t WHERE b IS TRUE",
            "SELECT d FROM t WHERE b = 1",
        ),
        (
            "SELECT d FROM t WHERE b IS FALSE",
            "SELECT d FROM t WHERE b = 0",
        ),
    ];

    for target in [DialectType::TSQL, DialectType::Fabric] {
        for (sql, expected) in cases {
            assert_eq!(pg_to_target(sql, target), expected, "failed for {target:?}");
        }
    }
}

#[test]
fn scalar_boolean_tests_are_definite_bit_values() {
    let cases = [
        (
            "SELECT b IS TRUE AS istrue FROM t",
            "SELECT CAST(CASE WHEN b = 1 THEN 1 ELSE 0 END AS BIT) AS istrue FROM t",
        ),
        (
            "SELECT b IS FALSE AS isfalse FROM t",
            "SELECT CAST(CASE WHEN b = 0 THEN 1 ELSE 0 END AS BIT) AS isfalse FROM t",
        ),
        (
            "SELECT b IS NOT TRUE AS isnt FROM t",
            "SELECT CAST(CASE WHEN b = 0 OR b IS NULL THEN 1 ELSE 0 END AS BIT) AS isnt FROM t",
        ),
        (
            "SELECT b IS NOT FALSE AS isnf FROM t",
            "SELECT CAST(CASE WHEN b = 1 OR b IS NULL THEN 1 ELSE 0 END AS BIT) AS isnf FROM t",
        ),
        (
            "SELECT b IS UNKNOWN AS isu FROM t",
            "SELECT CAST(CASE WHEN b IS NULL THEN 1 ELSE 0 END AS BIT) AS isu FROM t",
        ),
    ];

    for target in [DialectType::TSQL, DialectType::Fabric] {
        for (sql, expected) in cases {
            assert_eq!(pg_to_target(sql, target), expected, "failed for {target:?}");
        }
    }
}

#[test]
fn boolean_tests_on_predicate_operands_do_not_compare_predicates_to_integers() {
    let cases = [
        (
            "SELECT d FROM t WHERE (a > 1) IS NOT FALSE",
            "SELECT d FROM t WHERE CASE WHEN NOT (a > 1) THEN 0 ELSE 1 END = 1",
        ),
        (
            "SELECT d FROM t WHERE (a > 1) IS NOT TRUE",
            "SELECT d FROM t WHERE CASE WHEN (a > 1) THEN 0 ELSE 1 END = 1",
        ),
        (
            "SELECT (a > 1) IS TRUE AS ok FROM t",
            "SELECT CAST(CASE WHEN (a > 1) THEN 1 ELSE 0 END AS BIT) AS ok FROM t",
        ),
    ];

    for target in [DialectType::TSQL, DialectType::Fabric] {
        for (sql, expected) in cases {
            assert_eq!(pg_to_target(sql, target), expected, "failed for {target:?}");
        }
    }
}