def test_07_two_tables_all_cols(self):
q = 'select * from tablea,tableb'
exp = {
'b.b': [
'1', '2', '3', '3', '1', '2', '3', '3', '1', '2', '3', '3',
'1', '2', '3', '3'
],
'b.str': [
'one', 'two', 'three', 'one', 'one', 'two', 'three', 'one',
'one', 'two', 'three', 'one', 'one', 'two', 'three', 'one'
],
'a.str': [
'one', 'one', 'one', 'one', 'two', 'two', 'two', 'two',
'three', 'three', 'three', 'three', 'one', 'one', 'one', 'one'
],
'b.val': [
'1', '2', '3', '1', '1', '2', '3', '1', '1', '2', '3', '1',
'1', '2', '3', '1'
],
'a.val': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'1', '1', '1', '1'
],
'a.a': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'4', '4', '4', '4'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joined tables, all columns: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_03_multiple_column(self):
q = 'select a.val,a.a from tablea'
exp = {'a.a': ['1', '2', '3', '4'], 'a.val': ['1', '2', '3', '1']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "multiple columns from a single table: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_01_single_column_unique(self):
q = 'select a.a from tablea'
exp = {'a.a': ['1', '2', '3', '4']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "single column all unique: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_02_single_column_repeats(self):
q = 'select b.b from tableb'
exp = {'b.b': ['1', '2', '3', '3']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "single column with repeats: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_14_join_multi_tables(self):
q = "select a.val,b.val,c.val from tablea,tableb,tablec"
exp = {
'c.val': [
'100', '200', '100', '200', '100', '200', '100', '200', '100',
'200', '100', '200', '100', '200', '100', '200', '100', '200',
'100', '200', '100', '200', '100', '200', '100', '200', '100',
'200', '100', '200', '100', '200'
],
'b.val': [
'1', '1', '2', '2', '3', '3', '1', '1', '1', '1', '2', '2',
'3', '3', '1', '1', '1', '1', '2', '2', '3', '3', '1', '1',
'1', '1', '2', '2', '3', '3', '1', '1'
],
'a.val': [
'1', '1', '1', '1', '1', '1', '1', '1', '2', '2', '2', '2',
'2', '2', '2', '2', '3', '3', '3', '3', '3', '3', '3', '3',
'1', '1', '1', '1', '1', '1', '1', '1'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joining 3 tables': " + "EXPECTED: " +
str(exp) + " GOT: " + str(res))
def test_11_col_gt_col(self):
q = "select a.a from tablea where a.a>a.val"
exp = {'a.a': ['4']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "selecting: where col1>col2': " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_09_col_gt_val(self):
q = "select a.a from tablea where a.val>'1'"
exp = {'a.a': ['2', '3']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "selecting: where col>'val': " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_12_join_col_eq_col(self):
q = "select a.val,b.val from tablea,tableb where a.a=b.b"
exp = {'b.val': ['1', '2', '3', '1'], 'a.val': ['1', '2', '3', '3']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp),
"selecting from joined tables: where col1=col2': " + "EXPECTED: " +
str(exp) + " GOT: " + str(res))
def test_04_star_column(self):
q = 'select * from tablea'
exp = {
'a.str': ['one', 'two', 'three', 'one'],
'a.val': ['1', '2', '3', '1'],
'a.a': ['1', '2', '3', '4']
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "all columns from a single table: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_05_two_tables_one_col(self):
q = 'select a.a from tablea,tableb'
exp = {
'a.a': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'4', '4', '4', '4'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joined tables, single column: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_15_join_multi_tables_with_selection(self):
q = "select a.val,b.val,c.val from tablea,tableb,tablec where a.a=b.b,a.a>c.c"
exp = {
'c.val': ['100', '100', '200', '100', '200'],
'a.val': ['2', '3', '3', '3', '3'],
'b.val': ['2', '3', '3', '1', '1']
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joining 3 tables with selection': " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_06_two_tables_two_cols(self):
q = 'select a.a,b.b from tablea,tableb'
exp = {
'a.a': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'4', '4', '4', '4'
],
'b.b': [
'1', '2', '3', '3', '1', '2', '3', '3', '1', '2', '3', '3',
'1', '2', '3', '3'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joined tables, one column per table: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))