Filter Operation not working for Source: MySQL and Target: Spanner

[oll-brajesh]

Trying to run the filter feature but getting the following error:

Command:

data-validation validate column \    
    -sc MySQL \
    -tc Spanner \
    -tbls test_db.people=database-id.test_table \
    --filters 'age=35'

Error Message:

Traceback (most recent call last):
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/bin/data-validation", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/__main__.py", line 587, in main
    validate(args)
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/__main__.py", line 565, in validate
    run(args)
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/__main__.py", line 532, in run
    run_validations(args, config_managers)
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/__main__.py", line 488, in run_validations
    run_validation(config_manager, dry_run=args.dry_run, verbose=args.verbose)
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/__main__.py", line 461, in run_validation
    validator.execute()
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/data_validation.py", line 96, in execute
    result_df = self._execute_validation(
                ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/data_validation/data_validation.py", line 307, in _execute_validation
    target_df = futures[1].result()
                ^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/Cellar/python@3.11/3.11.6/Frameworks/Python.framework/Versions/3.11/lib/python3.11/concurrent/futures/_base.py", line 449, in result
    return self.__get_result()
           ^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/Cellar/python@3.11/3.11.6/Frameworks/Python.framework/Versions/3.11/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
  File "/opt/homebrew/Cellar/python@3.11/3.11.6/Frameworks/Python.framework/Versions/3.11/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/third_party/ibis/ibis_cloud_spanner/__init__.py", line 150, in execute
    sql = query_ast.compile()
          ^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/base.py", line 40, in compile
    compiled_queries = [q.compile() for q in self.queries]
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/base.py", line 40, in <listcomp>
    compiled_queries = [q.compile() for q in self.queries]
                        ^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/query_builder.py", line 257, in compile
    where_frag = self.format_where()
                 ^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/query_builder.py", line 393, in format_where
    new_pred = self._translate(pred, permit_subquery=True)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/query_builder.py", line 221, in _translate
    return translator.get_result()
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/translator.py", line 206, in get_result
    translated = self.translate(self.node)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/brajeshsharma/Desktop/docker_mongos/dv_test/env/lib/python3.11/site-packages/ibis/backends/base/sql/compiler/translator.py", line 240, in translate
    raise com.OperationNotDefinedError(f'No translation rule for {type(op)}')
ibis.common.exceptions.OperationNotDefinedError: No translation rule for <class 'third_party.ibis.ibis_addon.operations.RawSQL'>

[helensilva14]

Hi @brajesh-cldcvr! Which version of DVT and MySQL are you using?

[helensilva14]

@brajesh-cldcvr Actually I was able to reproduce it in our latest DVT version and it was indeed a bug, we were missing a mapping to enable the SQL creation for filtering clauses. I just created a PR to fix it and soon it'll be merged to our develop branch. Thank you for opening this issue!