Several performance optimizations

[paulrutter]

We have been using AlaSQL for a couple of years, and had some patches applied to it locally. These patches were created after Node.js CPU profiling AlaSQL queries. This PR contains these patches.

Performance improvements for AlaSQL

• Add AST cache
• Add regex pattern cache when using LIKE (building RegEx is expensive)
• Optimize when having lots of values in a (NOT) IN statement, using JS Set
• Remove RegEx checking for DATE function (what is it used for? It slows DATE functions down)
• When using NOW, just return the date (not sure why a date is translated to a string?)

Please let me know if these changes make sense and/or require changes. Especially the last two changes might be there for a reason we don't know about.

I also skipped some test cases around string dates and adjusted one for WHERE IN, using plain string literals. The PR is lacking new test cases, but if you're interested in these changes, we can work on these.

I couldn't get the formatter to work on Windows though, so that still has to be done.

Thank you for the time you are putting into AlaSQL!

[mathiasrw]

I love it!

Let me have a look at the code and merge as soon as possible

[paulrutter]

I was under the impression that the failing unit tests where due to running on Windows (newlines), but apparantly these also fail in CI. Are these new failures? Then we need to look into these.

[paulrutter]

@mathiasrw I fixed the unit tests for the LIKE, all unit tests succeed now locally:

  ............................................................................................................................................................
  ....,,,......................................,,,................,...........................................................................................
  .............................................................................................................................,,..,................,.........
  ...............................,.........................................................................................................................,,,
  ,,,,............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,..........................................,,,...................,,,,......,,,,........
  ..,,,,,,,,,,,,,,,...,,,...,,,,,,,,,,,,,,,,,,.........,,,,,,,,..................................,,,,,,,,,,,,........,,,..............................,,,,,,,,
  ,,.................................,,,,....,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,....,,,,,,................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  ,,,,,,,,,,,,,,,,,,,,,,,,,,,....,,,,,,,,.......,,,,,,..........................................,,,,,,,,,,,,,,,,,,,.........,,,,.,............................
  ...........................................................,........,,.,.,..,,,,,,,,,,......................................................................
  .......,.................................,,,,,,,,,,,,,,,,,,,,,,,,,..........................................................................................
  .....................................................,........................................................,.......,,..,,..,,..,,.........,..............
  ..,.......,..,......,,...................,..,........,,,.....,,,,,,,,,,,,,,,,...............,.,.............................................................
  ...................................................,.................

  1561 passing (4s)
  380 pending

[paulrutter]

@mathiasrw I adjusted the skipped tests and re-enabled them again, as i made it configurable as option instead. I extended the relevant unit tests.

Can you run the workflow again?

[mathiasrw]

I sure can. Sorry for slow response. Lots of things going on at the moment.

I love the performance optimizations, but we cant "just" redo tests that beforehand was working. If we will be doing changes that by some can be considered breaking (= tests are not verifying the same thing as before) than I want to pool those together and add them at the next major version bump (we got a few other tings that is also in the queue for that)

Are you OK to split this PR into so we seperate what demands a change of an existing test?

[paulrutter]

@mathiasrw I fixed the implementation so it supports both string literals and new String. Added new unit tests for using string literals in IN, and also added tests using NOT IN.

Can you check and run the workflow again? This way, we don't need to split of the PR.

[paulrutter]

Thanks for merging the PR, will this be part of next release?