When joining on multiple columns "IndexError: list index out of range" will occur.
query(""" select a1.symbol, a1.date_count, a2.date_count_d
from
(select symbol, count(date) as date_count from zh_a_all group by symbol) a1
inner join
(select symbol, count(date) as date_count_d from (select distinct symbol, date from zh_a_all) a group by symbol)a2
on a1.symbol = a2.symbol and a1.date_count = a2.date_count_d
""")
[Column(final_name=symbol, value=IbisStringColumn(), name=symbol, table=<sql_to_ibis.sql_objects.Table object at 0x00000250AD580C88>), Aggregate(final_name=date_count, value=IbisIntegerScalar(), alias=date_count), Token(from_expression, <sql_to_ibis.sql_objects.Table object at 0x00000250AE94BCC8>), GroupByColumn(final_name=symbol, value=IbisStringColumn(), name=symbol, table=None)]
PandasTable[table]
name: zh_a_all
schema:
date : timestamp
open : float64
high : float64
low : float64
close : float64
volume : float64
outstanding_share : float64
turnover : float64
symbol : string
[Token(SELECT_CONSTRAINT, 'distinct'), Column(final_name=symbol, value=IbisStringColumn(), name=symbol, table=<sql_to_ibis.sql_objects.Table object at 0x00000250AD580C88>), Column(final_name=date, value=IbisTimestampColumn(), name=date, table=<sql_to_ibis.sql_objects.Table object at 0x00000250AD580C88>), Token(from_expression, <sql_to_ibis.sql_objects.Table object at 0x00000250AD4A3648>)]
PandasTable[table]
name: zh_a_all
schema:
date : timestamp
open : float64
high : float64
low : float64
close : float64
volume : float64
outstanding_share : float64
turnover : float64
symbol : string
[Column(final_name=symbol, value=IbisStringColumn(), name=symbol, table=Subquery(name=a, value=ref_0
PandasTable[table]
name: zh_a_all
schema:
date : timestamp
open : float64
high : float64
low : float64
close : float64
volume : float64
outstanding_share : float64
turnover : float64
symbol : string
ref_1
Selection[table]
table:
Table: ref_0
selections:
symbol = Column[string*] 'symbol' from table
ref_0
date = Column[timestamp*] 'date' from table
ref_0
Distinct[table]
table:
Table: ref_1)), Aggregate(final_name=date_count_d, value=IbisIntegerScalar(), alias=date_count_d), Token(from_expression, Subquery(name=a, value=ref_0
PandasTable[table]
name: zh_a_all
schema:
date : timestamp
open : float64
high : float64
low : float64
close : float64
volume : float64
outstanding_share : float64
turnover : float64
symbol : string
ref_1
Selection[table]
table:
Table: ref_0
selections:
symbol = Column[string*] 'symbol' from table
ref_0
date = Column[timestamp*] 'date' from table
ref_0
Distinct[table]
table:
Table: ref_1)), GroupByColumn(final_name=symbol, value=IbisStringColumn(), name=symbol, table=None)]
ref_0
PandasTable[table]
name: zh_a_all
schema:
date : timestamp
open : float64
high : float64
low : float64
close : float64
volume : float64
outstanding_share : float64
turnover : float64
symbol : string
ref_1
Selection[table]
table:
Table: ref_0
selections:
symbol = Column[string*] 'symbol' from table
ref_0
date = Column[timestamp*] 'date' from table
ref_0
Distinct[table]
table:
Table: ref_1
VisitError Traceback (most recent call last)
~\anaconda3\lib\site-packages\sql_to_ibis\sql_select_query.py in parse_sql(self)
135 # ambiguous column references are not distorted
--> 136 ).transform(tree)
137 except UnexpectedToken as err:
~\anaconda3\lib\site-packages\lark\visitors.py in transform(self, tree)
104 def transform(self, tree):
--> 105 return self._transform_tree(tree)
106
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
100 def _transform_tree(self, tree):
--> 101 children = list(self._transform_children(tree.children))
102 return self._call_userfunc(tree, children)
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_children(self, children)
91 if isinstance(c, Tree):
---> 92 yield self._transform_tree(c)
93 elif self.visit_tokens and isinstance(c, Token):
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
100 def _transform_tree(self, tree):
--> 101 children = list(self._transform_children(tree.children))
102 return self._call_userfunc(tree, children)
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_children(self, children)
91 if isinstance(c, Tree):
---> 92 yield self._transform_tree(c)
93 elif self.visit_tokens and isinstance(c, Token):
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
100 def _transform_tree(self, tree):
--> 101 children = list(self._transform_children(tree.children))
102 return self._call_userfunc(tree, children)
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_children(self, children)
91 if isinstance(c, Tree):
---> 92 yield self._transform_tree(c)
93 elif self.visit_tokens and isinstance(c, Token):
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
100 def _transform_tree(self, tree):
--> 101 children = list(self._transform_children(tree.children))
102 return self._call_userfunc(tree, children)
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_children(self, children)
91 if isinstance(c, Tree):
---> 92 yield self._transform_tree(c)
93 elif self.visit_tokens and isinstance(c, Token):
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
100 def _transform_tree(self, tree):
--> 101 children = list(self._transform_children(tree.children))
102 return self._call_userfunc(tree, children)
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_children(self, children)
91 if isinstance(c, Tree):
---> 92 yield self._transform_tree(c)
93 elif self.visit_tokens and isinstance(c, Token):
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
100 def _transform_tree(self, tree):
--> 101 children = list(self._transform_children(tree.children))
102 return self._call_userfunc(tree, children)
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_children(self, children)
91 if isinstance(c, Tree):
---> 92 yield self._transform_tree(c)
93 elif self.visit_tokens and isinstance(c, Token):
~\anaconda3\lib\site-packages\lark\visitors.py in _transform_tree(self, tree)
101 children = list(self._transform_children(tree.children))
--> 102 return self._call_userfunc(tree, children)
103
~\anaconda3\lib\site-packages\lark\visitors.py in _call_userfunc(self, tree, new_children)
71 except Exception as e:
---> 72 raise VisitError(tree.data, tree, e)
73
VisitError: Error trying to process rule "join_expression":
list index out of range
During handling of the above exception, another exception occurred:
IndexError Traceback (most recent call last)
in
5 (select symbol, count(date) as date_count_d from (select distinct symbol, date from zh_a_all) a group by symbol)a2
6 on a1.symbol = a2.symbol and a1.date_count = a2.date_count_d
----> 7 """)
~\anaconda3\lib\site-packages\dataframe_sql\sql_select_query.py in query(sql)
94
95 """
---> 96 return ibis_query(sql).execute()
~\anaconda3\lib\site-packages\sql_to_ibis\sql_select_query.py in query(sql)
110
111 """
--> 112 return SqlToTable(sql).ibis_expr
113
114
~\anaconda3\lib\site-packages\sql_to_ibis\sql_select_query.py in init(self, sql)
119 self.sql = sql
120
--> 121 self.ast = self.parse_sql()
122 self.ibis_expr = self.ast
123
~\anaconda3\lib\site-packages\sql_to_ibis\sql_select_query.py in parse_sql(self)
145 while isinstance(err, VisitError):
146 err = err.orig_exc
--> 147 raise err
148
149
~\anaconda3\lib\site-packages\lark\visitors.py in _call_userfunc(self, tree, new_children)
64 wrapper = getattr(f, 'visit_wrapper', None)
65 if wrapper is not None:
---> 66 return f.visit_wrapper(f, tree.data, children, tree.meta)
67 else:
68 return f(children)
~\anaconda3\lib\site-packages\lark\visitors.py in _vargs_inline(f, data, children, meta)
311
312 def _vargs_inline(f, data, children, meta):
--> 313 return f(*children)
314 def _vargs_meta_inline(f, data, children, meta):
315 return f(meta, *children)
~\anaconda3\lib\site-packages\lark\visitors.py in f(self, *args, **kwargs)
295 if with_self:
296 def f(self, *args, **kwargs):
--> 297 return _f(self, *args, **kwargs)
298 else:
299 def f(self, *args, **kwargs):
~\anaconda3\lib\site-packages\sql_to_ibis\parsing\sql_parser.py in join_expression(self, *args)
287 column_comparison = join_condition.children[0].children[0].children
288 column1 = str(column_comparison[0].children)
--> 289 column2 = str(column_comparison[1].children)
290
291 column1_side, column1 = self._determine_column_side(column1, table1, table2)
IndexError: list index out of range