spider
spider copied to clipboard
taoyds
Not supporting any MySQL functions!
I'm trying to generate the parse sql queries for any functions like DATE_FORMAT or any other functions using script in the spider repository, preprocess/parse_one_sql.py to generate this parsed sql query and it is working perfectly for some queries, but when i tried this query ,SELECT DATE_FORMAT(date_complaint_raised,'%Y-%m'), count(*) FROM Complaints WHERE YEAR(date_complaint_raised) = value GROUP BY DATE_FORMAT(date_complaint_raised,'%Y-%m'), it gives some date-format assertion error. , while it is working fine with the simple queries(which don't have any function). Can any one please suggest a way to make this script to work for functions as well, and create the parse queries for it. Btw the error im getting is given as.
@danielhers @ryanzhumich @IsaWdx @KylinChang @yangkai2g7k
You need to add extra functionality in process_sql.py
Look for AGG_OPS global variable and its' usages. Functions can be implemented the same way, but you'll also need to support comma symbol.
You need to add extra functionality in
process_sql.pyLook forAGG_OPSglobal variable and its' usages. Functions can be implemented the same way, but you'll also need to support comma symbol.
Thankyou so much for replying one more question, like the AGG_OPS, have some limited functions, but the MySQL or other databases has lots of functions, so should i have to put all of them in a predefined variable? And also the comma you are talking ablut, can you elaborate more, like whats the role of it?
Yes, you should put all the functions in predefined variable. I think you can start with simply extending AGG_OPS with your function (e.g. date_format) but I don't guarantee you that everything would work as expected as this function has two parameters.
Talking about comma: I think that it may be interpreted incorrectly by the whole parser as currently comma is used only in Select query to separate columns and not to separate parameters of functions (this may mean that there's some code like: split select part by comma and parse each part with easier parser, which would lead to incorrect results because it would simply split date_format call into two separate strings and will try to analyze each of them independently and fail.
Oh, thanks, alot for your suggestion, it seems like a lot of work. I have to be very precise on writing the code, specifically for handling the function parameters. I'll try to make it worked.
Hi @CrafterKolyan i tried to write the code that support functions, but i was facing the many difficulties, like to differentiate between the blocks DATE_FORMAT(date_complaint_raised,'%Y-%m') and Count(*), parser getting confused , like which is what. Can you please help in it?