def test_join_tables_where_ambigous_column_error(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('int.tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN ), where=BinaryOperation('and', args=[ BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('tab1.column1'), Constant(1)]), BinaryOperation('=', args=[Identifier('tab2.column1'), Constant(0)]), ] ), BinaryOperation('=', args=[Identifier('column3'), Constant(0)]), # Ambigous column: no idea what table column3 comes from ] ) ) with pytest.raises(PlanningException) as e: plan_query(query, integrations=['int'])
def test_select_from_predictor_no_where_error(self): query = Select(targets=[Star()], from_table=Identifier('mindsdb.pred')) with pytest.raises(PlanningException): plan_query(query, predictor_namespace='mindsdb', predictor_metadata={'pred': {}})
def test_join_tables_error_on_wrong_table_in_condition(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('int.tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab3.column1')]), #Wrong table name join_type=JoinType.INNER_JOIN )) with pytest.raises(PlanningException) as e: plan_query(query, integrations=['int'])
def test_select_from_predictor_plan_group_by_error(self): query = Select( targets=[Identifier('x1'), Identifier('x2'), Identifier('pred.y')], from_table=Identifier('mindsdb.pred'), group_by=[Identifier('x1')]) with pytest.raises(PlanningException): plan_query(query, predictor_namespace='mindsdb', predictor_metadata={'pred': {}})
def test_join_predictor_plan_order_by(self): query = Select(targets=[Identifier('tab.column1'), Identifier('pred.predicted')], from_table=Join(left=Identifier('int.tab'), right=Identifier('mindsdb.pred'), join_type=JoinType.INNER_JOIN, implicit=True), where=BinaryOperation('=', args=[Identifier('tab.product_id'), Constant('x')]), limit=Constant(10), offset=Constant(15), order_by=[OrderBy(field=Identifier('tab.column1'))] ) expected_plan = QueryPlan( steps=[ FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab'), where=BinaryOperation('=', args=[Identifier('tab.product_id'), Constant('x')]), limit=Constant(10), offset=Constant(15), order_by=[OrderBy(field=Identifier('tab.column1'))], ), ), ApplyPredictorStep(namespace='mindsdb', dataframe=Result(0), predictor=Identifier('pred')), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('result_0', alias=Identifier('tab')), right=Identifier('result_1', alias=Identifier('pred')), join_type=JoinType.INNER_JOIN)), ProjectStep(dataframe=Result(2), columns=[Identifier('tab.column1'), Identifier('pred.predicted')]), ], ) plan = plan_query(query, integrations=['int'], predictor_namespace='mindsdb', predictor_metadata={'pred': {}}) assert plan.steps == expected_plan.steps
def test_integration_select_limit_offset(self): query = Select(targets=[Identifier('column1')], from_table=Identifier('int.tab'), where=BinaryOperation('=', args=[Identifier('column1'), Identifier('column2')]), limit=Constant(10), offset=Constant(15), ) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[Identifier('tab.column1', alias=Identifier('column1'))], from_table=Identifier('tab'), where=BinaryOperation('=', args=[Identifier('tab.column1'), Identifier( 'tab.column2')]), limit=Constant(10), offset=Constant(15), ), ), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_integration_name_is_case_insensitive(self): query = Select(targets=[Identifier('column1')], from_table=Identifier('int.tab'), where=BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('column1'), Identifier('column2')]), BinaryOperation('>', args=[Identifier('column3'), Constant(0)]), ])) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select(targets=[Identifier('tab.column1', alias=Identifier('column1'))], from_table=Identifier('tab'), where=BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('tab.column1'), Identifier('tab.column2')]), BinaryOperation('>', args=[Identifier('tab.column3'), Constant(0)]), ]) )), ]) plan = plan_query(query, integrations=['INT']) assert plan.steps == expected_plan.steps
def test_join_tables_disambiguate_identifiers_in_condition(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('int.tab2'), condition=BinaryOperation(op='=', args=[Identifier('int.tab1.column1'), # integration name included Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN ) ) plan = plan_query(query, integrations=['int']) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[Star()], from_table=Identifier('tab1')), ), FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab2')), ), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('tab1'), right=Identifier('tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), # integration name gets stripped out Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN )), ProjectStep(dataframe=Result(2), columns=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')]), ], ) assert plan.steps == expected_plan.steps
def test_integration_select_default_namespace(self): query = Select(targets=[Identifier('column1'), Constant(1), Function('database', args=[])], from_table=Identifier('tab'), where=BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('column1'), Identifier('column2')]), BinaryOperation('>', args=[Identifier('column3'), Constant(0)]), ])) expected_plan = QueryPlan(integrations=['int'], default_namespace='int', steps=[ FetchDataframeStep(integration='int', query=Select(targets=[Identifier('tab.column1', alias=Identifier('column1')), Constant(1), Function('database', args=[]), ], from_table=Identifier('tab'), where=BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('tab.column1'), Identifier('tab.column2')]), BinaryOperation('>', args=[Identifier('tab.column3'), Constant(0)]), ]) ), step_num=0, references=None, ), ]) plan = plan_query(query, integrations=['int'], default_namespace='int') for i in range(len(plan.steps)): assert plan.steps[i] == expected_plan.steps[i]
def test_integration_select_subquery_in_where(self): query = Select(targets=[Star()], from_table=Identifier('int.tab1'), where=BinaryOperation(op='in', args=( Identifier(parts=['column1']), Select(targets=[Identifier('column2')], from_table=Identifier('int.tab2'), parentheses=True) ))) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab1'), where=BinaryOperation(op='in', args=[ Identifier('tab1.column1'), Select(targets=[ Identifier('tab2.column2', alias=Identifier('column2'))], from_table=Identifier('tab2'), parentheses=True)] ))), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_integration_select_plan_group_by(self): query = Select(targets=[Identifier('column1'), Identifier("column2"), Function(op="sum", args=[Identifier(parts=["column3"])], alias=Identifier('total')), ], from_table=Identifier('int.tab'), group_by=[Identifier("column1"), Identifier("column2")], having=BinaryOperation('=', args=[Identifier("column1"), Constant(0)]) ) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select(targets=[ Identifier('tab.column1', alias=Identifier('column1')), Identifier('tab.column2', alias=Identifier('column2')), Function(op="sum", args=[Identifier(parts=['tab', 'column3'])], alias=Identifier('total')), ], from_table=Identifier('tab'), group_by=[Identifier('tab.column1'), Identifier('tab.column2')], having=BinaryOperation('=', args=[Identifier('tab.column1'), Constant(0)]) )), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_join_tables_plan_default_namespace(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')], from_table=Join(left=Identifier('tab1'), right=Identifier('tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN ) ) expected_plan = QueryPlan(integrations=['int'], default_namespace='int', steps = [ FetchDataframeStep(integration='int', query=Select( targets=[Star()], from_table=Identifier('tab1')), ), FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab2')), ), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('tab1'), right=Identifier('tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN )), ProjectStep(dataframe=Result(2), columns=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')]), ], ) plan = plan_query(query, integrations=['int'], default_namespace='int') assert plan.steps == expected_plan.steps
def test_select_from_predictor_default_namespace(self): query = Select( targets=[Star()], from_table=Identifier('pred'), where=BinaryOperation( op='and', args=[ BinaryOperation(op='=', args=[Identifier('x1'), Constant(1)]), BinaryOperation(op='=', args=[Identifier('x2'), Constant('2')]) ], )) expected_plan = QueryPlan( predictor_namespace='mindsdb', default_namespace='mindsdb', steps=[ ApplyPredictorRowStep(namespace='mindsdb', predictor=Identifier('pred'), row_dict={ 'x1': 1, 'x2': '2' }), ProjectStep(dataframe=Result(0), columns=[Star()]), ], ) plan = plan_query(query, predictor_namespace='mindsdb', default_namespace='mindsdb', predictor_metadata={'pred': {}}) assert plan.steps == expected_plan.steps
def test_join_predictor_plan_default_namespace_predictor(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('pred.predicted')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('pred'), join_type=JoinType.INNER_JOIN, implicit=True) ) expected_plan = QueryPlan( default_namespace='mindsdb', steps=[ FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab1')), ), ApplyPredictorStep(namespace='mindsdb', dataframe=Result(0), predictor=Identifier('pred')), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('result_0', alias=Identifier('tab1')), right=Identifier('result_1', alias=Identifier('pred')), join_type=JoinType.INNER_JOIN)), ProjectStep(dataframe=Result(2), columns=[Identifier('tab1.column1'), Identifier('pred.predicted')]), ], ) plan = plan_query(query, integrations=['int'], predictor_namespace='mindsdb', default_namespace='mindsdb', predictor_metadata={'pred': {}}) for i in range(len(plan.steps)): assert plan.steps[i] == expected_plan.steps[i]
def test_select_from_predictor_get_columns(self): sql = f'SELECT GDP_per_capita_USD FROM hdi_predictor_external WHERE 1 = 0' query = parse_sql(sql, dialect='mindsdb') expected_query = Select( targets=[Identifier('GDP_per_capita_USD')], from_table=Identifier('hdi_predictor_external'), where=BinaryOperation(op="=", args=[Constant(1), Constant(0)])) assert query.to_tree() == expected_query.to_tree() expected_plan = QueryPlan( predictor_namespace='mindsdb', default_namespace='mindsdb', steps=[ GetPredictorColumns( namespace='mindsdb', predictor=Identifier('hdi_predictor_external')), ProjectStep(dataframe=Result(0), columns=[Identifier('GDP_per_capita_USD')]), ], ) plan = plan_query(query, predictor_namespace='mindsdb', default_namespace='mindsdb', predictor_metadata={'hdi_predictor_external': {}}) assert plan.steps == expected_plan.steps
def test_predictors_select_plan(self): query = Select( targets=[ Identifier('column1'), Constant(1), NullConstant(), Function('database', args=[]) ], from_table=Identifier('mindsdb.predictors'), where=BinaryOperation( 'and', args=[ BinaryOperation( '=', args=[Identifier('column1'), Identifier('column2')]), BinaryOperation('>', args=[Identifier('column3'), Constant(0)]), ])) expected_plan = QueryPlan( integrations=['mindsdb'], steps=[ FetchDataframeStep( integration='mindsdb', query=Select( targets=[ Identifier('predictors.column1', alias=Identifier('column1')), Constant(1), NullConstant(), Function('database', args=[]), ], from_table=Identifier('predictors'), where=BinaryOperation( 'and', args=[ BinaryOperation( '=', args=[ Identifier('predictors.column1'), Identifier('predictors.column2') ]), BinaryOperation( '>', args=[ Identifier('predictors.column3'), Constant(0) ]), ])), step_num=0, references=None, ), ]) plan = plan_query(query, integrations=['mindsdb']) for i in range(len(plan.steps)): assert plan.steps[i] == expected_plan.steps[i]
def test_plan_union_queries(self): query1 = Select(targets=[Identifier('column1'), Constant(None, alias=Identifier('predicted'))], from_table=Identifier('int.tab'), where=BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('column1'), Identifier('column2')]), BinaryOperation('>', args=[Identifier('column3'), Constant(0)]), ])) query2 = Select( targets=[Identifier('tab1.column1'), Identifier('pred.predicted', alias=Identifier('predicted'))], from_table=Join(left=Identifier('int.tab1'), right=Identifier('mindsdb.pred'), join_type=JoinType.INNER_JOIN, implicit=True) ) query = Union(left=query1, right=query2, unique=False) expected_plan = QueryPlan( steps=[ # Query 1 FetchDataframeStep(integration='int', query=Select(targets=[Identifier('tab.column1', alias=Identifier('column1')), Constant(None, alias=Identifier('predicted'))], from_table=Identifier('tab'), where=BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('tab.column1'), Identifier('tab.column2')]), BinaryOperation('>', args=[Identifier('tab.column3'), Constant(0)]), ]) )), # Query 2 FetchDataframeStep(integration='int', query=Select( targets=[Star()], from_table=Identifier('tab1')), ), ApplyPredictorStep(namespace='mindsdb', dataframe=Result(1), predictor=Identifier('pred')), JoinStep(left=Result(1), right=Result(2), query=Join(left=Identifier('result_1', alias=Identifier('tab1')), right=Identifier('result_2', alias=Identifier('pred')), join_type=JoinType.INNER_JOIN)), ProjectStep(dataframe=Result(3), columns=[Identifier('tab1.column1'), Identifier('pred.predicted', alias=Identifier('predicted'))]), # Union UnionStep(left=Result(0), right=Result(4), unique=False), ], ) plan = plan_query(query, integrations=['int'], predictor_namespace='mindsdb', predictor_metadata={'pred': {}}) for i in range(len(plan.steps)): assert plan.steps[i] == expected_plan.steps[i]
def test_no_predictor_error(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('pred.predicted')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('pred'), join_type=None, implicit=True) ) with pytest.raises(PlanningException): plan = plan_query(query, integrations=['int'], predictor_metadata={'pred': {}})
def test_integration_select_plan_star(self): query = Select(targets=[Star()], from_table=Identifier('int.tab')) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab'))), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_select_from_predictor_multiple_values_error(self): query = Select( targets=[Star()], from_table=Identifier('mindsdb.pred'), where=BinaryOperation( op='and', args=[ BinaryOperation(op='=', args=[Identifier('x1'), Constant(1)]), BinaryOperation(op='=', args=[Identifier('x1'), Constant('2')]) ], )) with pytest.raises(PlanningException): plan_query(query, predictor_namespace='mindsdb', predictor_metadata={'pred': {}})
def test_join_predictor_error_when_filtering_on_predictions(self): """ Query: SELECT rental_price_confidence FROM postgres_90.test_data.home_rentals AS ta JOIN mindsdb.hrp3 AS tb WHERE ta.sqft > 1000 AND tb.rental_price_confidence > 0.5 LIMIT 5; """ query = Select(targets=[Identifier('rental_price_confidence')], from_table=Join(left=Identifier('postgres_90.test_data.home_rentals', alias=Identifier('ta')), right=Identifier('mindsdb.hrp3', alias=Identifier('tb')), join_type=JoinType.INNER_JOIN, implicit=True), where=BinaryOperation('and', args=[ BinaryOperation('>', args=[Identifier('ta.sqft'), Constant(1000)]), BinaryOperation('>', args=[Identifier('tb.rental_price_confidence'), Constant(0.5)]), ]), limit=5 ) with pytest.raises(PlanningException): plan_query(query, integrations=['postgres_90'], predictor_namespace='mindsdb', predictor_metadata={'hrp3': {}})
def test_integration_select_column_alias(self): query = Select(targets=[Identifier('col1', alias=Identifier('column_alias'))], from_table=Identifier('int.tab')) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[Identifier(parts=['tab', 'col1'], alias=Identifier('column_alias'))], from_table=Identifier(parts=['tab'])), ), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_integration_select_table_alias_full_query(self): sql = 'select ta.sqft from int.test_data.home_rentals as ta' query = parse_sql(sql, dialect='sqlite') expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[Identifier(parts=['ta', 'sqft'], alias=Identifier('sqft'))], from_table=Identifier(parts=['test_data', 'home_rentals'], alias=Identifier('ta'))), ), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_join_tables_plan_groupby(self): query = Select(targets=[ Identifier('tab1.column1'), Identifier('tab2.column1'), Function('sum', args=[Identifier('tab2.column2')], alias=Identifier('total'))], from_table=Join(left=Identifier('int.tab1'), right=Identifier('int.tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN ), group_by=[Identifier('tab1.column1'), Identifier('tab2.column1')], having=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Constant(0)]) ) plan = plan_query(query, integrations=['int']) expected_plan = QueryPlan(integrations=['int'], steps = [ FetchDataframeStep(integration='int', query=Select( targets=[Star()], from_table=Identifier('tab1')), ), FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab2')), ), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('tab1'), right=Identifier('tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN )), GroupByStep(dataframe=Result(2), targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Function('sum', args=[Identifier('tab2.column2')])], columns=[Identifier('tab1.column1'), Identifier('tab2.column1')]), FilterStep(dataframe=Result(3), query=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Constant(0)])), ProjectStep(dataframe=Result(4), columns=[Identifier('tab1.column1'), Identifier('tab2.column1'), Function(op='sum', args=[Identifier('tab2.column2')], alias=Identifier('total'))]), ], ) assert plan.steps == expected_plan.steps
def test_integration_select_plan_complex_path(self): query = Select(targets=[Identifier(parts=['int', 'tab', 'a column with spaces'])], from_table=Identifier('int.tab')) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[ Identifier('tab.`a column with spaces`', alias=Identifier('a column with spaces') ) ], from_table=Identifier('tab')), ), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_integration_select_subquery_in_from(self): query = Select(targets=[Identifier('column1')], from_table=Select(targets=[Identifier('column1')], from_table=Identifier('int.tab'), alias=Identifier('subquery'))) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[Identifier('column1')], from_table=Select( targets=[Identifier('tab.column1', alias=Identifier('column1'))], from_table=Identifier('tab'), alias=Identifier('subquery')), )), ]) plan = plan_query(query, integrations=['int']) assert plan.steps == expected_plan.steps
def test_join_tables_plan_order_by(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('int.tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN ), limit=Constant(10), offset=Constant(15), order_by=[OrderBy(field=Identifier('tab1.column1'))], ) plan = plan_query(query, integrations=['int']) expected_plan = QueryPlan(integrations=['int'], steps = [ FetchDataframeStep(integration='int', query=Select( targets=[Star()], from_table=Identifier('tab1')), ), FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab2')), ), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('tab1'), right=Identifier('tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN )), OrderByStep(dataframe=Result(2), order_by=[OrderBy(field=Identifier('tab1.column1'))]), LimitOffsetStep(dataframe=Result(3), limit=10, offset=15), ProjectStep(dataframe=Result(4), columns=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')]), ], ) assert plan.steps == expected_plan.steps
def test_integration_select_3_level(self): sql = "select * from xxx.yyy.zzz where x > 1" query = parse_sql(sql, dialect='mindsdb') expected_plan = QueryPlan(integrations=['int'], default_namespace='xxx', steps=[ FetchDataframeStep( integration='xxx', query=Select( targets=[Star()], from_table=Identifier('yyy.zzz'), where=BinaryOperation(op='>', args=[ Identifier('yyy.zzz.x'), Constant(1) ]) ) ) ]) plan = plan_query(query, integrations=['xxx']) assert plan.steps == expected_plan.steps
def test_join_tables_where_plan(self): query = Select(targets=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')], from_table=Join(left=Identifier('int.tab1'), right=Identifier('int.tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN ), where=BinaryOperation('and', args=[ BinaryOperation('and', args=[ BinaryOperation('=', args=[Identifier('tab1.column1'), Constant(1)]), BinaryOperation('=', args=[Identifier('tab2.column1'), Constant(0)]), ] ), BinaryOperation('=', args=[Identifier('tab1.column3'), Identifier('tab2.column3')]), ] ) ) plan = plan_query(query, integrations=['int']) expected_plan = QueryPlan(integrations=['int'], steps=[ FetchDataframeStep(integration='int', query=Select( targets=[Star()], from_table=Identifier('tab1'), ), ), FetchDataframeStep(integration='int', query=Select(targets=[Star()], from_table=Identifier('tab2'), ), ), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('tab1'), right=Identifier('tab2'), condition=BinaryOperation(op='=', args=[Identifier('tab1.column1'), Identifier('tab2.column1')]), join_type=JoinType.INNER_JOIN )), FilterStep(dataframe=Result(2), query=BinaryOperation('and', args=[ BinaryOperation('and', args=[ BinaryOperation('=', args=[ Identifier( 'tab1.column1'), Constant( 1)]), BinaryOperation('=', args=[ Identifier( 'tab2.column1'), Constant( 0)]), ] ), BinaryOperation('=', args=[Identifier( 'tab1.column3'), Identifier( 'tab2.column3')]), ] )), ProjectStep(dataframe=Result(3), columns=[Identifier('tab1.column1'), Identifier('tab2.column1'), Identifier('tab2.column2')]), ], ) assert plan.steps == expected_plan.steps
def test_nested_select(self): # for tableau sql = f''' SELECT time FROM ( select * from int.covid join mindsdb.pred limit 10 ) `Custom SQL Query` limit 1 ''' query = parse_sql(sql, dialect='mindsdb') expected_plan = QueryPlan( default_namespace='mindsdb', steps=[ FetchDataframeStep(integration='int', query=parse_sql('select * from covid limit 10')), ApplyPredictorStep(namespace='mindsdb', dataframe=Result(0), predictor=Identifier('pred')), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('result_0', alias=Identifier('covid')), right=Identifier('result_1', alias=Identifier('pred')), join_type=JoinType.JOIN)), ProjectStep(dataframe=Result(2), columns=[Star()]), ProjectStep(dataframe=Result(3), columns=[Identifier('time')], ignore_doubles=True), LimitOffsetStep(dataframe=Result(4), limit=1) ], ) plan = plan_query( query, integrations=['int'], predictor_namespace='mindsdb', default_namespace='mindsdb', predictor_metadata={'pred': {}} ) for i in range(len(plan.steps)): assert plan.steps[i] == expected_plan.steps[i] sql = f''' SELECT `time` FROM ( select * from int.covid join mindsdb.pred ) `Custom SQL Query` GROUP BY 1 ''' query = parse_sql(sql, dialect='mindsdb') expected_plan = QueryPlan( default_namespace='mindsdb', steps=[ FetchDataframeStep(integration='int', query=parse_sql('select * from covid')), ApplyPredictorStep(namespace='mindsdb', dataframe=Result(0), predictor=Identifier('pred')), JoinStep(left=Result(0), right=Result(1), query=Join(left=Identifier('result_0', alias=Identifier('covid')), right=Identifier('result_1', alias=Identifier('pred')), join_type=JoinType.JOIN)), ProjectStep(dataframe=Result(2), columns=[Star()]), GroupByStep(dataframe=Result(3), columns=[Constant(1)], targets=[Identifier('time')]) ], ) plan = plan_query( query, integrations=['int'], predictor_namespace='mindsdb', default_namespace='mindsdb', predictor_metadata={'pred': {}} ) for i in range(len(plan.steps)): assert plan.steps[i] == expected_plan.steps[i]