Error Unresolved

Getting this error on the following code

Error
Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

SQL Query

SELECT sku as sku_code, collection_name, brand_name
FROM (SELECT catalog_product_entity.entity_id, sku, value
FROM catalog_product_entity
JOIN catalog_product_entity_int ON catalog_product_entity.entity_id = catalog_product_entity_int.entity_id
WHERE attribute_id = 187) AS subquery
JOIN collection_info ON option_id = subquery.value
Where brand_name='X' and collection_name='Y'

Hi @Anum
As the error say, you cannot mix collations. Metabase expects your application database to be 5.7.7 or newer, with charset utf8mb4 and collation utf8mb4_unicode_ci:
https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html#mysql-or-mariadb

don't get the error. Please expand here. How can I fix this query to make this work?

@Anum It has nothing to do with your query, but incorrect configuration of your Metabase application database, which is MySQL in your case.
You need to make sure that you are using charset utf8mb4 and collation utf8mb4_unicode_ci.
https://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5

It's a view column and not a base table. So the above link is not working

@Anum You are misunderstanding what I'm writing.

Post "Diagnostic Info" from Admin > Troubleshooting.

In your setup of Metabase, you have set the Metabase application database (where Metabase stores all it's information) to MySQL.
That database needs to be charset utf8mb4 and collation utf8mb4_unicode_ci - including for every table and column within.

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9-LTS",
"java.vendor": "Amazon.com Inc.",
"java.vendor.url": "https://aws.amazon.com/corretto/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9-LTS",
"os.name": "Linux",
"os.version": "4.14.225-169.362.amzn2.x86_64",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.34-log"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"date": "2020-12-03",
"tag": "v0.37.3",
"branch": "release-x.37.x",
"hash": "2f1e783"
},
"settings": {
"report-timezone": "UTC"
}
}
}

@Anum I can only see that you're running on AWS, but not if you're using JAR or Docker, but change collation on the application database for the database and all tables and all columns.

It is running fine locally in debeaver.

@Anum I'm just going to stop writing, since you are not reading what I'm writing.
You are misunderstanding the difference between data source queries and the application database.