def test_nested_array_join(self):
table = self._make_table(
't1', Column('x', types.Int32, primary_key=True),
Column(
'parent',
types.Nested(
Column('child1', types.Int32),
Column('child2', types.String),
)))
query = select([
table.c.parent.child1,
table.c.parent.child2,
]).array_join(table.c.parent)
self.assertEqual(
self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 '
'FROM t1 '
'ARRAY JOIN t1.parent')
query = select([
table.c.parent.child1,
table.c.parent.child2,
]).array_join(table.c.parent.child1, )
self.assertEqual(
self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 '
'FROM t1 '
'ARRAY JOIN t1.parent.child1')
query = select([
table.c.parent.child1,
table.c.parent.child2,
]).array_join(
table.c.parent.child1,
table.c.parent.child2,
)
self.assertEqual(
self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 '
'FROM t1 '
'ARRAY JOIN t1.parent.child1, t1.parent.child2')
parent_labeled = table.c.parent.label('p')
query = select([
table.c.parent.child1,
table.c.parent.child2,
]).array_join(parent_labeled)
self.assertEqual(
self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 '
'FROM t1 '
'ARRAY JOIN t1.parent AS p')
query = select([
parent_labeled.child1,
parent_labeled.child2,
table.c.parent.child1,
]).array_join(parent_labeled)
self.assertEqual(
self.compile(query),
'SELECT p.child1, p.child2, t1.parent.child1 AS child1_1 '
'FROM t1 '
'ARRAY JOIN t1.parent AS p')
def test_group_by_with_totals(self):
table = self.create_table()
query = select([table.c.x]).group_by(table.c.x)
self.assertEqual(self.compile(query), 'SELECT x FROM t1 GROUP BY x')
query = select([table.c.x]).group_by(table.c.x).with_totals()
self.assertEqual(self.compile(query),
'SELECT x FROM t1 GROUP BY x WITH TOTALS')
def test_final(self):
table = self._make_table()
query = select([table.c.x]).final().group_by(table.c.x)
self.assertEqual(
self.compile(query),
'SELECT t1.x FROM t1 FINAL GROUP BY t1.x'
)
def test_sample(self):
table = self.create_table()
query = select([table.c.x]).sample(0.1).group_by(table.c.x)
self.assertEqual(self.compile(query),
'SELECT x FROM t1 SAMPLE %(param_1)s GROUP BY x')
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x FROM t1 SAMPLE 0.1 GROUP BY x')
def test_limit_by(self):
table = self._make_table()
query = select([table.c.x]).order_by(table.c.x)\
.limit_by([table.c.x], limit=1)
self.assertEqual(
self.compile(query),
'SELECT t1.x FROM t1 ORDER BY t1.x LIMIT %(param_1)s BY t1.x')
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT t1.x FROM t1 ORDER BY t1.x LIMIT 1 BY t1.x')
def test_join_same_column_name(self):
table_1 = self.create_table(
'table_1', Column('x', types.UInt32, primary_key=True))
table_2 = self.create_table(
'table_2', Column('x', types.UInt32, primary_key=True))
join = table_1.join(table_2, table_1.c.x == table_2.c.x, type='INNER')
self.assertEqual(
self.compile(select([table_1.c.x]).select_from(join)),
'SELECT table_1.x FROM table_1 '
'INNER JOIN table_2 ON table_1.x = table_2.x')
def make_statement(type=None,
strictness=None,
distribution=None,
full=False,
isouter=False):
join = table_1.join(table_2,
table_2.c.y == table_1.c.x,
isouter=isouter,
full=full,
type=type,
strictness=strictness,
distribution=distribution)
return select([table_1.c.x]).select_from(join)
def test_nested_array_join_left(self):
table = self._make_table(
't1', Column('x', types.Int32, primary_key=True),
Column(
'parent',
types.Nested(
Column('child1', types.Int32),
Column('child2', types.String),
)))
query = select([
table.c.parent.child1,
table.c.parent.child2,
]).array_join(table.c.parent, left=True)
self.assertEqual(
self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 '
'FROM t1 '
'LEFT ARRAY JOIN t1.parent')
def test_nested_type(self):
table = self.create_table(
't1', Column('x', types.Int32, primary_key=True),
Column(
'parent',
types.Nested(
Column('child1', types.Int32),
Column('child2', types.String),
)))
query = select([table.c.parent.child1]).where(
and_(table.c.parent.child1 == [1, 2],
table.c.parent.child2 == ['foo', 'bar']))
self.assertEqual(
self.compile(query, literal_binds=True),
'SELECT parent.child1 FROM t1 '
'WHERE parent.child1 = [1, 2] '
'AND parent.child2 = [\'foo\', \'bar\']')
