def test__function_between(self):
c1 = fn.Coalesce(Field('foo'), 0)[0:1]
c2 = fn.Coalesce(Field('foo', table=self.t), 0)[0:1]
self.assertEqual('COALESCE("foo",0) BETWEEN 0 AND 1', str(c1))
self.assertEqual('COALESCE("btw"."foo",0) BETWEEN 0 AND 1', str(c2))
def test__mixed_case_is_not_aggregate(self):
v = Case() \
.when(Field('foo') == 1, fn.Sum(Field('bar'))) \
.when(Field('foo') == 2, Field('fiz'))
self.assertFalse(v.is_aggregate)
def test_to_char(self):
db = Database()
to_char = db.to_char(Field('field'))
self.assertEqual(str(to_char), 'CAST("field" AS VARCHAR)')
def test__negative_agg_func_is_aggregate(self):
v = Negative(fn.Sum(Field('foo')))
self.assertTrue(v.is_aggregate)
def test__mixed_func_arithmetic_is_not_aggregate(self):
v = Field('foo') / fn.Sum(Field('foo'))
self.assertFalse(v.is_aggregate)
def test_trunc_quarter(self):
result = SnowflakeDatabase().trunc_date(Field('date'), 'quarter')
self.assertEqual('TRUNC("date",\'Q\')', str(result))
def test__field_arithmetic_is_not_aggregate(self):
v = Field('foo') + Field('bar')
self.assertFalse(v.is_aggregate)
def test__criterion_eq_str(self):
c1 = Field('foo') == 'abc'
c2 = Field('foo', table=self.t).eq('abc')
self.assertEqual('"foo"=\'abc\'', str(c1))
self.assertEqual('"crit"."foo"=\'abc\'', str(c2))
def test__nested__or(self):
c = (Field('foo') == 1) | (Field('bar') == 2) | (Field('buz') == 3)
self.assertEqual('"foo"=1 OR "bar"=2 OR "buz"=3', str(c))
def test__function_notin(self):
c1 = fn.Coalesce(Field('foo'), 0).notin([0, 1])
c2 = fn.Coalesce(Field('foo', table=self.t), 0).notin([0, 1])
self.assertEqual('COALESCE("foo",0) NOT IN (0,1)', str(c1))
self.assertEqual('COALESCE("notin"."foo",0) NOT IN (0,1)', str(c2))
def test__nested__and(self):
c = (Field('foo') == 1) & (Field('bar') == 2) & (Field('buz') == 3)
self.assertEqual('"foo"=1 AND "bar"=2 AND "buz"=3', str(c))
def test__notin_character(self):
c1 = Field('foo').notin(['a', 'b'])
c2 = Field('foo', table=self.t).notin(['a', 'b'])
self.assertEqual('"foo" NOT IN (\'a\',\'b\')', str(c1))
self.assertEqual('"notin"."foo" NOT IN (\'a\',\'b\')', str(c2))
def test__notin_number(self):
c1 = Field('foo').notin([0, 1])
c2 = Field('foo', table=self.t).notin([0, 1])
self.assertEqual('"foo" NOT IN (0,1)', str(c1))
self.assertEqual('"notin"."foo" NOT IN (0,1)', str(c2))
def test__in_unicode(self):
c1 = Field('foo').isin([u'a', u'b'])
c2 = Field('foo', table=self.t).isin([u'a', u'b'])
self.assertEqual('"foo" IN (\'a\',\'b\')', str(c1))
self.assertEqual('"isin"."foo" IN (\'a\',\'b\')', str(c2))
def test_date_add_year(self):
result = SnowflakeDatabase().date_add(Field('date'), 'year', 1)
self.assertEqual('TIMESTAMPADD(\'year\',1,"date")', str(result))
def test__nested__mixed(self):
c = ((Field('foo') == 1) & (Field('bar') == 2)) | (Field('buz') == 3)
self.assertEqual('("foo"=1 AND "bar"=2) OR "buz"=3', str(c))
def test_trunc_week(self):
result = SnowflakeDatabase().trunc_date(Field('date'), 'week')
self.assertEqual('TRUNC("date",\'IW\')', str(result))
def test__between_and_isin(self):
c = Field('foo').isin([1, 2, 3]) & Field('bar').between(0, 1)
self.assertEqual('"foo" IN (1,2,3) AND "bar" BETWEEN 0 AND 1', str(c))
def test__field_is_not_aggregate(self):
v = Field('foo')
self.assertFalse(v.is_aggregate)
def test__criterion_eq_datetime(self):
c1 = Field('foo') == datetime(2000, 1, 1, 12, 30, 55)
c2 = Field('foo', table=self.t).eq(datetime(2000, 1, 1, 12, 30, 55))
self.assertEqual('"foo"=\'2000-01-01T12:30:55\'', str(c1))
self.assertEqual('"crit"."foo"=\'2000-01-01T12:30:55\'', str(c2))
def test__field_arithmetic_constant_is_not_aggregate(self):
v = Field('foo') + 1
self.assertFalse(v.is_aggregate)
def test__criterion_eq_right(self):
c1 = 1 == Field('foo')
c2 = -1 == Field('foo', table=self.t)
self.assertEqual('"foo"=1', str(c1))
self.assertEqual('"crit"."foo"=-1', str(c2))
def test__agg_func_arithmetic_is_aggregate(self):
v = fn.Sum(Field('foo')) / fn.Sum(Field('foo'))
self.assertTrue(v.is_aggregate)
def test__criterion_is_null(self):
c1 = Field('foo').isnull()
c2 = Field('foo', table=self.t).isnull()
self.assertEqual('"foo" IS NULL', str(c1))
self.assertEqual('"crit"."foo" IS NULL', str(c2))
def test__func_arithmetic_constant_is_not_aggregate(self):
v = 1 / fn.Sum(Field('foo'))
self.assertTrue(v.is_aggregate)
def test__criterion_ne_str(self):
c1 = Field('foo') != 'abc'
c2 = Field('foo', table=self.t).ne('abc')
self.assertEqual('"foo"<>\'abc\'', str(c1))
self.assertEqual('"crit"."foo"<>\'abc\'', str(c2))
def test_use_different_table_objects_for_same_table(self):
table = Table("t")
q = Query.from_(table).select('*').where(Field('id', table=table) == 1)
self.assertEqual('SELECT * FROM "t" WHERE "id"=1', str(q))
def test__criterion_ne_date(self):
c1 = Field('foo') != date(2000, 1, 1)
c2 = Field('foo', table=self.t).ne(date(2000, 1, 1))
self.assertEqual('"foo"<>\'2000-01-01\'', str(c1))
self.assertEqual('"crit"."foo"<>\'2000-01-01\'', str(c2))
def test_to_fixed_string_field(self):
self.assertEqual(
ToFixedString(Field('field_name'), 100).get_sql(),
'toFixedString("field_name",100)'
)
def test__criterion_eq_decimal(self):
c1 = Field('foo') == 1.0
c2 = Field('foo', table=self.t).eq(0.5)
self.assertEqual('"foo"=1.0', str(c1))
self.assertEqual('"crit"."foo"=0.5', str(c2))
