BigQuery SELECT statement with WHERE clause fail only on BQ CLI

Question

I am running simple select query in BigQuery GCP Console and it works perfectly fine. But, when I run the same query using BQ CLI, it fails.

When I run the same query without the "WHERE" clause, it works.

SELECT field_path FROM `GCP_PROJECT_ID.MY_DATASET.INFORMATION_SCHEMA.COLUMN_FIELD_PATH`
WHERE table_name="MY_TABLE_NAME"

Below is the error message

Error in query string: Error processing job 'GCP_project_ID:jobidxxxxx': Unrecognized name: MY_DATASET at [1:1xx]

I have tried the following "WHERE" clauses as well. None of these work as well.

... WHERE table_name IN ("MY_TABLE_NAME")
... WHERE table_name like "%MY_TABLE_NAME%"

Answer 1

I have reproduced your query on my own datasets using the Command Line tool and it worked fine. This is the command I ran:

bq query --use_legacy_sql=false 'SELECT field_path FROM `<projectName>.<DatasetName>.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`where table_name="<TableName>"'