Repository: 15five/scim2-filter-parser

Issue:
 userName attribute should be case-insensitive, per the RFC
From https://github.com/15five/django-scim2/issues/76

> See https://datatracker.ietf.org/doc/html/rfc7643#section-4.1.1: (userName)
>     This attribute is REQUIRED and is case insensitive.
> Currently this case-insensitive behavior is not implemented and the filter lookups are case-sensitive.

Discussion:
logston: Hey @powellc, if you have the time, can you give this a rigorous review? It changes core logic in a pretty large way. I'd like to make sure my assumptions are sound and my changes are worth the costs. 
andersk: When converting a string to a `LIKE` or `ILIKE` pattern, it’s necessary to escape `%`, `_`, and `\` as `\%`, `\_`, and `\\`. This doesn’t seem to do that.

```console
$ python3 -m scim2_filter_parser.transpilers.sql \
    'userType eq "a%b_c\\d" and userName co "a%b_c\\d"'
SQL: (usertype = {a}) AND (username ILIKE {b})
PARAMS: {'a': 'a%b_c\\\\d', 'b': '%a%b_c\\\\d%'}
```

Expected:

```console
SQL: (usertype = {a}) AND (username ILIKE {b})
PARAMS: {'a': 'a%b_c\\d', 'b': '%a\\%b\\_c\\\\d%'}
```

This may be a separate bug, but this will increase its impact by transpiling more queries to `ILIKE`.
andersk: Oh…I guess there’s also a question of which databases this needs to be compatible with? `ILIKE` works in PostgreSQL but not MySQL or SQLite.
logston: Right, good points. As anticipated, this is trickier than this quick patch lets on. 

Another solution that was suggested was to uppercase strings before comparison. eg. `UPPER`. I'll need to check if that's available in all major SQL engines. 

Patch:
diff --git a/CHANGELOG.rst b/CHANGELOG.rst
index 14f28e6..35eb5c5 100644
--- a/CHANGELOG.rst
+++ b/CHANGELOG.rst
@@ -1,5 +1,12 @@
 CHANGE LOG
 ==========
+0.4.0
+-----
+- Update userName to be case insensitive.  #31
+
+BREAKING CHANGE: This allows queries that did not match rows before to
+match rows now! 
+
 
 0.3.9
 -----
diff --git a/setup.py b/setup.py
index fa62e03..c46c582 100644
--- a/setup.py
+++ b/setup.py
@@ -14,7 +14,7 @@ def long_description():
 
 setup(
     name='scim2-filter-parser',
-    version='0.3.9',
+    version='0.4.0',
     description='A customizable parser/transpiler for SCIM2.0 filters',
     url='https://github.com/15five/scim2-filter-parser',
     maintainer='Paul Logston',
diff --git a/src/scim2_filter_parser/ast.py b/src/scim2_filter_parser/ast.py
index b019f01..28de65e 100644
--- a/src/scim2_filter_parser/ast.py
+++ b/src/scim2_filter_parser/ast.py
@@ -95,6 +95,12 @@ class AttrPath(AST):
     sub_attr  : (SubAttr, type(None))  # noqa: E203
     uri       : (str, type(None))  # noqa: E203
 
+    @property
+    def case_insensitive(self):
+        # userName is always case-insensitive
+        # https://datatracker.ietf.org/doc/html/rfc7643#section-4.1.1
+        return self.attr_name == 'userName'
+
 
 class CompValue(AST):
     value : str  # noqa: E203
@@ -105,6 +111,10 @@ class AttrExpr(AST):
     attr_path  : AttrPath  # noqa: E203
     comp_value : CompValue  # noqa: E203
 
+    @property
+    def case_insensitive(self):
+        return self.attr_path.case_insensitive
+
 
 # The following classes for visiting and rewriting the AST are taken
 # from Python's ast module.   It's really easy to make mistakes when
diff --git a/src/scim2_filter_parser/transpilers/django_q_object.py b/src/scim2_filter_parser/transpilers/django_q_object.py
index def4633..5ef90b2 100644
--- a/src/scim2_filter_parser/transpilers/django_q_object.py
+++ b/src/scim2_filter_parser/transpilers/django_q_object.py
@@ -139,13 +139,13 @@ class Transpiler(ast.NodeTransformer):
             partial = partial.replace(".", "__")
         if full and partial:
             # Specific to Azure
-            op, value = self.visit_AttrExprValue(node.value, node.comp_value)
+            op, value = self.visit_AttrExprValue(node)
             key = partial + "__" + op
             return full & Q(**{key: value})
         elif full:
             return full
         elif partial:
-            op, value = self.visit_AttrExprValue(node.value, node.comp_value)
+            op, value = self.visit_AttrExprValue(node)
             key = partial + "__" + op
             return Q(**{key: value})
         else:
@@ -159,20 +159,20 @@ class Transpiler(ast.NodeTransformer):
             return None
         if "." in attr:
             attr = attr.replace(".", "__")
-        op, value = self.visit_AttrExprValue(node.value, node.comp_value)
+        op, value = self.visit_AttrExprValue(node)
         key = attr + "__" + op
         query = Q(**{key: value})
         if node.value == "ne":
             query = ~query
         return query
 
-    def visit_AttrExprValue(self, node_value, node_comp_value):
-        op = self.lookup_op(node_value)
+    def visit_AttrExprValue(self, node):
+        op = self.lookup_op(node.value)
 
-        if node_comp_value:
+        if node.comp_value:
             # There is a comp_value, so visit node and build SQL.
             # prep item_id to be a str replacement placeholder
-            value = self.visit(node_comp_value)
+            value = self.visit(node.comp_value)
         else:
             value = None
 
diff --git a/src/scim2_filter_parser/transpilers/sql.py b/src/scim2_filter_parser/transpilers/sql.py
index 0c3fba4..7128edb 100644
--- a/src/scim2_filter_parser/transpilers/sql.py
+++ b/src/scim2_filter_parser/transpilers/sql.py
@@ -112,28 +112,38 @@ class Transpiler(ast.NodeTransformer):
         if isinstance(node.attr_path.attr_name, scim2ast.Filter):
             full, partial = self.visit_PartialAttrExpr(node.attr_path.attr_name)
             if full and partial:
-                value = self.visit_AttrExprValue(node.value, node.comp_value)
+                value = self.visit_AttrExprValue(node)
                 return f'({full} AND {partial} {value})'
             elif full:
                 return full
             elif partial:
-                value = self.visit_AttrExprValue(node.value, node.comp_value)
+                value = self.visit_AttrExprValue(node)
                 return f'{partial} {value}'
             else:
                 return None
         else:
+            # Case-insensitivity only needs to be checked in this branch
+            # because userName is currently the only attribute that can be case
+            # insensitive and userName can not be a nested part of a complex query (eg.
+            # emails.type in emails[type eq "Primary"]...).
+            # https://datatracker.ietf.org/doc/html/rfc7643#section-4.1.1
             attr = self.visit(node.attr_path)
             if attr is None:
                 return None
-            value = self.visit_AttrExprValue(node.value, node.comp_value)
+
+            value = self.visit_AttrExprValue(node)
+
+            if node.case_insensitive:
+                return f'UPPER({attr}) {value}'
+
             return f'{attr} {value}'
 
-    def visit_AttrExprValue(self, node_value, node_comp_value):
-        op_sql = self.lookup_op(node_value)
+    def visit_AttrExprValue(self, node):
+        op_sql = self.lookup_op(node.value)
 
         item_id = self.get_next_id()
 
-        if not node_comp_value:
+        if not node.comp_value:
             self.params[item_id] = None
             return op_sql
 
@@ -142,15 +152,19 @@ class Transpiler(ast.NodeTransformer):
         # prep item_id to be a str replacement placeholder
         item_id_placeholder = '{' + item_id + '}'
 
-        if 'LIKE' == op_sql:
+        if node.value.lower() in self.matching_op_by_scim_op.keys():
             # Add appropriate % signs to values in LIKE clause
-            prefix, suffix = self.lookup_like_matching(node_value)
-            value = prefix + self.visit(node_comp_value) + suffix
+            prefix, suffix = self.lookup_like_matching(node.value)
+            value = prefix + self.visit(node.comp_value) + suffix
+
         else:
-            value = self.visit(node_comp_value)
+            value = self.visit(node.comp_value)
 
         self.params[item_id] = value
 
+        if node.case_insensitive:
+            return f'{op_sql} UPPER({item_id_placeholder})'
+
         return f'{op_sql} {item_id_placeholder}'
 
     def visit_AttrPath(self, node):


Test patch:
diff --git a/tests/test_query.py b/tests/test_query.py
index ee4161d..7cd9595 100644
--- a/tests/test_query.py
+++ b/tests/test_query.py
@@ -16,6 +16,7 @@ class RFCExamples(unittest.TestCase):
         username TEXT,
         first_name TEXT,
         last_name TEXT,
+        nickname TEXT,
         title TEXT,
         update_ts DATETIME,
         user_type TEXT
@@ -24,17 +25,17 @@ class RFCExamples(unittest.TestCase):
 
     INSERT_USERS = '''
     INSERT INTO users
-        (id, username, first_name, last_name, title, update_ts, user_type)
+        (id, username, first_name, last_name, nickname, title, update_ts, user_type)
     VALUES
-        (1, 'bjensen', 'Brie', 'Jensen', NULL, NULL, NULL),
-        (2, 'momalley', 'Mike', 'O''Malley', NULL, NULL, NULL),
-        (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', NULL, 'Employee'),
-        (4, 'crobertson', 'Carly', 'Robertson', NULL, NULL, 'Intern'),
-
-        (5, 'gt', 'Gina', 'Taylor', NULL, '2012-05-13T04:42:34Z', NULL),
-        (6, 'ge', 'Greg', 'Edgar', NULL, '2011-05-13T04:42:34Z', NULL),
-        (7, 'lt', 'Lisa', 'Ting', NULL, '2010-05-13T04:42:34Z', NULL),
-        (8, 'le', 'Linda', 'Euler', NULL, '2011-05-13T04:42:34Z', NULL)
+        (1, 'bjensen', 'Brie', 'Jensen', 'BB', NULL, NULL, NULL),
+        (2, 'momalley', 'Mike', 'O''Malley', NULL, NULL, NULL, NULL),
+        (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', NULL, 'Employee'),
+        (4, 'crobertson', 'Carly', 'Robertson', NULL, NULL, NULL, 'Intern'),
+
+        (5, 'gt', 'Gina', 'Taylor', NULL, NULL, '2012-05-13T04:42:34Z', NULL),
+        (6, 'ge', 'Greg', 'Edgar', NULL, NULL, '2011-05-13T04:42:34Z', NULL),
+        (7, 'lt', 'Lisa', 'Ting', NULL, NULL, '2010-05-13T04:42:34Z', NULL),
+        (8, 'le', 'Linda', 'Euler', NULL, NULL, '2011-05-13T04:42:34Z', NULL)
     '''
 
     CREATE_TABLE_EMAILS = '''
@@ -95,6 +96,7 @@ class RFCExamples(unittest.TestCase):
         # attr_name, sub_attr, uri: table name
         ('title', None, None): 'users.title',
         ('userName', None, None): 'users.username',
+        ('nickName', None, None): 'users.nickname',
         ('userType', None, None): 'users.user_type',
         ('name', 'familyName', None): 'users.last_name',
         ('meta', 'lastModified', None): 'users.update_ts',
@@ -105,6 +107,7 @@ class RFCExamples(unittest.TestCase):
         ('ims', 'type', None): 'ims.type',
         ('schemas', None, None): 'schemas.text',
         ('userName', None, 'urn:ietf:params:scim:schemas:core:2.0:User'): 'username',
+        ('nickName', None, 'urn:ietf:params:scim:schemas:core:2.0:User'): 'nickname',
     }
 
     JOINS = (
@@ -133,120 +136,120 @@ class RFCExamples(unittest.TestCase):
 
         self.assertEqual(expected_rows, results)
 
-    def test_username_eq(self):
-        query = 'userName eq "bjensen"'
+    def test_nickname_eq(self):
+        query = 'nickName eq "BB"'
         expected_rows = [
-            (1, 'bjensen', 'Brie', 'Jensen', None, None, None)
+            (1, 'bjensen', 'Brie', 'Jensen', 'BB', None, None, None)
         ]
         self.assertRows(query, expected_rows)
 
     def test_family_name_contains(self):
         query = '''name.familyName co "O'Malley"'''
         expected_rows = [
-            (2, 'momalley', 'Mike', "O'Malley", None, None, None),
+            (2, 'momalley', 'Mike', "O'Malley", None, None, None, None),
         ]
         self.assertRows(query, expected_rows)
 
-    def test_username_startswith(self):
-        query = 'userName sw "J"'
+    def test_nickname_startswith(self):
+        query = 'nickName sw "J"'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_schema_username_startswith(self):
-        query = 'urn:ietf:params:scim:schemas:core:2.0:User:userName sw "J"'
+        query = 'urn:ietf:params:scim:schemas:core:2.0:User:nickName sw "J"'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_title_has_value(self):
         query = 'title pr'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_meta_last_modified_gt(self):
         query = 'meta.lastModified gt "2011-05-13T04:42:34Z"'
         expected_rows = [
-            (5, 'gt', 'Gina', 'Taylor', None, '2012-05-13T04:42:34Z', None),
+            (5, 'gt', 'Gina', 'Taylor', None, None, '2012-05-13T04:42:34Z', None),
         ]
         self.assertRows(query, expected_rows)
 
     def test_meta_last_modified_ge(self):
         query = 'meta.lastModified ge "2011-05-13T04:42:34Z"'
         expected_rows = [
-            (5, 'gt', 'Gina', 'Taylor', None, '2012-05-13T04:42:34Z', None),
-            (6, 'ge', 'Greg', 'Edgar', None, '2011-05-13T04:42:34Z', None),
-            (8, 'le', 'Linda', 'Euler', None, '2011-05-13T04:42:34Z', None)
+            (5, 'gt', 'Gina', 'Taylor', None, None, '2012-05-13T04:42:34Z', None),
+            (6, 'ge', 'Greg', 'Edgar', None, None, '2011-05-13T04:42:34Z', None),
+            (8, 'le', 'Linda', 'Euler', None, None, '2011-05-13T04:42:34Z', None)
         ]
         self.assertRows(query, expected_rows)
 
     def test_meta_last_modified_lt(self):
         query = 'meta.lastModified lt "2011-05-13T04:42:34Z"'
         expected_rows = [
-            (7, 'lt', 'Lisa', 'Ting', None, '2010-05-13T04:42:34Z', None),
+            (7, 'lt', 'Lisa', 'Ting', None, None, '2010-05-13T04:42:34Z', None),
         ]
         self.assertRows(query, expected_rows)
 
     def test_meta_last_modified_le(self):
         query = 'meta.lastModified le "2011-05-13T04:42:34Z"'
         expected_rows = [
-            (6, 'ge', 'Greg', 'Edgar', None, '2011-05-13T04:42:34Z', None),
-            (7, 'lt', 'Lisa', 'Ting', None, '2010-05-13T04:42:34Z', None),
-            (8, 'le', 'Linda', 'Euler', None, '2011-05-13T04:42:34Z', None)
+            (6, 'ge', 'Greg', 'Edgar', None, None, '2011-05-13T04:42:34Z', None),
+            (7, 'lt', 'Lisa', 'Ting', None, None, '2010-05-13T04:42:34Z', None),
+            (8, 'le', 'Linda', 'Euler', None, None, '2011-05-13T04:42:34Z', None)
         ]
         self.assertRows(query, expected_rows)
 
     def test_title_has_value_and_user_type_eq(self):
         query = 'title pr and userType eq "Employee"'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_title_has_value_or_user_type_eq(self):
         query = 'title pr or userType eq "Intern"'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
-            (4, 'crobertson', 'Carly', 'Robertson', None, None, 'Intern'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
+            (4, 'crobertson', 'Carly', 'Robertson', None, None, None, 'Intern'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_schemas_eq(self):
         query = 'schemas eq "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User"'
         expected_rows = [
-            (4, 'crobertson', 'Carly', 'Robertson', None, None, 'Intern'),
+            (4, 'crobertson', 'Carly', 'Robertson', None, None, None, 'Intern'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_user_type_eq_and_email_contains_or_email_contains(self):
         query = 'userType eq "Employee" and (emails co "example.com" or emails.value co "example.org")'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_user_type_ne_and_not_email_contains_or_email_contains(self):
         query = 'userType ne "Employee" and not (emails co "example.com" or emails.value co "example.org")'
         expected_rows = [
-            (4, 'crobertson', 'Carly', 'Robertson', None, None, 'Intern'),
+            (4, 'crobertson', 'Carly', 'Robertson', None, None, None, 'Intern'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_user_type_eq_and_not_email_type_eq(self):
         query = 'userType eq "Employee" and (emails.type eq "work")'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
     def test_user_type_eq_and_not_email_type_eq_work_and_value_contains(self):
         query = 'userType eq "Employee" and emails[type eq "work" and value co "@example.com"]'
         expected_rows = [
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
@@ -254,8 +257,8 @@ class RFCExamples(unittest.TestCase):
         query = ('emails[type eq "work" and value co "@example.com"] or '
                  'ims[type eq "xmpp" and value co "@foo.com"]')
         expected_rows = [
-            (1, 'bjensen', 'Brie', 'Jensen', None, None, None),
-            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'Friend', None, 'Employee'),
+            (1, 'bjensen', 'Brie', 'Jensen', 'BB', None, None, None),
+            (3, 'Jacob', 'Jacob', 'Jingleheimer', 'JJ', 'Friend', None, 'Employee'),
         ]
         self.assertRows(query, expected_rows)
 
@@ -441,7 +444,7 @@ class CommandLine(unittest.TestCase):
             '    FROM users',
             '    LEFT JOIN emails ON emails.user_id = users.id',
             '    LEFT JOIN schemas ON schemas.user_id = users.id',
-            '    WHERE username = bjensen;'
+            '    WHERE UPPER(username) = UPPER(bjensen);'
         ]
         self.assertEqual(result, expected)
 
diff --git a/tests/test_transpiler.py b/tests/test_transpiler.py
index 2198cde..dfaf562 100644
--- a/tests/test_transpiler.py
+++ b/tests/test_transpiler.py
@@ -6,52 +6,72 @@ from scim2_filter_parser.lexer import SCIMLexer
 from scim2_filter_parser.parser import SCIMParser
 import scim2_filter_parser.transpilers.sql as transpile_sql
 
+class SetupHelper(TestCase):
+    attr_map = None
 
-class RFCExamples(TestCase):
+    def setUp(self):
+        self.lexer = SCIMLexer()
+        self.parser = SCIMParser()
+
+    def assertSQL(self, query, expected_sql, expected_params, attr_map=None):
+        tokens = self.lexer.tokenize(query)
+        ast = self.parser.parse(tokens)
+
+        if attr_map is None:
+            attr_map = self.attr_map
+
+        transpiler = transpile_sql.Transpiler(attr_map)
+
+        sql, params = transpiler.transpile(ast)
+
+        self.assertEqual(expected_sql, sql, query)
+        self.assertEqual(expected_params, params, query)
+
+
+class RFCExamples(SetupHelper, TestCase):
     attr_map = {
         ('name', 'familyName', None): 'name.familyname',
         ('emails', None, None): 'emails',
         ('emails', 'type', None): 'emails.type',
         ('emails', 'value', None): 'emails.value',
         ('userName', None, None): 'username',
+        ('nickName', None, None): 'nickname',
         ('title', None, None): 'title',
         ('userType', None, None): 'usertype',
         ('schemas', None, None): 'schemas',
         ('userName', None, 'urn:ietf:params:scim:schemas:core:2.0:User'): 'username',
+        ('nickName', None, 'urn:ietf:params:scim:schemas:core:2.0:User'): 'nickname',
         ('meta', 'lastModified', None): 'meta.lastmodified',
         ('ims', 'type', None): 'ims.type',
         ('ims', 'value', None): 'ims.value',
     }
 
-    def setUp(self):
-        self.lexer = SCIMLexer()
-        self.parser = SCIMParser()
-        self.transpiler = transpile_sql.Transpiler(self.attr_map)
-
-    def assertSQL(self, query, expected_sql, expected_params):
-        tokens = self.lexer.tokenize(query)
-        ast = self.parser.parse(tokens)
-        sql, params = self.transpiler.transpile(ast)
-
-        self.assertEqual(expected_sql, sql, query)
-        self.assertEqual(expected_params, params, query)
-
     def test_attr_paths_are_created(self):
         query = 'userName eq "bjensen"'
         tokens = self.lexer.tokenize(query)
         ast = self.parser.parse(tokens)
-        self.transpiler.transpile(ast)
 
-        self.assertEqual(len(self.transpiler.attr_paths), 1)
-        for path in self.transpiler.attr_paths:
+        transpiler = transpile_sql.Transpiler(self.attr_map)
+        transpiler.transpile(ast)
+
+        self.assertEqual(len(transpiler.attr_paths), 1)
+        for path in transpiler.attr_paths:
             self.assertTrue(isinstance(path, transpile_sql.AttrPath))
 
+    # userName is always case-insensitive
+    # https://datatracker.ietf.org/doc/html/rfc7643#section-4.1.1
     def test_username_eq(self):
         query = 'userName eq "bjensen"'
-        sql = "username = {a}"
+        sql = "UPPER(username) = UPPER({a})"
         params = {'a': 'bjensen'}
         self.assertSQL(query, sql, params)
 
+    def test_nickname_eq(self):
+        query = 'nickName eq "Bob"'
+        sql = "nickname = {a}"
+        params = {'a': 'Bob'}
+        self.assertSQL(query, sql, params)
+
     def test_family_name_contains(self):
         query = '''name.familyName co "O'Malley"'''
         sql = "name.familyname LIKE {a}"
@@ -60,13 +80,25 @@ class RFCExamples(TestCase):
 
     def test_username_startswith(self):
         query = 'userName sw "J"'
-        sql = "username LIKE {a}"
+        sql = "UPPER(username) LIKE UPPER({a})"
+        params = {'a': 'J%'}
+        self.assertSQL(query, sql, params)
+
+    def test_nickname_startswith(self):
+        query = 'nickName sw "J"'
+        sql = "nickname LIKE {a}"
         params = {'a': 'J%'}
         self.assertSQL(query, sql, params)
 
     def test_schema_username_startswith(self):
         query = 'urn:ietf:params:scim:schemas:core:2.0:User:userName sw "J"'
-        sql = "username LIKE {a}"
+        sql = "UPPER(username) LIKE UPPER({a})"
+        params = {'a': 'J%'}
+        self.assertSQL(query, sql, params)
+
+    def test_schema_nickname_startswith(self):
+        query = 'urn:ietf:params:scim:schemas:core:2.0:User:nickName sw "J"'
+        sql = "nickname LIKE {a}"
         params = {'a': 'J%'}
         self.assertSQL(query, sql, params)
 
@@ -149,27 +181,35 @@ class RFCExamples(TestCase):
         params = {'a': 'work', 'b': '%@example.com%', 'c': 'xmpp', 'd': '%@foo.com%'}
         self.assertSQL(query, sql, params)
 
+    def test_username_with_more_complex_query(self):
+        query = (
+            'emails[type eq "work" and value co "@example.com"] or '
+            'ims[type eq "xmpp" and value co "@foo.com"] or '
+            'userName eq "bjensen"'
+        )
+        sql = (
+            '(((emails.type = {a}) AND (emails.value LIKE {b})) OR '
+            '((ims.type = {c}) AND (ims.value LIKE {d}))) OR '
+            '(UPPER(username) = UPPER({e}))'
+        )
+        params = {
+            'a': 'work',
+            'b': '%@example.com%',
+            'c': 'xmpp',
+            'd': '%@foo.com%',
+            'e': 'bjensen',
+        }
+        self.assertSQL(query, sql, params)
 
-class UndefinedAttributes(TestCase):
-
-    def setUp(self):
-        self.lexer = SCIMLexer()
-        self.parser = SCIMParser()
-
-    def assertSQL(self, query, attr_map, expected_sql, expected_params):
-        tokens = self.lexer.tokenize(query)
-        ast = self.parser.parse(tokens)
-        sql, params = transpile_sql.Transpiler(attr_map).transpile(ast)
 
-        self.assertEqual(expected_sql, sql, query)
-        self.assertEqual(expected_params, params, query)
+class UndefinedAttributes(SetupHelper, TestCase):
 
     def test_username_eq(self):
         query = 'userName eq "bjensen"'
         sql = None
         params = {}
         attr_map = {}
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_title_has_value_and_user_type_eq_1(self):
         query = 'title pr and userType eq "Employee"'
@@ -178,7 +218,7 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('title', None, None): 'title',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_title_has_value_and_user_type_eq_2(self):
         query = 'title pr and userType eq "Employee"'
@@ -187,14 +227,14 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('userType', None, None): 'usertype',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_schemas_eq(self):
         query = 'schemas eq "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User"'
         sql = None
         params = {}
         attr_map = {}
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_eq_and_email_contains_or_email_contains(self):
         query = 'userType eq "Employee" and (emails co "example.com" or emails.value co "example.org")'
@@ -205,7 +245,7 @@ class UndefinedAttributes(TestCase):
             ('emails', None, None): 'emails',
             ('emails', 'value', None): 'emails.value',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_ne_and_not_email_contains_or_email_contains(self):
         query = 'userType ne "Employee" and not (emails co "example.com" or emails.value co "example.org")'
@@ -216,7 +256,7 @@ class UndefinedAttributes(TestCase):
             ('emails', None, None): 'emails',
             ('emails', 'value', None): 'emails.value',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_eq_and_not_email_type_eq_1(self):
         query = 'userType eq "Employee" and (emails.type eq "work")'
@@ -225,7 +265,7 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('userType', None, None): 'usertype',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_eq_and_not_email_type_eq_2(self):
         query = 'userType eq "Employee" and (emails.type eq "work")'
@@ -234,7 +274,7 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('emails', 'type', None): 'emails.type',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_eq_and_not_email_type_eq_work_and_value_contains_1(self):
         query = 'userType eq "Employee" and emails[type eq "work" and value co "@example.com"]'
@@ -243,7 +283,7 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('userType', None, None): 'usertype',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_eq_and_not_email_type_eq_work_and_value_contains_2(self):
         query = 'userType eq "Employee" and emails[type eq "work" and value co "@example.com"]'
@@ -252,7 +292,7 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('emails', 'type', None): 'emails.type',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_user_type_eq_and_not_email_type_eq_work_and_value_contains_3(self):
         query = 'userType eq "Employee" and emails[type eq "work" and value co "@example.com"]'
@@ -262,7 +302,7 @@ class UndefinedAttributes(TestCase):
             ('emails', 'type', None): 'emails.type',
             ('emails', 'value', None): 'emails.value',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_emails_type_eq_work_value_contians_or_ims_type_eq_and_value_contians_1(self):
         query = ('emails[type eq "work" and value co "@example.com"] or '
@@ -273,7 +313,7 @@ class UndefinedAttributes(TestCase):
             ('emails', 'value', None): 'emails.value',
             ('ims', 'type', None): 'ims.type',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_emails_type_eq_work_value_contians_or_ims_type_eq_and_value_contians_2(self):
         query = ('emails[type eq "work" and value co "@example.com"] or '
@@ -285,7 +325,7 @@ class UndefinedAttributes(TestCase):
             ('ims', 'type', None): 'ims.type',
             ('ims', 'value', None): 'ims.value',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_emails_type_eq_work_value_contians_or_ims_type_eq_and_value_contians_3(self):
         query = ('emails[type eq "work" and value co "@example.com"] or '
@@ -297,7 +337,7 @@ class UndefinedAttributes(TestCase):
             ('emails', 'type', None): 'emails.type',
             ('ims', 'type', None): 'ims.type',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_emails_type_eq_work_value_contians_or_ims_type_eq_and_value_contians_4(self):
         query = ('emails[type eq "work" and value co "@example.com"] or '
@@ -308,7 +348,7 @@ class UndefinedAttributes(TestCase):
             ('emails', 'type', None): 'emails.type',
             ('ims', 'value', None): 'ims.value',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_email_type_eq_primary_value_eq_uuid_1(self):
         query = 'emails[type eq "Primary"].value eq "001750ca-8202-47cd-b553-c63f4f245940"'
@@ -317,7 +357,7 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('emails', 'value', None): 'emails.value',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
     def test_email_type_eq_primary_value_eq_uuid_2(self):
         query = 'emails[type eq "Primary"].value eq "001750ca-8202-47cd-b553-c63f4f245940"'
@@ -326,29 +366,16 @@ class UndefinedAttributes(TestCase):
         attr_map = {
             ('emails', 'type', None): 'emails.type',
         }
-        self.assertSQL(query, attr_map, sql, params)
+        self.assertSQL(query, sql, params, attr_map)
 
 
-class AzureQueries(TestCase):
+class AzureQueries(SetupHelper, TestCase):
     attr_map = {
         ('emails', 'type', None): 'emails.type',
         ('emails', 'value', None): 'emails.value',
         ('externalId', None, None): 'externalid',
     }
 
-    def setUp(self):
-        self.lexer = SCIMLexer()
-        self.parser = SCIMParser()
-        self.transpiler = transpile_sql.Transpiler(self.attr_map)
-
-    def assertSQL(self, query, expected_sql, expected_params):
-        tokens = self.lexer.tokenize(query)
-        ast = self.parser.parse(tokens)
-        sql, params = self.transpiler.transpile(ast)
-
-        self.assertEqual(expected_sql, sql, query)
-        self.assertEqual(expected_params, params, query)
-
     def test_email_type_eq_primary_value_eq_uuid(self):
         query = 'emails[type eq "Primary"].value eq "001750ca-8202-47cd-b553-c63f4f245940"'
         sql = "(emails.type = {a} AND emails.value = {b})"
@@ -368,24 +395,11 @@ class AzureQueries(TestCase):
         self.assertSQL(query, sql, params)
 
 
-class GitHubBugsQueries(TestCase):
+class GitHubBugsQueries(SetupHelper, TestCase):
     attr_map = {
         ('emails', 'type', None): 'emails.type',
     }
 
-    def setUp(self):
-        self.lexer = SCIMLexer()
-        self.parser = SCIMParser()
-        self.transpiler = transpile_sql.Transpiler(self.attr_map)
-
-    def assertSQL(self, query, expected_sql, expected_params):
-        tokens = self.lexer.tokenize(query)
-        ast = self.parser.parse(tokens)
-        sql, params = self.transpiler.transpile(ast)
-
-        self.assertEqual(expected_sql, sql, query)
-        self.assertEqual(expected_params, params, query)
-
     def test_g15_ne_op(self):
         query = 'emails[type ne "work"]'
         sql = "emails.type != {a}"
@@ -405,7 +419,7 @@ class CommandLine(TestCase):
         transpile_sql.main(['userName eq "bjensen"'])
         result = self.test_stdout.getvalue().strip().split('\n')
         expected = [
-            'SQL: username = {a}',
+            'SQL: UPPER(username) = UPPER({a})',
             "PARAMS: {'a': 'bjensen'}"
         ]
         self.assertEqual(result, expected)