evalengine: Support all built-in MySQL functions

vmg commented 2 years ago

The last major shortcoming of the evaluation engine are the many built-in SQL functions that ship with MySQL and which we currently do not support. Since adding support for these functions is an arduous, iterative project, this tracking issue intends to act as an authoritative list of the progress we've made bringing these functions over.

Comparison Operators

[X] > Greater than operator
[X] >= Greater than or equal operator
[X] < Less than operator
[X] <>, != Not equal operator
[X] <= Less than or equal operator
[X] <=> NULL-safe equal to operator
[X] = Equal operator
[x] BETWEEN ... AND ... Whether a value is within a range of values
[X] COALESCE() Return the first non-NULL argument
[X] GREATEST() Return the largest argument
[X] IN() Whether a value is within a set of values
[x] INTERVAL() Return the index of the argument that is less than the first argument
[X] IS Test a value against a boolean
[X] IS NOT Test a value against a boolean
[X] IS NOT NULL NOT NULL value test
[X] IS NULL NULL value test
[X] ISNULL() Test whether the argument is NULL
[X] LEAST() Return the smallest argument
[X] LIKE Simple pattern matching
[x] NOT BETWEEN ... AND ... Whether a value is not within a range of values
[X] NOT IN() Whether a value is not within a set of values
[X] NOT LIKE Negation of simple pattern matching
[x] STRCMP() Compare two strings

Logical Operators

[X] AND, && Logical AND
[x] NOT, ! Negates value
[X] OR, || Logical OR
[x] XOR Logical XOR

Assignment Operators

[ ] := Assign a value
[ ] = Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)

Flow Control Functions

[x] CASE Case operator
[x] IF() If/else construct
[x] IFNULL() Null if/else construct
[x] NULLIF() Return NULL if expr1 = expr2

Numeric Functions and Operators

[x] %, MOD Modulo operator
[X] * Multiplication operator
[X] + Addition operator
[X] - Minus operator
[X] - Change the sign of the argument
[X] / Division operator
[x] ABS() Return the absolute value
[x] ACOS() Return the arc cosine
[x] ASIN() Return the arc sine
[x] ATAN() Return the arc tangent
[x] ATAN2(), ATAN() Return the arc tangent of the two arguments
[x] CEIL() Return the smallest integer value not less than the argument
[x] CEILING() Return the smallest integer value not less than the argument
[x] CONV() Convert numbers between different number bases
[x] COS() Return the cosine
[x] COT() Return the cotangent
[x] CRC32() Compute a cyclic redundancy check value
[x] DEGREES() Convert radians to degrees
[x] DIV Integer division
[x] EXP() Raise to the power of
[x] FLOOR() Return the largest integer value not greater than the argument
[x] LN() Return the natural logarithm of the argument
[x] LOG() Return the natural logarithm of the first argument
[x] LOG10() Return the base-10 logarithm of the argument
[x] LOG2() Return the base-2 logarithm of the argument
[x] MOD() Return the remainder
[x] PI() Return the value of pi
[x] POW() Return the argument raised to the specified power
[x] POWER() Return the argument raised to the specified power
[x] RADIANS() Return argument converted to radians
[ ] RAND() Return a random floating-point value
[x] ROUND() Round the argument
[x] SIGN() Return the sign of the argument
[x] SIN() Return the sine of the argument
[x] SQRT() Return the square root of the argument
[x] TAN() Return the tangent of the argument
[x] TRUNCATE() Truncate to specified number of decimal places

Date and Time Functions

[x] ADDDATE() Add time values (intervals) to a date value
[ ] ADDTIME() Add time
[x] CONVERT_TZ() Convert from one time zone to another
[x] CURDATE() Return the current date
[x] CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
[x] CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
[x] CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
[x] CURTIME() Return the current time
[x] DATE() Extract the date part of a date or datetime expression
[x] DATE_ADD() Add time values (intervals) to a date value
[x] DATE_FORMAT() Format date as specified
[x] DATE_SUB() Subtract a time value (interval) from a date
[ ] DATEDIFF() Subtract two dates
[x] DAY() Synonym for DAYOFMONTH()
[x] DAYNAME() Return the name of the weekday
[x] DAYOFMONTH() Return the day of the month (0-31)
[x] DAYOFWEEK() Return the weekday index of the argument
[x] DAYOFYEAR() Return the day of the year (1-366)
[ ] EXTRACT() Extract part of a date
[x] FROM_DAYS() Convert a day number to a date
[x] FROM_UNIXTIME() Format Unix timestamp as a date
[ ] GET_FORMAT() Return a date format string
[x] HOUR() Extract the hour
[x] LAST_DAY Return the last day of the month for the argument
[x] LOCALTIME(), LOCALTIME Synonym for NOW()
[x] LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
[x] MAKEDATE() Create a date from the year and day of year
[x] MAKETIME() Create time from hour, minute, second
[x] MICROSECOND() Return the microseconds from argument
[x] MINUTE() Return the minute from the argument
[x] MONTH() Return the month from the date passed
[x] MONTHNAME() Return the name of the month
[x] NOW() Return the current date and time
[x] PERIOD_ADD() Add a period to a year-month
[x] PERIOD_DIFF() Return the number of months between periods
[x] QUARTER() Return the quarter from a date argument
[x] SEC_TO_TIME() Converts seconds to 'hh:mm:ss' format
[x] SECOND() Return the second (0-59)
[ ] STR_TO_DATE() Convert a string to a date
[x] SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
[ ] SUBTIME() Subtract times
[x] SYSDATE() Return the time at which the function executes
[x] TIME() Extract the time portion of the expression passed
[ ] TIME_FORMAT() Format as time
[x] TIME_TO_SEC() Return the argument converted to seconds
[ ] TIMEDIFF() Subtract time
[ ] TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
[x] TIMESTAMPADD() Add an interval to a datetime expression
[ ] TIMESTAMPDIFF() Subtract an interval from a datetime expression
[x] TO_DAYS() Return the date argument converted to days
[x] TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
[x] UNIX_TIMESTAMP() Return a Unix timestamp
[x] UTC_DATE() Return the current UTC date
[x] UTC_TIME() Return the current UTC time
[x] UTC_TIMESTAMP() Return the current UTC date and time
[x] WEEK() Return the week number
[x] WEEKDAY() Return the weekday index
[x] WEEKOFYEAR() Return the calendar week of the date (1-53)
[x] YEAR() Return the year
[x] YEARWEEK() Return the year and week

String Functions and Operators

[x] ASCII() Return numeric value of left-most character
[x] BIN() Return a string containing binary representation of a number
[x] BIT_LENGTH() Return length of argument in bits
[x] CHAR() Return the character for each integer passed
[x] CHAR_LENGTH() Return number of characters in argument
[x] CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
[x] CONCAT() Return concatenated string
[x] CONCAT_WS() Return concatenate with separator
[x] ELT() Return string at index number
[ ] EXPORT_SET() Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
[x] FIELD() Index (position) of first argument in subsequent arguments
[ ] FIND_IN_SET() Index (position) of first argument within second argument
[ ] FORMAT() Return a number formatted to specified number of decimal places
[x] FROM_BASE64() Decode base64 encoded string and return result
[x] HEX() Hexadecimal representation of decimal or string value
[x] INSERT() Insert substring at specified position up to specified number of characters
[x] INSTR() Return the index of the first occurrence of substring
[x] LCASE() Synonym for LOWER()
[x] LEFT() Return the leftmost number of characters as specified
[x] LENGTH() Return the length of a string in bytes
[x] LIKE Simple pattern matching
[ ] LOAD_FILE() Load the named file
[x] LOCATE() Return the position of the first occurrence of substring
[x] LOWER() Return the argument in lowercase
[x] LPAD() Return the string argument, left-padded with the specified string
[x] LTRIM() Remove leading spaces
[ ] MAKE_SET() Return a set of comma-separated strings that have the corresponding bit in bits set
[ ] MATCH() Perform full-text search
[x] MID() Return a substring starting from the specified position
[x] NOT LIKE Negation of simple pattern matching
[x] NOT REGEXP Negation of REGEXP
[x] OCT() Return a string containing octal representation of a number
[x] OCTET_LENGTH() Synonym for LENGTH()
[x] ORD() Return character code for leftmost character of the argument
[x] POSITION() Synonym for LOCATE()
[x] #13849
[x] REGEXP Whether string matches regular expression
[x] REGEXP_INSTR() Starting index of substring matching regular expression
[x] REGEXP_LIKE() Whether string matches regular expression
[x] REGEXP_REPLACE() Replace substrings matching regular expression
[x] REGEXP_SUBSTR() Return substring matching regular expression
[x] REPEAT() Repeat a string the specified number of times
[x] REPLACE() Replace occurrences of a specified string
[x] REVERSE() Reverse the characters in a string
[x] RIGHT() Return the specified rightmost number of characters
[x] RLIKE Whether string matches regular expression
[x] RPAD() Append string the specified number of times
[x] RTRIM() Remove trailing spaces
[ ] SOUNDEX() Return a soundex string
[ ] SOUNDS LIKE Compare sounds
[x] SPACE() Return a string of the specified number of spaces
[x] STRCMP() Compare two strings
[x] SUBSTR() Return the substring as specified
[x] SUBSTRING() Return the substring as specified
[ ] SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
[x] TO_BASE64() Return the argument converted to a base-64 string
[x] TRIM() Remove leading and trailing spaces
[x] UCASE() Synonym for UPPER()
[x] UNHEX() Return a string containing hex representation of a number
[x] UPPER() Convert to uppercase
[x] WEIGHT_STRING() Return the weight string for a string

Cast Functions

[x] CAST() Cast a value as a certain type
[x] CONVERT() Cast a value as a certain type

Bit Functions and Operators

[x] & Bitwise AND
[x] >> Right shift
[x] << Left shift
[x] ^ Bitwise XOR
[x] BIT_COUNT() Return the number of bits that are set
[x] | Bitwise OR
[x] ~ Bitwise inversion

JSON

[x] -> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
[x] ->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
[x] JSON_ARRAY() Create JSON array
[ ] JSON_ARRAY_APPEND() Append data to JSON document
[ ] JSON_ARRAY_INSERT() Insert into JSON array
[ ] JSON_CONTAINS() Whether JSON document contains specific object at path
[x] JSON_CONTAINS_PATH() Whether JSON document contains any data at path
[x] JSON_DEPTH() Maximum depth of JSON document
[x] JSON_EXTRACT() Return data from JSON document
[ ] JSON_INSERT() Insert data into JSON document
[x] JSON_KEYS() Array of keys from JSON document
[x] JSON_LENGTH() Number of elements in JSON document
[ ] JSON_MERGE() Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
[ ] JSON_MERGE_PATCH() Merge JSON documents, replacing values of duplicate keys
[ ] JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys
[x] JSON_OBJECT() Create JSON object
[ ] JSON_OVERLAPS() Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0)
[ ] JSON_PRETTY() Print a JSON document in human-readable format
[x] JSON_QUOTE() Quote JSON document
[ ] JSON_REMOVE() Remove data from JSON document
[ ] JSON_REPLACE() Replace values in JSON document
[ ] JSON_SCHEMA_VALID() Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not
[ ] JSON_SCHEMA_VALIDATION_REPORT() Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure 8.0.17
[ ] JSON_SEARCH() Path to value within JSON document
[ ] JSON_SET() Insert data into JSON document
[ ] JSON_STORAGE_FREE() Freed space within binary representation of JSON column value following partial update
[ ] JSON_STORAGE_SIZE() Space used for storage of binary representation of a JSON document
[ ] JSON_TABLE() Return data from a JSON expression as a relational table
[ ] JSON_TYPE() Type of JSON value
[x] JSON_UNQUOTE() Unquote JSON value
[ ] JSON_VALID() Whether JSON value is valid
[ ] JSON_VALUE() Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type
[ ] MEMBER OF() Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)

XML Functions

[ ] ExtractValue() Extract a value from an XML string using XPath notation
[ ] UpdateXML() Return replaced XML fragment

Encryption and Compression Functions

[ ] AES_DECRYPT() Decrypt using AES
[ ] AES_ENCRYPT() Encrypt using AES
[ ] COMPRESS() Return result as a binary string
[x] MD5() Calculate MD5 checksum
[x] RANDOM_BYTES() Return a random byte vector
[x] SHA1(), SHA() Calculate an SHA-1 160-bit checksum
[x] SHA2() Calculate an SHA-2 checksum
[ ] STATEMENT_DIGEST() Compute statement digest hash value
[ ] STATEMENT_DIGEST_TEXT() Compute normalized statement digest
[ ] UNCOMPRESS() Uncompress a string compressed
[ ] UNCOMPRESSED_LENGTH() Return the length of a string before compression
[ ] VALIDATE_PASSWORD_STRENGTH() Determine strength of password

Miscellaneous Functions

[ ] ANY_VALUE() Suppress ONLY_FULL_GROUP_BY value rejection
[x] BIN_TO_UUID() Convert binary UUID to string
[ ] DEFAULT() Return the default value for a table column
[ ] GROUPING() Distinguish super-aggregate ROLLUP rows from regular rows
[x] INET_ATON() Return the numeric value of an IP address
[x] INET_NTOA() Return the IP address from a numeric value
[x] INET6_ATON() Return the numeric value of an IPv6 address
[x] INET6_NTOA() Return the IPv6 address from a numeric value
[x] IS_IPV4() Whether argument is an IPv4 address
[x] IS_IPV4_COMPAT() Whether argument is an IPv4-compatible address
[x] IS_IPV4_MAPPED() Whether argument is an IPv4-mapped address
[x] IS_IPV6() Whether argument is an IPv6 address
[x] IS_UUID() Whether argument is a valid UUID
[ ] MASTER_POS_WAIT() Block until the replica has read and applied all updates up to the specified position
[ ] NAME_CONST() Cause the column to have the given name
[ ] SLEEP() Sleep for a number of seconds
[ ] SOURCE_POS_WAIT() Block until the replica has read and applied all updates up to the specified position
[x] UUID() Return a Universal Unique Identifier (UUID)
[ ] UUID_SHORT() Return an integer-valued universal identifier
[x] UUID_TO_BIN() Convert string UUID to binary
[ ] VALUES() Define the values to be used during an INSERT

kaveeshadinamidu commented 2 years ago

Hii @vmg, I would like to contribute to this project on GSoC 2022. Can you guide me on how should I start with this project? Thank You!

vmg commented 2 years ago

Hi @kaveeshadinamidu! There's not much you can do to get started right now since our GSoC application hasn't been approved yet. I would advise you to brush up on your Go skills meanwhile!

Jassi10000-zz commented 2 years ago

Hi @kaveeshadinamidu! There's not much you can do to get started right now since our GSoC application hasn't been approved yet. I would advise you to brush up on your Go skills meanwhile!

HI @vmg I am looking to contribute to this project in GSOC'22 , btw is the application approved ? Also please what all things I should be good with in GO, this would really help while contributing

vmg commented 2 years ago

Hi @Jassi10000! We won't know if the project has been accepted until March 7th, the official date when Google will announce the accepted organizations. As for important things to learn: I think it's important to have a solid grasp of SQL (and the particular SQL syntax that MySQL uses). For Go, just basic knowledge of the language will suffice.

Jassi10000-zz commented 2 years ago

Hi @Jassi10000! We won't know if the project has been accepted until March 7th, the official date when Google will announce the accepted organizations. As for important things to learn: I think it's important to have a solid grasp of SQL (and the particular SQL syntax that MySQL uses). For Go, just basic knowledge of the language will suffice.

I hope it gets accepted 😊, and thanks @vmg for telling about the level of knowledge I should have

vmg commented 2 years ago

Hey everyone! We've seen a lot of interest from potential students about this GSoC proposal. This includes many candidates opening PRs to implement some of the functionality of the project ahead of time. Because of this interest, I wanted to take a moment to write some contribution guidelines, so nobody wastes their time.

First and foremost, although we greatly appreciate all OSS contributions to the Vitess project, be advised that you don't need to contribute a PR to Vitess in order to be accepted to the GSoC. We will obviously take into account your contributions while evaluating your proposal, but if your goal is to give a good image of yourself, you must put effort into these contributions, otherwise they may count against you.

If you really think that opening an introductory PR will help you understand the project better, please keep the following in mind:

The best thing you can do before attempting to contribute to the Vitess project is reading the Contributor guidelines and making sure you're following them -- particularly the DCO rules about signing your commits.
The Vitess project is a distributed OSS project that operates asynchronously with developers from all over the world. Because of this, the number one thing we care about in your PRs, in your proposals and in your contributions to the project are your communication skills.
- Make sure you pick a descriptive name for your Pull Requests.
- Make sure you follow Vitess' PR template and fill it up properly. This includes a detailed description of what you're trying to accomplish, how you're testing the feature
- Make sure you write descriptive commit messages for all your changes. This includes details on the change itself for anything you haven't explained in your PR description.
- Ask questions. I'm here to guide you and answer your doubts. Rather than submitting something that you're unsure about for review, open a Draft PR and ask me about the issue. You can also reach out via our Developer Slack.
- Make sure you're using good, proper English, with proper grammar and without typos. I know that's a struggle for all of us non-native speakers, including myself, but it is of utmost importance that your communication can be understood properly by the other contributors to the project. Sometimes this means that it'll take more effort to write your PR descriptions and commit messages. This is an effort that we all make, so please keep that in mind if you want to spend the Summer working with us.
These evaluation engine reverse-engineering efforts are hard, which is one of the reasons why we don't require students to send a PR before they're accepted into the program. If you really want to send such PR please make sure that the PR actually works as to not waste the maintainers' time.
- The behavior of all these MySQL built-in functions is tricky and nuanced. It varies for different input types and contexts. You need to play with a live MySQL instance in depth to figure out how the function truly behaves -- simply reading the official documentation is not enough. If you just implement the bare minimum for the built-in function, that is not an useful contribution, and that is not the work you'll be performing during the summer if you're accepted into the program. Please make sure that you're submitting for review an implementation of a built-in function that matches MySQL's behavior to the best extent of your hability.
- In order to make sure that your proposal matches MySQL's behavior, it must include integration tests. We test the behavior of the evaluation engine extensively against live MySQL instances. In integration/comparison_test.go you can find many examples of how the built-ins are tested. You must include an integration test with your PR that verifies the behavior of your PR with as many inputs as you can think of. Please spend some time thinking through the possible corner cases and including them in the integration test. And, obviously, please make sure that the integration test is green before submitting your PR.

Let me wrap up by thanking you all, and any further prospective students, for the interest you've shown in our project, and emphasize that unless you have a very keen interest on implementing and submitting a built-in function before the project starts, your time will be better spent reviewing the project and preparing a good, comprehensive application with proper English, instead of opening incomplete PRs.

vmg commented 2 years ago

The Cloud Native Computing Foundation has been accepted for the Summer of Code, so this project can now start accepting applications. Good luck everyone!

https://summerofcode.withgoogle.com/programs/2022/organizations/cncf

csprinkle74 commented 2 years ago

`

Duplicate of #`

skant7 commented 2 years ago

Hi, @vmg I'm interested in working on this project, so the project's scope will be to implement all the leftover functions or just some part of it during the LFX mentorship time window ?

Weijun-H commented 2 years ago

Hi, @vmg I am trying to understand how evalengine works. And I find all Bit Functions and Operators almost done, does they still need to be refined? If so, could you give me some hints so that I cound work on them to understand the mechanism better.

vmg commented 2 years ago

Hi @skant7! As explained in the description, implementing all the missing functionality in the evalengine during your internship is unfeasible, so as part of your application you should come up with a subset of the functionality that you think would be useful to implement and that would fit in the duration of the program.

vmg commented 2 years ago

@Weijun-H: good catch! The bitwise functions were already implemented but we didn't update the issue. I've ticked their boxes -- I don't think any further work needs to be done on them (unless I missed some corner cases). A good place to look are a lot of the string helper functions, which are all still missing.

crsdvaibhav commented 1 year ago

@vmg Hey! I was looking to work on this issue during GSoC'23. Could you please give me a few pointers so I can better understand the codebase around the issue?

vmg commented 1 year ago

Hey @crsdvaibhav! This was a GSoC'22 project that has already finished. We still don't know whether Vitess will participate in GSoC'23, whether it'll get accepted, and whether this project will be resumed then! I'll update this issue once we figure things out. Stay tuned and thanks for your interest.

crsdvaibhav commented 1 year ago

Thanks for the status! In the meantime I will at the other issues.

On Fri, 9 Dec, 2022, 5:48 pm Vicent Martí, @.***> wrote:

Hey @crsdvaibhav https://github.com/crsdvaibhav! This was GSoC'22 project that has already finished. We still don't know whether Vitess will participate in GSoC'23, whether it'll get accepted, and whether this project will be resumed then! I'll update this issue once we figure things out. Stay tuned and thanks for your interest.

— Reply to this email directly, view it on GitHub https://github.com/vitessio/vitess/issues/9647#issuecomment-1344238209, or unsubscribe https://github.com/notifications/unsubscribe-auth/AW2EDG7AZJ24IWPBP6AZN6DWMMPQFANCNFSM5NYCLXFQ . You are receiving this because you were mentioned.Message ID: @.***>

Anu-Ra-g commented 1 year ago

So for all these new applicants, applying to these programs like GSOC, LFX, etc. on what basis are they selected, considering the fact that an applicant has no prior experience in Golang, necessary tools, and the development of the project? @vmg I'm planning to such open source programs.

vmg commented 1 year ago

Hey @Anu-Ra-g! I am going very soon on paternity leave (:baby: :tada:) so there isn't going to be any evalengine related projects for Vitess this year, as I won't be around to mentor.

For all the other Vitess-related projects that we're going to have this year in GSOC and LFX, we're looking for actual experience in Golang. As a rule of thumb, you're supposed to apply to internship projects for languages you have a beginner-intermediate expertise with, because the mentorship time must be spent actually onboarding you on the technical details of the project, not teaching you how to program in Go.

Cheers!

vitessio / vitess