def test_like_from_pr16(self):
result = format({
"from": "trade",
"where": {
"and": [
{
"like": ["school", {
"literal": "%shool"
}]
},
{
"eq": ["name", {
"literal": "abc"
}]
},
{
"in": ["id", {
"literal": ["1", "2"]
}]
},
]
},
"select": "*",
})
expected = (
"SELECT * FROM trade WHERE school LIKE '%shool' AND name = 'abc' AND id IN"
" ('1', '2')")
self.assertEqual(result, expected)
def test_where_in_and_in(self):
result = format({
"select": {
"value": "a"
},
"from": "dual",
"where": {
"and": [
{
"in": [
"a",
{
"literal": ["r", "g", "b"]
},
]
},
{
"in": [
"b",
[10, 11, 12],
]
},
]
},
})
expected = "SELECT a FROM dual WHERE a IN ('r', 'g', 'b') AND b IN (10, 11, 12)"
self.assertEqual(result, expected)
def test_dot_table_name(self):
result = format({
"select": "*",
"from": "SYS.XYZZY",
})
expected = "SELECT * FROM SYS.XYZZY"
self.assertEqual(result, expected)
def test_with_cte_various(self):
expected = (
"WITH t1 AS (SELECT a FROM table), t2 AS (SELECT 1) SELECT * FROM t1, t2"
)
result = format({
"select":
"*",
"from": ["t1", "t2"],
"with": [
{
"name": "t1",
"value": {
"select": {
"value": "a"
},
"from": "table"
}
},
{
"name": "t2",
"value": {
"select": {
"value": 1
}
}
},
],
})
self.assertEqual(result, expected)
def test_bad_join_name(self):
bad_json = {
"select": {
"value": "t1.field1"
},
"from": [
"t1", {
"left intro join": "t2",
"on": {
"eq": ["t1.id", "t2.id"]
}
}
],
}
with self.assertRaises(["Fail to detect join type",
"left intro join"]):
format(bad_json)
def test_select_dots_names(self):
result = format({
"select": {
"value": "a.b.c._d"
},
"from": "dual",
})
expected = "SELECT a.b.c._d FROM dual"
self.assertEqual(result, expected)
def test_select_underscore_name(self):
result = format({
"select": {
"value": "_id"
},
"from": "dual",
})
expected = "SELECT _id FROM dual"
self.assertEqual(result, expected)
def test_offset(self):
result = format({
"select": "*",
"from": "a",
"limit": 10,
"offset": 10
})
expected = "SELECT * FROM a LIMIT 10 OFFSET 10"
self.assertEqual(result, expected)
def select_one_column(self):
result = format({
"select": [{
"value": "A"
}],
"from": ["dual"],
})
expected = "SELECT A FROM dual"
self.assertEqual(result, expected)
def test_binary_not(self):
expected = "SELECT * FROM t WHERE ~c"
result = format({
"select": "*",
"from": "t",
"where": {
"binary_not": "c"
}
})
self.assertEqual(result, expected)
def test_binary_or(self):
expected = "SELECT * FROM t WHERE c | 4"
result = format({
"select": "*",
"from": "t",
"where": {
"binary_or": ["c", 4]
}
})
self.assertEqual(result, expected)
def test_select_quote(self):
result = format({
"select": {
"value": {
"literal": "'"
}
},
"from": "dual",
})
expected = "SELECT '''' FROM dual"
self.assertEqual(result, expected)
def test_function(self):
result = format({
"select": {
"value": {
"count": 1
}
},
"from": "mytable",
})
expected = "SELECT COUNT(1) FROM mytable"
self.assertEqual(result, expected)
def test_negative_number(self):
result = format({
"from": "table1",
"where": {
"eq": ["A", -900]
},
"select": {
"value": "a"
},
})
expected = "SELECT a FROM table1 WHERE A = -900"
self.assertEqual(result, expected)
def test_where_neq(self):
result = format({
"select": "*",
"from": "dual",
"where": {
"neq": ["a", {
"literal": "test"
}]
},
})
expected = "SELECT * FROM dual WHERE a <> 'test'"
self.assertEqual(result, expected)
def test_count_literal(self):
result = format({
"select": {
"value": {
"count": {
"literal": "literal"
}
}
},
"from": "a",
})
expected = "SELECT COUNT('literal') FROM a"
self.assertEqual(result, expected)
def test_order_by(self):
result = format({
"select": {
"value": {
"count": 1
}
},
"from": "dual",
"orderby": {
"value": "a"
},
})
expected = "SELECT COUNT(1) FROM dual ORDER BY a"
self.assertEqual(result, expected)
def test_rlike_in_where(self):
result = format({
"from": "table1",
"where": {
"rlike": ["A", {
"literal": ".*20.*"
}]
},
"select": {
"value": "a"
},
})
expected = "SELECT a FROM table1 WHERE A RLIKE '.*20.*'"
self.assertEqual(result, expected)
def test_between(self):
result = format({
"select": [{
"value": "a"
}],
"from": ["t1"],
"where": {
"between": ["t1.a", 10, {
"literal": "ABC"
}]
},
})
expected = "SELECT a FROM t1 WHERE t1.a BETWEEN 10 AND 'ABC'"
self.assertEqual(result, expected)
def test_union(self):
result = format({
"union": [
{
"select": "*",
"from": "a"
},
{
"select": "*",
"from": "b"
},
],
})
expected = "SELECT * FROM a UNION SELECT * FROM b"
self.assertEqual(result, expected)
def test_with_cte(self):
expected = "WITH t AS (SELECT a FROM table) SELECT * FROM t"
result = format({
"select": "*",
"from": "t",
"with": {
"name": "t",
"value": {
"select": {
"value": "a"
},
"from": "table"
}
},
})
self.assertEqual(result, expected)
def select_many_column(self):
result = format({
"select": [
{
"value": "a"
},
{
"value": "b"
},
{
"value": "c"
},
],
"from": ["dual"],
})
expected = "SELECT a, b, c FROM dual"
self.assertEqual(result, expected)
def test_eq(self):
result = format({
"select": [
{
"value": "a"
},
{
"value": "b"
},
],
"from": ["t1", "t2"],
"where": {
"eq": ["t1.a", "t2.b"]
},
})
expected = "SELECT a, b FROM t1, t2 WHERE t1.a = t2.b"
self.assertEqual(result, expected)
def test_in_expression(self):
result = format({
"from": "task",
"select": "*",
"where": {
"in": [
"repo.branch.name",
{
"literal": ["try", "mozilla-central"]
},
]
},
})
expected = (
"SELECT * FROM task WHERE repo.branch.name IN ('try', 'mozilla-central')"
)
self.assertEqual(result, expected)
def test_select_quoted_name(self):
result = format({
"select": [
{
"name": "@*#&",
"value": "a"
},
{
"name": "test.g.g.c",
"value": "b"
},
],
"from":
"dual",
})
expected = 'SELECT a AS "@*#&", b AS test.g.g.c FROM dual'
self.assertEqual(result, expected)
def test_is_not_null(self):
result = format({
"select": [
{
"value": "a"
},
{
"value": "b"
},
],
"from": "t1",
"where": {
"exists": "t1.a"
},
})
expected = "SELECT a, b FROM t1 WHERE t1.a IS NOT NULL"
self.assertEqual(result, expected)
def test_neg_or_precedence(self):
result = format({
"from": "table1",
"where": {
"or": [{
"eq": ["A", -900]
}, {
"eq": ["B", 100]
}]
},
"select": [{
"value": "B"
}, {
"value": "C"
}],
})
expected = "SELECT B, C FROM table1 WHERE A = -900 OR B = 100"
self.assertEqual(result, expected)
def test_not_equal(self):
result = format({
"select": "*",
"from": "task",
"where": {
"and": [
{
"exists": "build.product"
},
{
"neq": ["build.product", {
"literal": "firefox"
}]
},
]
},
})
expected = (
"SELECT * FROM task WHERE build.product IS NOT NULL AND build.product <>"
" 'firefox'")
self.assertEqual(result, expected)
def test_groupby(self):
result = format({
"select": [
{
"value": "a"
},
{
"name": "b",
"value": {
"count": 1
}
},
],
"from":
"mytable",
"groupby": {
"value": "a"
},
})
expected = "SELECT a, COUNT(1) AS b FROM mytable GROUP BY a"
self.assertEqual(result, expected)
def test_select_expression(self):
result = format({
"select": {
"value": {
"add": [
"a",
{
"div": ["b", 2]
},
{
"mul": [45, "c"]
},
{
"div": [2, "d"]
},
]
}
},
"from": "dual",
})
expected = "SELECT a + b / 2 + 45 * c + 2 / d FROM dual"
self.assertEqual(result, expected)
