Repository: macbre/sql-metadata

Issue:
Alias and column names from subquery are case sensitive when used as conditions in outer query
It seem like case must match on alias/column names from subquery when they are used as a condition in the outer query, *only* if the subquery alias is used in the condition.

for example, this works:
```sql
select tEsT
from (select a as TeSt from nowhere) as t
where tEsT = 'asdf'
```

but this fails:
```sql
select tEsT
from (select a as TeSt from nowhere) as t
where t.tEsT = 'asdf'
```
with an error
```error
ValueError: 'tEsT' is not in list
```

I think it can be fixed by ignoring case when checking column alias.

In Parser.py, `_resolve_nested_query, we can change
```py
if column_name in subparser.columns_aliases_names:
```
to
```py
if column_name.lower() in [x.lower() for x in subparser.columns_aliases_names]:
```
or 
```py
if any(filter(lambda x: x.lower() == column_name.lower(),subparser.columns_aliases_names)):
```
... not sure of a nicer way to say it.

 I'll put a pull request w/  a test.

Discussion:

Patch:
diff --git a/sql_metadata/parser.py b/sql_metadata/parser.py
index ec8a823..0d28390 100644
--- a/sql_metadata/parser.py
+++ b/sql_metadata/parser.py
@@ -66,7 +66,7 @@ class Parser:  # pylint: disable=R0902
         self._subquery_level = 0
         self._nested_level = 0
         self._parenthesis_level = 0
-        self._open_parentheses = []
+        self._open_parentheses: List[SQLToken] = []
         self._aliases_to_check = None
         self._is_in_nested_function = False
         self._is_in_with_block = False
@@ -326,10 +326,11 @@ class Parser:  # pylint: disable=R0902
                 and not token.previous_token.is_nested_function_start
                 and token.is_alias_definition
             ):
-                if token.previous_token.normalized == "AS":
-                    token_check = token.get_nth_previous(2)
-                else:
-                    token_check = token.previous_token
+                token_check = (
+                    token.get_nth_previous(2)
+                    if token.previous_token.normalized == "AS"
+                    else token.previous_token
+                )
                 if token_check.is_column_definition_end:
                     # nested subquery like select a, (select a as b from x) as column
                     start_token = token.find_nearest_token(
@@ -348,6 +349,13 @@ class Parser:  # pylint: disable=R0902
                         alias_of = self._find_all_columns_between_tokens(
                             start_token=start_token, end_token=token
                         )
+                elif token_check.is_partition_clause_end:
+                    start_token = token.find_nearest_token(
+                        True, value_attribute="is_partition_clause_start"
+                    )
+                    alias_of = self._find_all_columns_between_tokens(
+                        start_token=start_token, end_token=token
+                    )
                 elif token.is_in_with_columns:
                     # columns definition is to the right in subquery
                     # we are in: with with_name (<aliases>) as (subquery)
@@ -916,6 +924,8 @@ class Parser:  # pylint: disable=R0902
             or token.get_nth_previous(4).normalized == "TABLE"
         ):
             token.is_create_table_columns_declaration_start = True
+        elif token.previous_token.normalized == "OVER":
+            token.is_partition_clause_start = True
         else:
             # nested function
             token.is_nested_function_start = True
@@ -938,6 +948,8 @@ class Parser:  # pylint: disable=R0902
             token.is_with_query_end = True
         elif last_open_parenthesis.is_create_table_columns_declaration_start:
             token.is_create_table_columns_declaration_end = True
+        elif last_open_parenthesis.is_partition_clause_start:
+            token.is_partition_clause_end = True
         else:
             token.is_nested_function_end = True
             self._nested_level -= 1
@@ -1010,12 +1022,15 @@ class Parser:  # pylint: disable=R0902
 
         return query
 
-    @staticmethod
-    def _determine_last_relevant_keyword(token: SQLToken, last_keyword: str):
+    def _determine_last_relevant_keyword(self, token: SQLToken, last_keyword: str):
         if token.is_keyword and "".join(token.normalized.split()) in RELEVANT_KEYWORDS:
             if not (
                 token.normalized == "FROM"
                 and token.get_nth_previous(3).normalized == "EXTRACT"
+            ) and not (
+                token.normalized == "ORDERBY"
+                and len(self._open_parentheses) > 0
+                and self._open_parentheses[-1].is_partition_clause_start
             ):
                 last_keyword = token.normalized
         return last_keyword
diff --git a/sql_metadata/token.py b/sql_metadata/token.py
index 01ee236..bb1eb37 100644
--- a/sql_metadata/token.py
+++ b/sql_metadata/token.py
@@ -80,6 +80,8 @@ class SQLToken:  # pylint: disable=R0902
         self.is_column_definition_end = False
         self.is_create_table_columns_declaration_start = False
         self.is_create_table_columns_declaration_end = False
+        self.is_partition_clause_start = False
+        self.is_partition_clause_end = False
 
     def __str__(self):
         """
@@ -182,6 +184,7 @@ class SQLToken:  # pylint: disable=R0902
             and (
                 self.last_keyword_normalized == "SELECT"
                 or self.previous_token.is_column_definition_end
+                or self.previous_token.is_partition_clause_end
             )
             and not self.previous_token.is_comment
         )


Test patch:
diff --git a/test/test_mssql_server.py b/test/test_mssql_server.py
index a565ab1..6c5e24c 100644
--- a/test/test_mssql_server.py
+++ b/test/test_mssql_server.py
@@ -17,56 +17,50 @@ def test_sql_server_cte():
             *
         FROM x
         JOIN y ON x.a = y.a
-            """
+        """
     )
     assert parser.tables == ["n", "y"]
     assert parser.with_names == ["x"]
     assert parser.with_queries == {"x": "SELECT * FROM n"}
     assert parser.columns == ["*", "a", "y.a"]
 
-    assert (
-        Parser(
-            """
-                WITH x AS (
-                    SELECT * FROM n
-                )
-                SELECT
-                    *
-                FROM x
-                JOIN y ON x.a = y.a
-                    """
-        ).tables
-        == ["n", "y"]
+    parser = Parser(
+        """
+            WITH x AS (
+                SELECT * FROM n
+            )
+            SELECT
+                *
+            FROM x
+            JOIN y ON x.a = y.a
+        """
     )
+    assert parser.tables == ["n", "y"]
 
-    assert (
-        Parser(
-            """
-                WITH foo AS (
-                    SELECT * FROM n
-                )
-                UPDATE z from foo set z.q = foo.y
-                    """
-        ).tables
-        == ["n", "z"]
+    parser = Parser(
+        """
+        WITH foo AS (
+            SELECT * FROM n
+        )
+        UPDATE z from foo set z.q = foo.y
+        """
     )
+    assert parser.tables == ["n", "z"]
 
-    assert (
-        Parser(
-            """
-                WITH foo AS (
-                     SELECT * FROM tab
-                )
-                DELETE FROM z JOIN foo ON z.a = foo.a
-                    """.strip()
-        ).tables
-        == ["tab", "z"]
+    parser = Parser(
+        """
+        WITH foo AS (
+             SELECT * FROM tab
+        )
+        DELETE FROM z JOIN foo ON z.a = foo.a
+        """
     )
+    assert parser.tables == ["tab", "z"]
 
 
 def test_sql_server_cte_sales_by_year():
     sales_query = """
-WITH cte_sales AS (
+    WITH cte_sales AS (
     SELECT
         staff_id,
         COUNT(*) order_count
@@ -76,12 +70,12 @@ WITH cte_sales AS (
         YEAR(order_date) = 2018
     GROUP BY
         staff_id
-)
-SELECT
+    )
+    SELECT
     AVG(order_count) average_orders_by_staff
-FROM
+    FROM
     cte_sales;
-    """.strip()
+    """
 
     parser = Parser(sales_query)
     assert parser.tables == ["sales.orders"]
@@ -104,3 +98,67 @@ FROM
         "select": ["staff_id", "*"],
         "where": ["order_date"],
     }
+
+
+def test_partition_over_with_rank_and_one_order():
+    """Test for #204"""
+    parser = Parser(
+        """
+        select t.RANKED, t.RANKED_two, t.test from (
+        SELECT
+        RANK() OVER (PARTITION BY col_one ORDER BY col_two) RANKED,
+        RANK() OVER (PARTITION BY col_one ORDER BY col_two) as RANKED_two,
+        col_three as test
+        FROM nice_table) as t
+        where t.RANKED = 1
+        and t.RANKED_two = 2
+        order by test
+        """
+    )
+    assert parser.tables == ["nice_table"]
+    assert parser.columns_aliases_names == ["RANKED", "RANKED_two", "test"]
+    assert parser.columns_aliases == {
+        "RANKED": ["col_one", "col_two"],
+        "RANKED_two": ["col_one", "col_two"],
+        "test": "col_three",
+    }
+    assert parser.columns == ["col_one", "col_two", "col_three"]
+    assert parser.columns_dict == {
+        "order_by": ["col_three"],
+        "select": ["col_one", "col_two", "col_three"],
+        "where": ["col_one", "col_two"],
+    }
+
+
+def test_partition_over_with_row_number_and_many_orders():
+    """Test for #204"""
+    parser = Parser(
+        """
+        select t.row_no, t.row_no_two, t.test from (
+        SELECT
+        ROW_NUMBER() OVER (
+        PARTITION BY col_one
+        ORDER BY col_two, col_three, col_four) row_no,
+        ROW_NUMBER() OVER (
+        PARTITION BY col_one
+        ORDER BY col_two, col_three) as row_no_two,
+        col_three as test
+        FROM nice_table) as t
+        where t.row_no = 1
+        and t.row_no_two = 2
+        order by t.row_no
+        """
+    )
+    assert parser.tables == ["nice_table"]
+    assert parser.columns_aliases_names == ["row_no", "row_no_two", "test"]
+    assert parser.columns_aliases == {
+        "row_no": ["col_one", "col_two", "col_three", "col_four"],
+        "row_no_two": ["col_one", "col_two", "col_three"],
+        "test": "col_three",
+    }
+    assert parser.columns == ["col_one", "col_two", "col_three", "col_four"]
+    assert parser.columns_dict == {
+        "order_by": ["col_one", "col_two", "col_three", "col_four"],
+        "select": ["col_one", "col_two", "col_three", "col_four"],
+        "where": ["col_one", "col_two", "col_three", "col_four"],
+    }