'Test to ensure that cql filtering where clauses are properly supported in the python driver.
test_mv_filtering Tests that various complex MV where clauses produce the correct results. It also validates that
these results and the grammar is supported appropriately.
@since 3.0.0
@jira_ticket PYTHON-399
@expected_result Materialized view where clauses should produce the appropriate results.
@test_category materialized_view'

def test_mv_filtering(self):

    create_table = 'CREATE    TABLE    {0}.scores(\n                                                                                                user    TEXT,\n                                                                                                game    TEXT,\n                                                                                                year    INT,\n                                                                                                month    INT,\n                                                                                                day    INT,\n                                                                                                score    INT,\n                                                                                                PRIMARY    KEY    (user,    game,    year,    month,    day)\n                                                                                                )'.format(self.keyspace_name)
    self.session.execute(create_table)
    create_mv_alltime = 'CREATE    MATERIALIZED    VIEW    {0}.alltimehigh    AS\n                                                                                                SELECT    *    FROM    {0}.scores\n                                                                                                WHERE    game    IS    NOT    NULL    AND    score    IS    NOT    NULL    AND    user    IS    NOT    NULL    AND    year    IS    NOT    NULL    AND    month    IS    NOT    NULL    AND    day    IS    NOT    NULL\n                                                                                                PRIMARY    KEY    (game,    score,    user,    year,    month,    day)\n                                                                                                WITH    CLUSTERING    ORDER    BY    (score    DESC)'.format(self.keyspace_name)
    create_mv_dailyhigh = 'CREATE    MATERIALIZED    VIEW    {0}.dailyhigh    AS\n                                                                                                SELECT    *    FROM    {0}.scores\n                                                                                                WHERE    game    IS    NOT    NULL    AND    year    IS    NOT    NULL    AND    month    IS    NOT    NULL    AND    day    IS    NOT    NULL    AND    score    IS    NOT    NULL    AND    user    IS    NOT    NULL\n                                                                                                PRIMARY    KEY    ((game,    year,    month,    day),    score,    user)\n                                                                                                WITH    CLUSTERING    ORDER    BY    (score    DESC)'.format(self.keyspace_name)
    create_mv_monthlyhigh = 'CREATE    MATERIALIZED    VIEW    {0}.monthlyhigh    AS\n                                                                                                SELECT    *    FROM    {0}.scores\n                                                                                                WHERE    game    IS    NOT    NULL    AND    year    IS    NOT    NULL    AND    month    IS    NOT    NULL    AND    score    IS    NOT    NULL    AND    user    IS    NOT    NULL    AND    day    IS    NOT    NULL\n                                                                                                PRIMARY    KEY    ((game,    year,    month),    score,    user,    day)\n                                                                                                WITH    CLUSTERING    ORDER    BY    (score    DESC)'.format(self.keyspace_name)
    create_mv_filtereduserhigh = "CREATE    MATERIALIZED    VIEW    {0}.filtereduserhigh    AS\n                                                                                                SELECT    *    FROM    {0}.scores\n                                                                                                WHERE    user    in    ('jbellis',    'pcmanus')    AND    game    IS    NOT    NULL    AND    score    IS    NOT    NULL    AND    year    is    NOT    NULL    AND    day    is    not    NULL    and    month    IS    NOT    NULL\n                                                                                                PRIMARY    KEY    (game,    score,    user,    year,    month,    day)\n                                                                                                WITH    CLUSTERING    ORDER    BY    (score    DESC)".format(self.keyspace_name)
    self.session.execute(create_mv_alltime)
    self.session.execute(create_mv_dailyhigh)
    self.session.execute(create_mv_monthlyhigh)
    self.session.execute(create_mv_filtereduserhigh)
    prepared_insert = self.session.prepare('INSERT    INTO    {0}.scores    (user,    game,    year,    month,    day,    score)    VALUES        (?,    ?,    ?    ,?    ,?,    ?)'.format(self.keyspace_name))
    bound = prepared_insert.bind(('pcmanus', 'Coup', 2015, 5, 1, 4000))
    self.session.execute(bound)
    bound = prepared_insert.bind(('jbellis', 'Coup', 2015, 5, 3, 1750))
    self.session.execute(bound)
    bound = prepared_insert.bind(('yukim', 'Coup', 2015, 5, 3, 2250))
    self.session.execute(bound)
    bound = prepared_insert.bind(('tjake', 'Coup', 2015, 5, 3, 500))
    self.session.execute(bound)
    bound = prepared_insert.bind(('iamaleksey', 'Coup', 2015, 6, 1, 2500))
    self.session.execute(bound)
    bound = prepared_insert.bind(('tjake', 'Coup', 2015, 6, 2, 1000))
    self.session.execute(bound)
    bound = prepared_insert.bind(('pcmanus', 'Coup', 2015, 6, 2, 2000))
    self.session.execute(bound)
    bound = prepared_insert.bind(('jmckenzie', 'Coup', 2015, 6, 9, 2700))
    self.session.execute(bound)
    bound = prepared_insert.bind(('jbellis', 'Coup', 2015, 6, 20, 3500))
    self.session.execute(bound)
    bound = prepared_insert.bind(('jbellis', 'Checkers', 2015, 6, 20, 1200))
    self.session.execute(bound)
    bound = prepared_insert.bind(('jbellis', 'Chess', 2015, 6, 21, 3500))
    self.session.execute(bound)
    bound = prepared_insert.bind(('pcmanus', 'Chess', 2015, 1, 25, 3200))
    self.session.execute(bound)
    query_statement = SimpleStatement("SELECT    *    FROM    {0}.alltimehigh    WHERE    game='Coup'".format(self.keyspace_name), consistency_level=ConsistencyLevel.QUORUM)
    results = self.session.execute(query_statement)
    self.assertEqual(results[0].game, 'Coup')
    self.assertEqual(results[0].year, 2015)
    self.assertEqual(results[0].month, 5)
    self.assertEqual(results[0].day, 1)
    self.assertEqual(results[0].score, 4000)
    self.assertEqual(results[0].user, 'pcmanus')
    prepared_query = self.session.prepare('SELECT    *    FROM    {0}.dailyhigh    WHERE    game=?    AND    year=?    AND    month=?    and    day=?'.format(self.keyspace_name))
    bound_query = prepared_query.bind(('Coup', 2015, 6, 2))
    results = self.session.execute(bound_query)
    self.assertEqual(results[0].game, 'Coup')
    self.assertEqual(results[0].year, 2015)
    self.assertEqual(results[0].month, 6)
    self.assertEqual(results[0].day, 2)
    self.assertEqual(results[0].score, 2000)
    self.assertEqual(results[0].user, 'pcmanus')
    self.assertEqual(results[1].game, 'Coup')
    self.assertEqual(results[1].year, 2015)
    self.assertEqual(results[1].month, 6)
    self.assertEqual(results[1].day, 2)
    self.assertEqual(results[1].score, 1000)
    self.assertEqual(results[1].user, 'tjake')
    prepared_query = self.session.prepare('SELECT    *    FROM    {0}.monthlyhigh    WHERE    game=?    AND    year=?    AND    month=?    and    score    >=    ?    and    score    <=    ?'.format(self.keyspace_name))
    bound_query = prepared_query.bind(('Coup', 2015, 6, 2500, 3500))
    results = self.session.execute(bound_query)
    self.assertEqual(results[0].game, 'Coup')
    self.assertEqual(results[0].year, 2015)
    self.assertEqual(results[0].month, 6)
    self.assertEqual(results[0].day, 20)
    self.assertEqual(results[0].score, 3500)
    self.assertEqual(results[0].user, 'jbellis')
    self.assertEqual(results[1].game, 'Coup')
    self.assertEqual(results[1].year, 2015)
    self.assertEqual(results[1].month, 6)
    self.assertEqual(results[1].day, 9)
    self.assertEqual(results[1].score, 2700)
    self.assertEqual(results[1].user, 'jmckenzie')
    self.assertEqual(results[2].game, 'Coup')
    self.assertEqual(results[2].year, 2015)
    self.assertEqual(results[2].month, 6)
    self.assertEqual(results[2].day, 1)
    self.assertEqual(results[2].score, 2500)
    self.assertEqual(results[2].user, 'iamaleksey')
    query_statement = SimpleStatement("SELECT    *    FROM    {0}.filtereduserhigh    WHERE    game='Chess'".format(self.keyspace_name), consistency_level=ConsistencyLevel.QUORUM)
    results = self.session.execute(query_statement)
    self.assertEqual(results[0].game, 'Chess')
    self.assertEqual(results[0].year, 2015)
    self.assertEqual(results[0].month, 6)
    self.assertEqual(results[0].day, 21)
    self.assertEqual(results[0].score, 3500)
    self.assertEqual(results[0].user, 'jbellis')
    self.assertEqual(results[1].game, 'Chess')
    self.assertEqual(results[1].year, 2015)
    self.assertEqual(results[1].month, 1)
    self.assertEqual(results[1].day, 25)
    self.assertEqual(results[1].score, 3200)
    self.assertEqual(results[1].user, 'pcmanus')
