[ISSUE] Unable to aggregate on date when using a view

[jdziat]

Describe the bug
Unable to group and aggregate on a table or view using acra. The same queries work when executing the queries.

To Reproduce
Steps to reproduce the behavior:

1. Create a table with the following fields: date, amount,name,user_id
2. Create a view based on the table using user_id to filter the requests coming in
3. Query the view and group based on a date field
4. Null is returned
   Expected behavior

We would expect this to behave the same way as a traditional table behaves

Acra configuration files
For AcraServer:
- [ ] configuration file or CLI params that you use to start AcraServer;
- [ ] encryptor_config.yaml if used.
For AcraTranslator:
- [ ] configuration file or CLI params that you use to start AcraTranslator.

Environment (please complete the following information):

• Acra version: latest
• Database server and its version: MySQL
• Installed components:
  • AcraServer
  • AcraTranslator
• Data-in-transit encryption between Acra and the client-side application:
  • TLS
  • AcraConnector
  • no transport encryption
• Installation way:
  • via Docker
  • via package manager
  • via Kubernetes

Additional context
Add any other context about the problem here.

[jdziat]

Example Schema:

• amount
• merchant_name (encrypted)
• date

Some example queries that work:

select merchant_name, sum(amount) from trans where merchant_name = 'Acme'

Example of query not working:

select month(date), merchant_name, sum(amount) from trans where date >'2025-01-01' and merchant_name='Acme'
group by month(date)

[Zhaars]

Hello @jdziat, thank you for reporting this issue. Unfortunately, we were able to reproduce the problem, and it appears that the MySQL parser used in the current version does not support expressions like MONTH(date). As a result, Acra simply ignores such queries.

As a temporary workaround, you can try rewriting the query without using this specific function. If this is a critical use case for you, please note that support for an updated parser is already on our roadmap and will be included in a future product release.

We appreciate your patience and feedback.