def test_isnull_1_0(table):
expr = table.g.isnull().ifelse(1, 0)
result = translate(expr)
expected = 'CASE WHEN `g` IS NULL THEN 1 ELSE 0 END'
assert result == expected
# inside some other function
result = translate(expr.sum())
expected = 'sum(CASE WHEN `g` IS NULL THEN 1 ELSE 0 END)'
assert result == expected
def test_timestamp_deltas(table, unit, compiled_unit):
f = '`i`'
K = 5
offset = ibis.interval(**{unit: K})
add_expr = table.i + offset
result = translate(add_expr)
assert result == f'date_add({f}, INTERVAL {K} {compiled_unit})'
sub_expr = table.i - offset
result = translate(sub_expr)
assert result == f'date_sub({f}, INTERVAL {K} {compiled_unit})'
def test_where_use_if(table):
expr = ibis.where(table.f > 0, table.e, table.a)
assert isinstance(expr, ir.FloatingValue)
result = translate(expr)
expected = "if(`f` > 0, `e`, `a`)"
assert result == expected
def test_hash(table):
expr = table.int_col.hash()
assert isinstance(expr, ir.IntegerColumn)
assert isinstance(table.int_col.sum().hash(), ir.IntegerScalar)
expr = table.int_col.hash()
expected = 'fnv_hash(`int_col`)'
assert translate(expr) == expected
def test_reduction_where(table, expr_fn, func_name):
expr = expr_fn(table)
result = translate(expr)
expected = (
f'{func_name}'
'(CASE WHEN `bigint_col` < 70 THEN `double_col` '
'ELSE NULL END)'
)
assert result == expected
def test_search_case(search_case):
expr = search_case
result = translate(expr)
expected = """CASE
WHEN `f` > 0 THEN `d` * 2
WHEN `c` < 0 THEN `a` * 2
ELSE CAST(NULL AS bigint)
END"""
assert result == expected
def test_simple_case(simple_case):
expr = simple_case
result = translate(expr)
expected = """CASE `g`
WHEN 'foo' THEN 'bar'
WHEN 'baz' THEN 'qux'
ELSE 'default'
END"""
assert result == expected
def test_numeric_unary_builtins(ibis_name, cname, table):
sql_name = IBIS_TO_SQL_NAMES.get(ibis_name, ibis_name)
method = getattr(table[cname], ibis_name)
expr = method()
expected = f'{sql_name}(`{cname}`)'
result = translate(expr)
assert result == expected
def test_column_ref_table_aliases():
context = ImpalaCompiler.make_context()
table1 = ibis.table([('key1', 'string'), ('value1', 'double')])
table2 = ibis.table([('key2', 'string'), ('value and2', 'double')])
context.set_ref(table1, 't0')
context.set_ref(table2, 't1')
expr = table1['value1'] - table2['value and2']
result = translate(expr, context=context)
expected = 't0.`value1` - t1.`value and2`'
assert result == expected
def test_correlated_predicate_subquery(table):
t0 = table
t1 = t0.view()
expr = t0.g == t1.g
ctx = ImpalaCompiler.make_context()
ctx.make_alias(t0)
# Grab alias from parent context
subctx = ctx.subcontext()
subctx.make_alias(t1)
subctx.make_alias(t0)
result = translate(expr, context=subctx)
expected = "t0.`g` = t1.`g`"
assert result == expected
def test_identifier_quoting():
schema = [('date', 'double'), ('table', 'string')]
table = ibis.table(schema)
translate(table['date'], named='`date`')
translate(table['table'], named='`table`')
def test_column_ref_quoting():
schema = [('has a space', 'double')]
table = ibis.table(schema)
translate(table['has a space'], named='`has a space`')
def test_decimal_builtins(mockcon, expr_fn, expected):
t = mockcon.table('tpch_lineitem')
col = t.l_extendedprice
expr = expr_fn(col)
result = translate(expr)
assert result == expected
def test_any_all(table, expr_fn, expected):
expr = expr_fn(table.f == 0)
result = translate(expr)
assert result == expected
def test_decimal_casts(table, expr_fn, expected):
expr = expr_fn(table)
result = translate(expr)
assert result == expected
def test_find(table):
expr = table.string_col.find('a', start=table.tinyint_col)
expected = "locate('a', `string_col`, `tinyint_col` + 1) - 1"
assert translate(expr) == expected
def test_casts(table, column, to_type, expected_type):
expr = table[column].cast(to_type)
result = translate(expr)
assert result == f"CAST(`{column}` AS {expected_type})"
def test_literals(value, expected):
expr = L(value)
result = translate(expr)
assert result == expected
def test_timestamp_now():
expr = ibis.now()
result = translate(expr)
assert result == "now()"
def test_timestamp_extract_field(table, field):
expr = getattr(table.i, field)()
expected = f"extract(`i`, '{field}')"
result = translate(expr)
assert result == expected
def test_negate(table, expr_fn, expected):
expr = expr_fn(table)
result = translate(expr)
assert result == expected
def test_isnull_notnull(table, expr_fn, expected):
expr = expr_fn(table)
result = translate(expr)
assert result == expected
def test_bucket_to_case(table, expr_fn, expected):
expr = expr_fn(table.f)
result = translate(expr)
assert result == expected
def test_misc_conditionals(table):
expr = table.a.nullif(0)
expected = 'nullif(`a`, 0)'
result = translate(expr)
assert result == expected
def test_numeric(expr_fn, expected, table):
expr = expr_fn(table)
result = translate(expr)
assert result == expected
def test_timestamp_literals(expr_fn, expected):
expr = expr_fn('2015-01-01 12:34:56')
result = translate(expr)
assert result == expected
def test_string_builtins(table, expr_fn, expected):
expr = expr_fn(table.string_col)
assert translate(expr) == expected
def test_timestamp_day_of_week(expr_fn, expected):
expr = expr_fn(ibis.timestamp('2015-09-01T01:00:23'))
result = translate(expr)
assert result == expected
def test_string_join():
expr = L(',').join(['a', 'b'])
expected = "concat_ws(',', 'a', 'b')"
assert translate(expr) == expected
def test_timestamp_from_integer(table, expr_fn, expected):
expr = expr_fn(table.c)
result = translate(expr)
assert result == expected
