Caching date-based SQL queries

[pelguetat]

So I've managed to get the cache working with Sqlite and Milvus against my Postgres db, and it works quite well. The issue I'm having is that for date based queries like:
SELECT EXTRACT(MONTH FROM transaction_date) AS month, SUM(sale_amount) AS total_sales
FROM transactions
WHERE EXTRACT(YEAR FROM transaction_date) = 2023
GROUP BY month
ORDER BY total_sales DESC
LIMIT 5;

The cached will be hit even if the dates don't match, which obviously makes the query useless, but also you wouldn't want to have a cached query per possible date.
The solutions I'm thinking are:

1. Pass the cached response through an LLM to fix the date, which sorts of defeats the purpose of caching.
2. Passed the cached response through a function that replaces the dates. This could work but would get complex for multiple different query formats.
   Any thoughts?

[SimFG]

Maybe you can get the date entered by the user in similarity evaluation, compare it with the date in the cache, and then if it is same, make other cache similar judgments, such as distance, etc.

[pelguetat]

The thing is, I want the cache to also work if the date is not exactly the same, but the core of the query is the same. So, if I ask, what was the revenue on march 2022, and then what was the revenue in april 2023, it would still pull the same cached query, but replace for the appropriate dates.

…

On Thu, 13 Jul 2023, 09:44 SimFG, ***@***.***> wrote: Maybe you can get the date entered by the user in similarity evaluation, compare it with the date in the cache, and then if it is same, make other cache similar judgments, such as distance, etc. — Reply to this email directly, view it on GitHub <#484 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AF7NBV5QJUPS2CFMLDPSTM3XP6YQHANCNFSM6AAAAAA2IS4WZU> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[SimFG]

@pelguetat got it. This feeling is a bit difficult to realize, because there is no way to figure out which part needs to be replaced through pure code, and it is also impossible to extract the value of the replacement. The ideal would probably require reasoning through a small llm.

[pelguetat]

I know, although it kind of defeats the purpose of caching if you use an llm lol. I'm currently using langchain's sqlchain, so I'm thinking maybe I just decompose that and intercept the query before it's performed, and use spacy or something like that to rewrite it and then execute and continue the flow.

…

On Thu, 13 Jul 2023, 12:19 SimFG, ***@***.***> wrote: @pelguetat <https://github.com/pelguetat> got it. This feeling is a bit difficult to realize, because there is no way to figure out which part needs to be replaced through pure code, and it is also impossible to extract the value of the replacement. The ideal would probably require reasoning through a small llm. — Reply to this email directly, view it on GitHub <#484 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AF7NBV2YUPLDYOA7XTBKLNLXP7KSZANCNFSM6AAAAAA2IS4WZU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[SimFG]

yes, you are right. This will be very troublesome if combined