def test_where(lineitem):
table = lineitem
q = table.l_quantity
expr = api.where(table.l_discount > 0, q * table.l_discount, api.null())
assert isinstance(expr, ir.DecimalColumn)
expr = api.where(
table.l_discount > 0, (q * table.l_discount).sum(), api.null()
)
assert isinstance(expr, ir.DecimalColumn)
expr = api.where(
table.l_discount.sum() > 0, (q * table.l_discount).sum(), api.null()
)
assert isinstance(expr, ir.DecimalScalar)
def test_builtins_1(self):
table = self.alltypes
i1 = table.tinyint_col
i4 = table.int_col
i8 = table.bigint_col
d = table.double_col
s = table.string_col
exprs = [
api.now(),
api.e,
# hash functions
i4.hash(),
d.hash(),
s.hash(),
# modulus cases
i1 % 5,
i4 % 10,
20 % i1,
d % 5,
i1.zeroifnull(),
i4.zeroifnull(),
i8.zeroifnull(),
i4.to_timestamp('s'),
i4.to_timestamp('ms'),
i4.to_timestamp('us'),
i8.to_timestamp(),
d.abs(),
d.cast('decimal(12, 2)'),
d.cast('int32'),
d.ceil(),
d.exp(),
d.isnull(),
d.fillna(0),
d.floor(),
d.log(),
d.ln(),
d.log2(),
d.log10(),
d.notnull(),
d.zeroifnull(),
d.nullifzero(),
d.round(),
d.round(2),
d.round(i1),
i1.sign(),
i4.sign(),
d.sign(),
# conv
i1.convert_base(10, 2),
i4.convert_base(10, 2),
i8.convert_base(10, 2),
s.convert_base(10, 2),
d.sqrt(),
d.zeroifnull(),
# nullif cases
5 / i1.nullif(0),
5 / i1.nullif(i4),
5 / i4.nullif(0),
5 / d.nullif(0),
api.literal(5).isin([i1, i4, d]),
# tier and histogram
d.bucket([0, 10, 25, 50, 100]),
d.bucket([0, 10, 25, 50], include_over=True),
d.bucket([0, 10, 25, 50], include_over=True, close_extreme=False),
d.bucket([10, 25, 50, 100], include_under=True),
d.histogram(10),
d.histogram(5, base=10),
d.histogram(base=10, binwidth=5),
# coalesce-like cases
api.coalesce(table.int_col,
api.null(),
table.smallint_col,
table.bigint_col, 5),
api.greatest(table.float_col,
table.double_col, 5),
api.least(table.string_col, 'foo'),
# string stuff
s.contains('6'),
s.like('6%'),
s.re_search('[\d]+'),
s.re_extract('[\d]+', 0),
s.re_replace('[\d]+', 'a'),
s.repeat(2),
s.translate("a", "b"),
s.find("a"),
s.lpad(10, 'a'),
s.rpad(10, 'a'),
s.find_in_set(["a"]),
s.lower(),
s.upper(),
s.reverse(),
s.ascii_str(),
s.length(),
s.strip(),
s.lstrip(),
s.strip(),
# strings with int expr inputs
s.left(i1),
s.right(i1),
s.substr(i1, i1 + 2),
s.repeat(i1)
]
proj_exprs = [expr.name('e%d' % i)
for i, expr in enumerate(exprs)]
projection = table[proj_exprs]
projection.limit(10).execute()
self._check_impala_output_types_match(projection)
def test_builtins_1(self):
table = self.alltypes
i1 = table.tinyint_col
i4 = table.int_col
i8 = table.bigint_col
d = table.double_col
s = table.string_col
exprs = [
api.now(),
api.e,
# hash functions
i4.hash(),
d.hash(),
s.hash(),
# modulus cases
i1 % 5,
i4 % 10,
20 % i1,
d % 5,
i1.zeroifnull(),
i4.zeroifnull(),
i8.zeroifnull(),
i4.to_timestamp('s'),
i4.to_timestamp('ms'),
i4.to_timestamp('us'),
i8.to_timestamp(),
d.abs(),
d.cast('decimal(12, 2)'),
d.cast('int32'),
d.ceil(),
d.exp(),
d.isnull(),
d.fillna(0),
d.floor(),
d.log(),
d.ln(),
d.log2(),
d.log10(),
d.notnull(),
d.zeroifnull(),
d.nullifzero(),
d.round(),
d.round(2),
d.round(i1),
i1.sign(),
i4.sign(),
d.sign(),
# conv
i1.convert_base(10, 2),
i4.convert_base(10, 2),
i8.convert_base(10, 2),
s.convert_base(10, 2),
d.sqrt(),
d.zeroifnull(),
# nullif cases
5 / i1.nullif(0),
5 / i1.nullif(i4),
5 / i4.nullif(0),
5 / d.nullif(0),
api.literal(5).isin([i1, i4, d]),
# tier and histogram
d.bucket([0, 10, 25, 50, 100]),
d.bucket([0, 10, 25, 50], include_over=True),
d.bucket([0, 10, 25, 50], include_over=True, close_extreme=False),
d.bucket([10, 25, 50, 100], include_under=True),
d.histogram(10),
d.histogram(5, base=10),
d.histogram(base=10, binwidth=5),
# coalesce-like cases
api.coalesce(table.int_col, api.null(), table.smallint_col,
table.bigint_col, 5),
api.greatest(table.float_col, table.double_col, 5),
api.least(table.string_col, 'foo'),
# string stuff
s.contains('6'),
s.like('6%'),
s.re_search('[\d]+'),
s.re_extract('[\d]+', 0),
s.re_replace('[\d]+', 'a'),
s.repeat(2),
s.translate("a", "b"),
s.find("a"),
s.lpad(10, 'a'),
s.rpad(10, 'a'),
s.find_in_set(["a"]),
s.lower(),
s.upper(),
s.reverse(),
s.ascii_str(),
s.length(),
s.strip(),
s.lstrip(),
s.strip(),
# strings with int expr inputs
s.left(i1),
s.right(i1),
s.substr(i1, i1 + 2),
s.repeat(i1)
]
proj_exprs = [expr.name('e%d' % i) for i, expr in enumerate(exprs)]
projection = table[proj_exprs]
projection.limit(10).execute()
self._check_impala_output_types_match(projection)