09 Apr 16:31

zachmu

f8b2e9e

Q2 2024 Release Patch Latest

Latest

This is a patch release on the Q2 release to remove the NewDefaultServer method, which was was panicking at query time when used.

go-mysql-server's sql interfaces are not guaranteed to settle until 1.0.

Merged PRs

go-mysql-server

2437: lowercase when looking up self referential foreign key columns
fixes: dolthub/dolt#7700
2436: Making @@server_id default value match MySQL
2434: Fixing the default value for the binlog_checksum system variable
Small change to make the default value of the global binlog_checksum system variable match MySQL's default value (i.e. "CRC32").
2433: NULL to nil
The SHOW FIELDS/COLUMNS FROM <table> query would return the string "NULL" for Default column rather than nil.
This mattered for Knex, which relied on it being NULL and not "NULL".
fixes: dolthub/dolt#7692
2432: support Threads_connected and Threads_running status variables
This PR adds support for Threads_connected and Threads_running status variables.
Additionally, the local enginetest are flaking consistently in dolt ci, so those have been removed;
we have handler tests for com_delete, com_insert, and com_update anyway.
Related: dolthub/dolt#7646
2431: Setting Innodb_buffer_pool_pages_total to 1, to avoid an issue with Datadog's collector
Datadog's metric collector errors out with a divide by zero error if the Innodb_buffer_pool_pages_total status variable is 0; changing it to 1 avoids this and allows the agent to collect metrics from Dolt.
2430: have status variables use go routines
This PR changes Status Variables to update through go routines, to avoid slowing down query execution due to the mutexes present.
2429: server trace time includes parsing
2427: support Com_delete, Com_insert, Com_update status variables
related: dolthub/dolt#7646
2426: use @@session.collation_server during create database ...
This PR makes it so create database ... actually reads the @@session.collation_server variable.
Additionally, this ensures that settings @@character_set_server sets @@collation_server and vice versa.
Interestingly, it seems like MySQL actually ignores the global scope of these system variables, and reads the session scope instead.
fixes dolthub/dolt#7651
2423: Adding test for preparing time.Time types
This PR adds tests for using time.Time, some tests have to be skipped because we don't support Timespan correctly.
companion pr: ~~dolthub/vitess#327~~ dolthub/vitess#328
test for dolthub/dolt#7665
2422: Support Questions status variable
This PR adds logic to update status variable Questions.
This only works in the server context, probably doesn't through dolt sql cli.
dolthub/dolt#7646
2421: [stats] costed index scan perf
Histogram copying is expensive. Instead pass and mutate references. We have to use a different struct type to load stats from JSON in order to support histogram interface generalization.
related Dolt-side: dolthub/dolt#7666
2420: support case-insensitive LIKE for show status/variables
MySQL stores session and global variables in a performance_schema database, and these tables have a case-insensitive collation on the variable names.
This PR emulates that behavior by hard coding the collation the schemas for ShowStatus and ShowVariables nodes.
2419: Bug fix: Allow JSON scalar comparison between int64 and float64
When comparing JSON values, numbers may be represented internally as an int64 or float64, but our comparison code wasn't casting an int64 to a float64 in order to compare it with a float64 value.
Fixes dolthub/dolt#7656
2418: fix show create database to actually show charset/collation
This PR fixes the SHOW CREATE DATABASE ... statement to actually show the charset/collation that the db is under instead of always default.
Additionally, this PR parses the charset database option, instead of ignoring it like before.
partially fixes: dolthub/dolt#7651
2416: /{.github,go.mod,go.sum}: bump go version
2414: stubbing out status variables
This PR adds the initial implementation of Status Variables.
There are 682 status variables, and are very similar to System Variables.
Every variable is read-only (and can only be updated by the server itself), and there are session-specific variables.
MySQL Docs: https://dev.mysql.com/doc/refman/8.0/en/server-status-variable-reference.html
Related: dolthub/dolt#7646
2412: New interface for binlog primary callbacks
First pass on connecting the GMS layer with the Dolt layer for handling callbacks when the SQL server is acting in binlog primary mode, through the new BinlogPrimaryController interface. This new interface pretty closely mirrors the existing callback interface for replica callbacks, the BinlogReplicaController interface.
Related to dolthub/dolt#7512
2411: implement json_search()
MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/json-search-functions.html#function_json-search
2410: Adding system variable innodb_autoinc_lock_mode
We currently only support innodb_autoinc_lock_mode = 2, not 0 or 1.
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
related: dolthub/dolt#7634
2404: Improve handling of unsigned and decimal types in JSON
Fixes #2391
MySQL's JSON type differs from standard JSON in some important ways. It supports types not supported in standard JSON, such as separate types for integers and floats, an unsigned int type, and a decimal type.
Prior to this PR, we would convert values to JSON by using the encodings/json package to marshall the value to a JSON string and then unmarshall it to a go map. This is not only slow, but it's incorrect for these additional types.
The main purpose of this PR is to add special handling for these types that allow them to be stored in JSON documents. We also avoid generating and parsing JSON in places where it's not actually necessary, and fix bugs where decimals get incorrectly converted into strings, or unsigned ints get converted into signed ints.
Finally, this fixes an issue where we send incorrect bytes for JSON-wrapped decimal values along the wire.
2403: fix dbName not being used in the example
This PR makes sure that dbName in the example is actually being used, instead of having a hardcoded "mydb" in createTestDatabase.
fixes #2402
2401: refactor and parse table options, support auto_increment table option
Table Options are now parsed as structs, so we can read/use some of the variables.
Character Sets, Collations, Create Table, TableSpec, etc. have been refactored.
Additionally, this PR adds support to parse and use the auto_increment table option.
TODO:
- CREATE TABLE ... LIKE ... needs to preserve table opts, like comments
- ~~alter table add column ... auto_increment does not work when there are already rows~~
  Companion PR: dolthub/vitess#322
2399: fix custom insert ordering for pk
fixes #2397
2398: fix in-memory implementation of RenameTable to read from session
The in-memory implementation of RenameTable uses data from the BaseDatabase, instead of reading it from the session.
This is problematic when there are multiple alter statements.
Additonally, includes some small refactor so all functions are pointer receiver instead of a mix.
fixes #2396
2394: Bug fix: Set non-boolean system variable enum values to 'ON' or 'OFF'
We were automatically converting ON and OFF values to to true and false when setting a system variable, which made it impossible to set system variables to those enum values. For example:
```
SET @@GLOBAL.gtid_mode='ON';
Variable 'gtid_mode' can't be set to the value of 'true'
```
[2393](https://github.com/dolthub...

Assets 2

28 Feb 22:44

zachmu

v0.18.0

8122f68

Q1 2024 Release

This is our quarterly roll-up release that contains many bug fixes, features, and performance improvements. Reminder that interfaces are not guaranteed to settle until 1.0.

This release contains a breaking change for setting up an in-memory database server, e.g. in testing. The in-memory database now requires its own session implementation, and the server must be supplied with a compatible session builder to prevent panics. See the _examples directory for an example.

Merged PRs

go-mysql-server

2350: [planbuilder] Limit allows unsigned ints
Parameterizing limits in stored procedures exposed a bug where we weren't accepting unsigned ints in LIMIT/OFFSET. This is only possible in prepared or stored procedure contexts afaik.
2348: Optional integrator schema validation
Move table schema validation so that we validate the accumulated result rather than sequential alters.
2347: implement json_depth()
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-depth
2346: fix null and empty paths for json functions
2345: Return correct MySQL error code when inserting into nonexistent columns.
Fixes #2344
This PR also adds a test framework for testing MySQL error codes, since this doesn't appear to currently be tested. This should make adding tests for other error codes easy.
Some of the MySQL error codes that I expected us to use (such as 1050: ERTableExists) have zero usages in GMS or Dolt. We're probably returning 1105: ERUnknownError for these.
2343: fix json bool comparison
JSON Boolean values are special; they do not behave like normal when converted/compared against other types.
fixes dolthub/dolt#7528
2342: Add row length guards
Tests and basic engine-side guards similar to mysql's behavior.
re: dolthub/dolt#7524
2339: only use crosshashjoin on subqueryaliases
This fixes 2 sqllogictests.
2336: fix rangetree removal of largest node without left child
When removing a right child, a missing nil guard was causing us to improperly propagate the new max range.
This resulted in overlapping ranges.
This fixes 1 sqllogictest.
2335: Schema pruning considers database name
Fix some inconsistencies with project and join hints. New test added for new project pruning changes.
2334: fix char to bool conversion
This PR adds missing nil guards in a few conversion methods.
fixes dolthub/dolt#7515
2333: [planbuilder] Skip post-aggregate projection when there are no aliases in target scope
re: dolthub/dolt#6982
This takes the last big chunk of CPU pressure off of TPC-C execution, which is mostly disk limited now:
2332: [time] shortcut for no timezone conversion
This function reads files from disk to try to convert a timezone to itself. There are other optimizations we could do to maybe try checking for the integer offset before doing syscalls.
re: dolthub/dolt#6982, ~10% TPC-C boost
2331: fix NOT expression in conjunction with WHERE EXISTS(<subquery>)
The analyzer rule unnest_exists_subqueries was accidentally dropping NOT expressions when hoisting subqueries from WHERE EXISTS... clauses.
This should fix 8 sqllogictests.
Correctness: dolthub/dolt#7510
2330: Fix DISTINCT over DECIMALS
There was another place where we were using hashstructure package, which does not hash decimal.Decimal types correctly.
Switched to xxhash package, which is what we use everywhere else.
Reusing solution from: #2279
This will fix 1 sqllogictest.
2329: expression.Div Micro Benchmarks
There are going to be changes to our division behavior that impact both its accuracy and speed.
This PR adds benchmarks to track the runtime improvements/degradations
2327: memo/planbuilder: Off by one error, and issue importing statistics for testing
The original bug was a one-off error where conjCollector was checking for the wrong equivalence column. That spiraled into:
- statistics were not properly being used for some tests because PRIMARY case sensitivity in ANALYZE ... UPDATE HISTOGRAM statements
- one integration test had a weird edge case where OSX and linux compute slightly differently float values, leading to different plans locally vs CI. One option was to truncate the specificity of all cost values with a multiply/round to avoid numerical weirdness...picking the first lowest plan also seems to work.
- variety of other small bugs in costing
- update join bug where the projections after a rearrangement lost expression id/nullability info. This was exposed by the changes above
2326: Various Decimal Type Fixes
This PR has a variety of fixes to have arithmetic operations (especially those involving decimals) behave more like MySQL.
The logic for the Type() method for Arthmetic and Div is simpler, and better tested.
When comparing Decimal results from division operations, MySQL has an internal Scale that is different than the Scale used when returning Decimal results for display.
Here's a matrix displaying the resulting scale:
(Ex: 1 / 3 = 0.333333333; scale 0 div scale 0 should return scale 9)

Additionally, this PR adds test for arithmetic operations over Datetime and Year types. There are still a some problems dealing with precision and parsing there...
Note: I believe the division optimization where float division is preferred over decimal division for internal calculations may be causing problems. More testing is needed to see if it's possible to enable this without causing inaccuracies/precision loss.
There are microbenchmarks measuring the performance of div expression, and it turns out these changes actually greatly improve the runtime.
Correctness: dolthub/dolt#7484
Fixes
- dolthub/dolt#7207
2323: SQLEXCEPTION proc handler
We originally supported one type of procedure handler, NOT FOUND, which explicitly checked for an error when fetching from a cursor io.EOFs. The implementation for that handler would walk the entire BEGIN/END scope stack inside the Fetch call looking for a handler, execute the handler body, and then embed the scope height into a special return error. The error walked back up the callstack looking for the BEGIN/END block embedded in the error message.
2319: Add net.Listener as server config option
Presently go-mysql-server is served by a net.Listener backed by a real port or socket. In some environments (like testing) we want to avoid the host's networking stack entirely. This changeset adds support for the caller to provide the net.Listener, which gives them full control over where the sever serves.
This opens the door for some cool workflows. For example, you can run the server using a buffer-based net.Listener implementation (which is what happens in the test that I added).
I landed on this solution while thinking through #2314
2317: support SELECT ... INTO OUTFILE/DUMPFILE ...
This adds support for MySQL's SELECT ... INTO OUTFILE/DUMPFILE ... feature.
It is the complement to LOAD DATA. There is no LOCAL option, so files created using this feature are on the server.
This PR adds a custom TestSuite for testing these files, as it needs to write, read, and delete files.
syntax: dolthub/vitess#311
fixes dolthub/dolt#7453
2316: Allow primary keys with auto_increment columns to be dropped when an appropriate index exists
Related to dolthub/dolt#7456
2315: Stored procedures can use params as LIMIT,OFFSET
fixes: dolthub/dolt#7458
Fixes procedure param types in the process.
Testing against mysql it doesn't seem like subqueries or regular column types are valid as LIMIT values in any case other than procedure parameters. I still need to test whether trigger columns can be used in LIMIT,OFFSET, but that seems like a separate problem.
2312: Added the ExtendedType interface
This ...

Assets 2

23 Aug 21:14

zachmu

v0.17.0

1595e17

Aug 2023 Release

This is our ~quarterly rollup release, containing many new features, bug fixes, and performance improvements. See the merged PRs for details. Interfaces are not guaranteed stable before 1.0.0.

This release includes dramatic changes to parsing and the query planner. Users running a test server are expected to be unaffected, but integrators building their own backend may need to make changes.

Merged PRs

go-mysql-server

1965: Add error test for ambiguous column name query
close dolthub/dolt#6395
1963: Fixed type hashing for Full-Text
Fixes the reopened issue: dolthub/dolt#6543
Not all types were covered by the default case.
1961: engine: Make ReadOnly a toggleable atomic.Bool.
1960: Fixed Full-Text NULL handling and ALTER TABLE case
Fixes dolthub/dolt#6540 and dolthub/dolt#6541
1959: Bug fix: Prevent panic when reading non-existent user and system vars
Fixes dolthub/dolt#6546
We have enginetests that cover both of these queries, but because we don't run them with the full wire request/response processing code, we didn't catch these bugs during testing. Happy to add more tests here if there are suggestions, but I think the right way to test both of these is to get our existing test suite running over a SQL server connection, instead of just using the internal interfaces to the engine (i.e. dolthub/dolt#3646), which Zach started on last week.
1958: Don't parse queries twice
Local profile for oltp_point_select (query with smallest time spent in execution) is 5-15% speedup. Impact on queries with longer-runtime will be smaller, proportional to the fraction of time spent in analysis vs execution.
results here: dolthub/dolt#6547 (comment)
1957: Fixed relevancy ordering for Full-Text
Fixes dolthub/dolt#6530
1956: Removed unused AutoIncrementGetter interface
Related to dolthub/dolt#6543.
The AutoIncrementGetter was moved from the editor to the table years ago, however the interface remained. I debated deleting this during my Full-Text implementation, however decided to leave it. Now, we've encountered an error with an integrator making use of the interface, so it has been removed here and the interface was moved into the integrator since it's an internal detail now.
1955: adding catalog table function interface
1954: Lateral join uses prepend row on RHS
1952: GMS tests have to resolve defaults
Force GMS to resolve column defaults, fix bugs exposed by additional testing. Dolt enginetests pass locally.
1951: Fixed collation display in SHOW CREATE TABLE
Originally, we didn't display the collation in SHOW CREATE TABLE when the collation was the default collation. Now, it doesn't display it if it's the same as the table collation, which mimics MySQL's behavior.
1950: Honor precision for datetime and timestamp, default to 0 (no fractional seconds)
1949: Revert "Merge pull request #1944 from dolthub/zachmu/timestamp"
This reverts commit ca69015, reversing changes made to c11b504.
1948: Name resolution refactor
Accumulation of:
- #1927
- #1932
- #1936
- #1939
- #1943
  Name resolution is interleaved with converting AST->plan nodes. Prepared statements use ParsedQuery and AST round tripping to analyze fresh query strings for every invoke. Various bug fixes.
1944: Changed datetime and timestamp types to honor precision and default to 0 digit precision
This matches the MySQL behavior.
Partial fix for dolthub/dolt#6503
1942: Full-Text Fixes Pt. 3
1941: Bug fix for JSON_ARRAY function with binary arguments
Binary args are now treated as character strings
Also added a testing path to ScriptTests that let you inject Vitess bindvars to exercise more server logic
Fixes #1855
1940: Add SECURITY.md.
1938: Create interface for indexible tables in IndexedTableAccess
Currently, only ResolvedTables are allowed to have indexes. There exists an interface, sql.IndexAddressable, which any node or table can implement in order to be a candidate for index-based optimization. But in practice, implementing that interface won't actually do anything because the IndexedTableAccess struct explicitly requires a ResolvedTable.
This PR replaces the ResolvedTable field in IndexedTableAccess with a new interface tentatively called TableNode, although a more specific name would probably be better.
In order for a node to be used for index-based optimization, it must implement this interface, and the table returned by the UnderlyingTable method must implement sql.IndexAddressable
1937: Remove do-nothing logic from pushdown.go
This code is for an optimization that "pushes" filters deeper into the tree so that they're adjacent to the tables they modify.
I've simplified the logic in two ways:
- Removed handling of IndexedTableAccess nodes. This analysis pass used to run after these nodes were generated, but now runs before, so this code path will never be hit. Removing this logic makes it easier to make future changes to IndexedTableAccess
- Removed the withTable subfunction, which never actually does anything, because the only table it will attempt to assign to a ResolvedTable is the table already on the node. This was likely leftover from a previous cleanup.
1935: Allow timestamps when encoding json
As reported on discord, Nautobot, through DJango, puts time stamp data into a json object. This fails because:
```
db> select JSON_OBJECT("a", Now());
unsupported type: time.Time
```
This change enables the encoding of a time stamp into a string.
1933: Fixed case sensitivity bugs in various statements
1930: leave aliases in projection
We convert expression.Alias into expression.GetField with the name replaced in the top-level projection.
When aliasing two different columns with the same name, we fail to properly distinguish them and end up rewriting the GetField indexes for both columns to be the same; this leads to incorrect results.
A simple fix appears to be simply allowing the top-level projection to remain as an expression.Alias.
This fix does not work for prepared statements in the old name resolution path.
fix for: dolthub/dolt#6455
1929: Add support for json_contains_path()
1928: Generated stored columns prototype
Looking for feedback. The approach is a little bit wonky: it uses the same code paths as column defaults since they behave so similarly, but this has some weird consequences:
- For generated columns, we fill in the column.DefaultValue field with the Generated expression
- In various places, we now have to consider whether to use either column.DefaultValue, or column.Generated
  Overall I think I'm favorable on this approach, but keeping the two values more separate might be better for maintainability, not sure. Thoughts?
1926: Use EvaluateCondition for conditions in join statements
Fixes dolthub/dolt#6412
Use the given EvaluateCondition util function to determine whether a join condition is satisfied, which accounts for truthy integer values as mentioned in the linked issue.
1925: Fix load data check constraint indexing bug
fix show tests
1924: Makes several new kinds of alter table statements involving auto_increment columns work correctly
Also: makes several kinds of ALTER TABLE statements with multiple clauses more lenient in their error checking than MySQL. These statements will now succeed instead of being rejected.
Fixes dolthub/dolt#6218
1922: Render enum/set variables as strings after they are set
Fixes https:...

Contributors

reltuk and max-hoffman

Assets 2

05 Jul 20:29

zachmu

v0.16.0

812b68d

Q3 2023 Release

This is the quarterly roll-up release, containing many new features and bug fixes.

Interfaces will not be stable until 1.0.

Merged PRs

go-mysql-server

1861: chore: remove refs to deprecated io/ioutil
1860: chore: unnecessary use of fmt.Sprintf
1859: chore: use copy(to, from) instead of a loop
1856: Support IPV6 loopback address for looking up user credentials
Map "::1" and "127.0.0.1" to localhost when looking up users.
There don't appear to be tests for this code path. TBD if I'll add some.
Related to: dolthub/dolt#6239
1854: Prevent loops in stored procedures from returning multiple result sets
The query in dolthub/dolt#6230 was causing rows from many result sets to be returned from a stored procedure. We already have code that limits BEGIN/END blocks to return the last SELECTed result set; this PR extends that logic to loop constructs as well.
Fixes: dolthub/dolt#6230
Dolt CI Checks: dolthub/dolt#6245
1853: chore: slice replace loop
1852: Alter stored procedure execution to deal with statements that commit transactions
This change adds checks to begin a new transaction whenever there isn't one during stored procedure execution. This lets things like dolt_commit() execute correctly in stored procedures.

1851: memo.Literal has different type than lookup
This panics on dolt:

CREATE TABLE tab2(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);
CREATE UNIQUE INDEX idx_tab2_0 ON tab2 (col1 DESC,col4 DESC);
CREATE INDEX idx_tab2_1 ON tab2 (col1,col0);
CREATE INDEX idx_tab2_2 ON tab2 (col4,col0);
CREATE INDEX idx_tab2_3 ON tab2 (col3 DESC);
INSERT INTO tab2 VALUES(0,344,171.98,'nwowg',833,149.54,'wjiif');
INSERT INTO tab2 VALUES(1,353,589.18,'femmh',44,621.85,'qedct');
SELECT pk FROM tab2 WHERE ((((((col0 IN (SELECT col3 FROM tab2 WHERE ((col1 = 672.71)) AND col4 IN (SELECT col1 FROM tab2 WHERE ((col4 > 169.88 OR col0 > 939 AND ((col3 > 578))))) AND col0 >= 377) AND col4 >= 817.87 AND (col4 > 597.59)) OR col4 >= 434.59 AND ((col4 < 158.43)))))) AND col0 < 303) OR ((col0 > 549)) AND (col4 BETWEEN 816.92 AND 983.96) OR (col3 BETWEEN 421 AND 96);

The PutField function expects the value to match the tuple descriptor exactly, and will panic if it does not.
The section of code in memo that creates a new range uses the type from the expression, but in other places it uses the index column expression types.
An alternative solution would be to have some logic in dolt to convert to the corresponding sql.Type based off the val.Enc

1848: IntDiv.Type() should always return either uint64 or int64
Previously, our IntDiv.convertLeftRight() used IntDiv.Type() to determine the larger type between IntDiv.Left.Type() and IntDiv.Right.Type() to avoid precision loss when doing internal calculations. Now, that logic is moved from IntDiv.Type() to IntDiv.convertLeftRight(), and IntDiv.Type() can only return uint64 or int64.
This should fix the sql correctness regression from #1834
1847: Fix TargetSchema.Resolved() to check targetSchema column default expressions
A couple SchemaTarget implementations weren't checking if the targetSchema was resolved as part of the Resolved() method. Added tests, audited the other implementations, and simplified the logic to use a new method on Schema to check that column default expressions are resolved.
Fixes: dolthub/dolt#6206
Dolt CI Run: dolthub/dolt#6213
1846: update information_schema.processlist to correctly display status of processes and databases
We used to hardcode "Query", now we reference process.Command
Additionally, we now get the database from the current session and use that variable.
fix for: dolthub/dolt#6023
1844: fix panic for group by binary type
We made a bad type assertion for sql.StringType.
Additionally, this fixes a issue where UnaryExpressions with GetFields would incorrectly throw a functional dependency error with ONLY_FULL_GROUP_BY enabled.
Fix for second part of: dolthub/dolt#6179
1843: Improvements to CAST and CONVERT functions
This PR adds support for casting/converting to FLOAT and DOUBLE types with the CAST and CONVERT functions. It also adds support for length (aka precision) and scale type constraints (e.g. CAST(1.2345 AS DECIMAL(3,2))).
Parser support for DOUBLE and FLOAT with CAST and CONVERT: dolthub/vitess#249
Fixes: dolthub/dolt#5835
1841: adding version and version_comment values
@@version now returns 8.0.11
@@version_comment now returns "Dolt"; in mysql, this appears to be dependent on OS / method of install
- Some people get MySQL Community Server - GPL
- Others get Homebrew
  Fix for first part of: dolthub/dolt#6179
1840: deduplicate (hash) intuple for and queries
This PR was originally supposed to fix it: original fix: #1677, but AND statements weren't covered.
fix for: dolthub/dolt#6189
1839: Slow degenerate semi join, hoist select opt
This enables recursive subquery decorrelations, and adds a hash join execution option for semi joins that is equivalent to cached subquery existence checks.
1838: resolve aliases in subqueries in function arguments
The rule reorderProjection also replaces subqueries with getfields in projections when they are used by subqueries, but it did not check for function expressions.
This meant that aliases in subqueries as arguments to functions threw a "x" could not be found error.
This PR just has the section of reorderProjection that is supposed to find deferredColumns also look at the arguments of functions recursively (because we can nest functions).
Additionally, there was another schema type bug:
```
tmp> select 0 as foo, if((select foo), 123, 456);
+-----+----------------------------+
| foo | if((select foo), 123, 456) |
+-----+----------------------------+
| 0   | 127                        |
+-----+----------------------------+
1 row in set (0.00 sec)
```
MySQL returns an Integer type for if statement, and if either argument is a String, it always returns a String.
fix for: dolthub/dolt#6174
1836: update cached table count in prepared statements
Prepared statements were caching table counts. We need to update the table count when finalizing prepared statements to bring table count up to date with any intermediate edits.
1834: fix expected schema for sum(literal)
The code path we take when print rows to shell is different than spooling from server.
In the sql case, we ignore the schema we get from analysis.
In the server case, we actually read the schema, and ensure that the rows are of that type.
When doing sum(literal), we use the type of the literal. In this issue, the literal was 1, so an INT8, which caps out at 127.
sum() is always supposed to return a float64, so I made a change to do that.
I checked by starting mysql with --column-type-info option, and it does appear that any columns coming from sum() has a DECIMAL type.
Fix for: dolthub/dolt#6120
1830: Use SO_REUSEADDR and SO_REUSEPORT options when creating the sql server on Unix
This prevents a transient error we've been seeing where the server sometimes fails to start, and the OS claims port already in use, even though we've already confirmed that the port is not in use prior to running dolt sql-server.
1829: plan.TableCountLookup short circuits count()
In many cases it is unnecessary to read an entire table to report count(*). We can use the RowCount() interface to jump to the answer.

1828: Consolidated collation maps
Main file to check is the generate/main.go file. After running the updated generation program, these are the consolidated files:

common_utf8mb4_es_0900_ai_ci_Weights: [utf8mb4_es_0900_ai_ci_Weights, utf8mb4_es_trad_0900_ai_ci_Weights]
common_utf8mb4_es_0900_as_cs_Weights: [utf8mb4_es_0900_as_cs_Weights, utf8mb4_es_trad_0900_as_cs_Weights]
common_utf_croatian_ci_Weights: [utf16_croatian_ci_Weights, utf32_croatian_ci_Weights, utf8mb3_croatian_ci_Weights, utf8mb4_croatian_ci_Weights]
common_utf_czech_ci_Weights: [u...

Assets 2

18 Apr 19:36

zachmu

v0.15.0

38acd8c

Q2 2023 Release

This is a periodic rollup release of ongoing development work. Reminder that core interfaces are not guaranteed to be stable until 1.0.

Merged PRs

go-mysql-server

1719: Added serving tray and bowtie
1717: Added a mascot image
1716: Revert "allow renaming views with RENAME TABLE statement (#1712)"
This reverts commit dac7262 from PR #1712
The PR above added a ViewDatabase implementation for PrivilegedDatabase, which is causing some Dolt cluster integration tests to fail. Temporarily pulling this commit out so we can get other GMS changes through and can debug the Dolt test failures separately.
1715: implement find_in_set
MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set
Note: Collations priority isn't exactly the same as MySQL
fix for: dolthub/dolt#5659
1713: Changing MaxTextResponseByteLength() to respect character_set_results
The Type.MaxTextResponseByteLength() function was not honoring the character_set_results system variable, which resulted in Dolt sending different response length metadata from MySQL when the value of character_set_results is not the same as the type's charset.
This change adds a *sql.Context parameter to the MaxTextResponseByteLength(ctx) function, so that the correct value can be pulled out of the session when the response metadata is sent through the handler.
Fixes: dolthub/dolt#5735
Related Dolt change: dolthub/dolt#5752
1712: allow renaming views with RENAME TABLE statement
- Added renaming of views with RENAME TABLE ... TO ... statement
- Added ViewDatabase implementation for PrivilegedDatabase
  TODO: ALTER TABLE ... RENAME ... should fail for renaming of views. Currently, vitess parses both the statements into the same node, which makes GMS parser not be able to detect the difference.
  Should return error: ERROR 1347 (HY000): 'mydb.myview' is not BASE TABLE
1711: update stored procedure timestamps to be accurate
Currently when we load non-built-in stored procedures, we re-analyze the sql string which also re-creates all the metadata. This means that the Created At and Modified At fields can re-set every time the procedure is loaded in, instead of showing the correct timestamp. This change updates the loaded in procedure with the correct timestamps.
fixes: dolthub/dolt#3081
1710: Detect invalid uses of * and window functions in queries.
This PR is the GMS side of the fix for dolthub/dolt#5656.
Preventing panics from invalid window functions is easy: replace the panic with returning a new kind of error.
The invalid *s were trickier. I added an additional analysis rule that runs immediately after resolving function names. It checks for any uses of "*" in a subexpression (so, not just "SELECT *") that aren't used inside a COUNT, COUNTDISTINCT, or JSONARRAY function.
It's possible that there's other places where *s are allowed that we need to account for. It's also possible that there may be some other disallowed uses of * that will pass this and still cause a panic.
1708: Allow uppercase collations
Fixes dolthub/dolt#5699
We were comparing the raw strings, which failed when they were anything but lowercase. Now we just force all strings to lowercase.
1707: fix JSON map key ordering
fix for: dolthub/dolt#5711
1704: fix group by over empty result sets
fix for: dolthub/dolt#5683
1702: sql/plan: track column renames on alter/modify column
fixes dolthub/dolt#5689
1701: support EVENT storage handling
This PR implements:
- CREATE EVENT statement
- DROP EVENT statement
- SHOW EVENTS statement
- SHOW CREATE EVENT statement.
  This PR handles events storage only. The event execution logic is not implemented yet. No events will be run and/or dropped if the execution time is passed or dropped if 'ends' time is passed and 'not preserve' is defined. Created events will be only stored in the database and can be queried to see details using SHOW statements.

1700: OrderedDistinct passes join tree row during exec
Re: dolthub/dolt#5700
OrderedDistinct dropped a parent row while executing a join tree, which caused a field index error in the child.
The query below has a slightly different plan, but the OrderedDistinct in the right half of a join tree, with a join SEMI_JOIN(scalarSubq0, scalarSubq1) as a child is the issue.

Project
├─ columns: [SUM(xy.x):0!null as SUM(x)]
└─ GroupBy
├─ select: SUM(xy.x:0!null)
├─ group:
└─ Project
├─ columns: [xy.x:0!null, xy.y:1]
└─ HashJoin
├─ Eq
│   ├─ xy.x:0!null
│   └─ scalarSubq0.u:2!null
├─ Filter
│   ├─ Eq
│   │   ├─ xy.x:0!null
│   │   └─ 2 (tinyint)
│   └─ Table
│       ├─ name: xy
│       └─ columns: [x y]
└─ HashLookup
├─ source: TUPLE(xy.x:0!null)
├─ target: TUPLE(scalarSubq0.u:0!null)
└─ CachedResults
└─ OrderedDistinct
└─ Project
├─ columns: [scalarSubq0.u:0!null]
└─ SemiJoin
├─ Eq
│   ├─ scalarSubq0.u:2!null
│   └─ scalarSubq1.a:4!null
├─ TableAlias(scalarSubq0)
│   └─ Table
│       ├─ name: uv
│       └─ columns: [u v]
└─ Filter
├─ Eq
│   ├─ scalarSubq1.a:0!null
│   └─ 2 (tinyint)
└─ TableAlias(scalarSubq1)
└─ Table
├─ name: ab
└─ columns: [a]

1696: resolve recursive CTE in scalar subquery
Re: dolthub/dolt#5631
1695: Fix value out of range bug
re: dolthub/dolt#5642
1693: update separator parsing in group_concat
Updates separator parsing for group_concat to use new Separator struct. This change is needed to allow '' as a separator.
fixes: dolthub/dolt#5570
related: dolthub/vitess#230
1692: don't push Filter below Limit
When there's a query with a filter over a subquery with a limit, we incorrectly push filters down to the subquery.
Example:
This
```
select * from (select * from t limit 1) t where i > 1;
```
is not equivalent to
```
select * from (select * from t where i > 1) t limit 1;
```
Fix for: dolthub/dolt#5568

1690: Use ordinals to force stable TopN heap sort
Re: dolthub/dolt#5640
... ORDER BY <expr> LIMIT 1 and ... ORDER BY <expr> LIMIT 1 OFFSET 1 both use TopN in a way that surprises users expecting stable output orders.
For example, the second query returning the first row is within the SQL spec but not user friendly:

-- setup
create table xy (x int primary key, y int);
insert into xy values (1,0),(2,0),(3,0),(4,0);
-- expect first row
select * from xy order by y asc limit 1;
+---+------+
| x | y    |
+---+------+
| 0 |    1 |
+---+------+
-- expect second row
select * from xy order by y asc limit 1 offset 1;
+---+------+
| x | y    |
+---+------+
| 0 |    1 |
+---+------+

This PR adds ordinals to rows in the TopN heap to differentiate insertion order in sort-groupings.

1689: Make merge_join pushdown safe
Index lookups were being pushed into merge join:

tmp1> explain select /*+ merge_join(xyz,uv) join_order(xy,uv) */ * from xyz join uv on x = u where y = 1;
+--------------------------------+
| plan                           |
+--------------------------------+
| MergeJoin                      |
|  ├─ cmp: (xyz.x = uv.u)        |
|  ├─ IndexedTableAccess(xyz)    |
|  │   ├─ index: [xyz.y]         |
|  │   ├─ filters: [{[1, 1]}]    |
|  │   └─ columns: [x y z]       |
|  └─ IndexedTableAccess(uv)     |
|      ├─ index: [uv.u]          |
|      ├─ filters: [{[NULL, ∞)}] |
|      └─ columns: [u v]         |
+--------------------------------+

In rare cases like above, this pushes an unsafe index into merge join that is not sorted monotonically on the join condition. A merge join index that violates join condition monotonicity will miss matches. In even rarer cases, the out of order value for the join condition included NULLs, causing this error: dolthub/dolt#5633.

1688: Support for recursive CTEs with no union
Ex:

with recursive a as (select 1) select * from a union select * from a;

Re: dolthub/dolt#5657

[1687](https://...

Contributors

dbussink

Assets 2

01 Nov 17:48

zachmu

v0.14.0

621dc30

Nov 2022 Release

This is a periodic rollup release. It contains many features, bug fixes, and improvements.

The core API has still not stabilized and will not be guaranteed until 1.0.

Merged PRs

go-mysql-server

1364: Updated and expanded engine examples
The README.md had an updated example, but the actual _example/main.go file did not. I've expanded the example file a bit to include information on setting up users, and slightly simplified the README.md example. I've also added tests for everything, so that if anything breaks, we'll know we need to update both the example file and the README.md portion.
1363: Convert errors during ComPrepare to SQLError
We were already converting errors in ComStmtExecute, ComMultiQuery, and ComQuery to SQLError so that the correct error codes would be sent to clients. This change adds that support to ComPrepare, too.
Added a unit test for that case and took the opportunity to simplify the interface for CastSQLError a little bit.
This change helps get Prisma support a little further along (dolthub/dolt#4511), but it doesn't look like it fully resolves everything Prisma needs to work with Dolt.
1361: Fixed collation check on foreign key columns

1358: fix example package
close #1357
This fixes runtime panic raised by example app in /_example.
I checked SQL client can obtain response in my local machine.

~/go-mysql-server/_example$ go build
~/go-mysql-server/_example$ ./_example

$ mysql --host=127.0.0.1 --port=3306 --database=mydb -u root
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from mytable;
+----------+-------------------+-------------------------------+---------------------+
| name     | email             | phone_numbers                 | created_at          |
+----------+-------------------+-------------------------------+---------------------+
| Evil Bob | evilbob@gmail.com | ["555-666-555","666-666-666"] | 2018-04-18 09:41:13 |
| Jane Doe | jane@doe.com      | []                            | 2018-04-18 09:41:13 |
| John Doe | john@doe.com      | ["555-555-555"]               | 2018-04-18 09:41:13 |
| John Doe | johnalt@doe.com   | []                            | 2018-04-18 09:41:13 |
+----------+-------------------+-------------------------------+---------------------+
4 rows in set (0.00 sec)

1356: add tests for sql type Zero() functions
Implementers of GMS might expect similar values to be returned by Convert() and Zero(). For decimal and enum implementations this was not the case and has been fixed.
1355: Allow any select statement for CREATE TABLE AS SELECT ...
Also fixes a semantics bug in the schema produced by some such statements.
1354: Bug fix for pushdownSort handling missing cols qualified with a table name
The Dolt bump for my GMS change to fix an alias issue in sort node pushdown triggered an error with matching missing column names now that we can include qualified column names.
This PR adds a repro for that case to GMS and fixes the issue by ensuring we create a UnresolvedQualifiedColumn when the missing column is qualified with a table name. I've run Dolt tests locally and confirmed there shouldn't be any other test failures in the next bump.
1353: Fix panic for show keys from information_schema.columns
Does this by removing the special handling of information_schema.columns as a separate node type, treats it just like any other ResolvedTable.
In the process, effectively rewrote how we handle column default values by 1) moving most logic to happen in the OnceBefore batch, rather than default rules, and 2) splitting it up into multiple passes that each have a single purpose. I found in the process of 1) that the previous rules had a lot of side effects and unintended ordering constraints, so introduced new rules and tweaked others to eliminate those.
1352: Update sort node to use alias reference when it will resolve missing columns
Fixes: dolthub/dolt#3016
Other changes:
- Refactors the existing OrderBy/GroupBy tests into a ScriptTest.
- Introduces a new interface, sql.Projector, that unites GroupBy, Window, and Project if a caller just needs to get the projected expressions.
1351: Add support for database collations
This allows setting the database collation, such that all newly created tables within a database (that do not explicitly set their collation) will inherit the database collation.
Builds on dolthub/vitess#199
1350: Subquery row iter fix field indexes
Recent changes to subquery scope visibility use the scope to communicate column definition availability; i.e., we do not pass the scope into subqueries we have determined to not depend on the outer scope, marking the same scope as cacheable. This analysis change needs a corresponding runtime change to indicate whether the scope row is expected at execution time.
1349: Skip process tracking when prepreparing queries
When we prepare a statement, the QueryProcess node we create is bound to the current context's PID. This is not the same PID as the context that will execute a statement created from that template, which results in ProcessList metadata not being properly cleaned up after a query has finished processing.
Fixes: dolthub/dolt#4601
I couldn't find a great way to test this in the GMS package, but I'm working on a test in dolt that I'll link to shortly.
1348: fix visibility for on duplicate key update
Currently, we treat plan.InsertInto.Source independently from Destination, and is not considered one of InsertInto's children. It is evaluated much later in the analysis process in the rule resolveInsertRules in a similar way as subqueries (we recurse the analyzer on it). This is problematic if we want to reference tables from Source.
In this PR, I resolve the tables for InsertInto.Source and added extra logic to correctly index those tables' columns.
There is a special case for on duplicate key update <expr> in that the LHS of the expr can only see Insert.Destination while the RHS can see Insert.Destination and Insert.Source.
Partial fix for: dolthub/dolt#4562
Note: This does not work for CTEs
This is only kind of a fix for the issue. The right way to fix this is probably to completely resolve InsertInto.Source before doing anything else, but I wasn't able to get that working yet.
1343: allow adding new primary key to table with > 1 row iff it has auto_increment
fix for: dolthub/dolt#4581
tests in dolt because memory.Table doesn't implement RewriteableTable
dolthub/dolt#4593
1341: fix comparison for geometry types
fix for: dolthub/dolt#3451
Maybe all geometry comparisons should just default to their EWKB formats; pretty confident this is what MySQL does.
1339: Update DateAdd/DateSub to return correct types.
This change fixes dolthub/dolt#4376
Previous implementation was hard-coded to return sql.Date, but now we are following the MySQL standard and return a type based on the inputs.
The tests in the repo are verifying that the correct data is returned, but we're not testing the actual SQL data that is received, so none of our tests are catching this case yet. We should open a new work item to start testing the actual SQL that's being returned by dolt. For this bug, testing was performed using a local version of dolt with these changes.
The failing query from the original bug is now working:
```
SELECT NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY), dolt_version();
+----------------------------+----------------------------------+----------------+
| NOW()                      | DATE_ADD(NOW(), INTERVAL 14 DAY) | dolt_version() |
+----------------------------+----------------------------------+----------------+
| 2022-10-18 18:53:24.406345 | 2022-11-01 18:53:24.406345       | 0.50.4         |
+----------------------------+----------------------------------+----------------+
1 row in set (0.02 sec)
```
1337: add support for GeometryCollection pt. 3
Part 10 of fix for: dolthub/dolt#3638
Changes:
- these functions no...

Assets 2

14 Jun 15:45

zachmu

v0.12.0

de8461d

June 2022 Release

This is a periodic rollup release. It contains many features, bug fixes, and improvements.

The core API has still not stabilized and will not be guaranteed until 1.0.

Merged PRs

go-mysql-server

1056: Allow REGEXP args to be converted
fix for #1055
1054: Fix prep AS OF
Prepared AS OFs errored when the asof target was not a bindvar. All of our previous tests treated ASOF also as a bindvar.
companion PR: dolthub/dolt#3592
1053: Added type wire tests
This enforces that we're returning the correct data over the wire (which may differ from the storage values). Majority of the fixes made have been moved to the parent PR (#1044) so that this one is not quite as large. They're all fairly straightforward, hence I don't think they require review.
The expected test output has been validated against MySQL (using the shim). In addition, this also enforces that the results are coming from the Type.SQL() function by running the output of a sql.RowIter (gathered from the directly querying the engine) through the Type.SQL() function and ensuring it matches.
1052: enginetest: Parameterize query plan tests
1051: Better join commutativity count
We excluded joins on the basis of join factors in the logical join tree,
rather than distinct join subtrees subject to commutativity. The
difference is that we have to permute n! to optimize the search space
of all valid commutative trees, versus a k^n (k = table rows) execution
runtime. 12! blocks analysis, k^12 is steep but can be OK depending on
cardinality and indexes. We impose no limits on k^n joins.
1047: sql/parse: Test round-tripping sql.Type as string
1046: Introduced OrderedIndex interface to deal with indexes that don't return ordered results
1044: Type value changes & row type assertions
This PR has two primary goals:
1. All types now pass their values around in a form most suitable to that type.
2. Enforce that the aforementioned types are always passed to integrators, such that integrators do not need to do type validation on their end.
  To elaborate on these points, some types already passed around values that were sensible and a best fit for that type, such as MEDIUMINT returning an int32. Other types, such as DECIMAL, passed around strings, which necessitated conversions for integrators to be able to properly persist the values. Not only that, there is currently no guarantee that a row's values each have their best fit type (a BIGINT can work with an int8, but it should be able to always expect int64). To make these guarantees, I'm adding a check at all GMS-integrator junctions that pass a sql.Row and verifying that the value types are exactly what that column expects.
  This may have the side effect of changing the output of a SELECT statement for integrators. As a SELECT statement simply returns a sql.RowIter, additional logic will be needed to convert all values to their canonical MySQL representation. This can easily be achieved by passing all values through Type.SQL() before display.
1043: enginetest: Validating Enginetest Harness
1042: exclude procedure aliases from showing information_schema.routines table
Not show procedure aliases in information_schema.routines table
Dolt PR tests this change
1041: cleanup enginetest
Splits up enginetest/enginetest.go into initialization.go and evaluation.go. The intent is to leave only test banks in enginetest.go
This is a pure refactor, no logical changes were made.
1038: Added a few foreign key tests
1037: Parallelize IndexedTableAccess on DoltDiff tables
1033: fix drop current database with case insensitive name
1032: Bug fixes for ModifyColumn related to primary keys
1029: fix some column values of information_schema.columns table
Fixes column_key, character_maximum_length, data_type and column_type column values of information_schema.columns table
1027: Moved responsibility for rewriting tables on add / drop primary key into engine
1026: More tests converted to new format
1025: adds mod() function
fix for: dolthub/dolt#3423
1022: Rewrite table for drop column
1021: Allow SHOW CREATE PROCEDURE for external procedures + display a fake CREATE PROCEDURE
A note regarding the fake CREATE PROCEDURE. It seems that Java's JDBC checks that the procedure creation statement is a valid statement, regardless of whether the statement remotely matches that of a stored procedure. So rather than returning a random statement, we return a "valid" CREATE PROCEDURE statement.
Fixes dolthub/dolt#3428 and dolthub/dolt#3424
1020: example memory.NewTable add "PrimaryKey:true" flag.
example memory.NewTable add "PrimaryKey:true" flag.
1019: UUID function is no longer based off current time
fix for: dolthub/dolt#3323
1018: support SRID for spatial type column definition
Added SRID value syntax functionality for column definition.
Added tests cover CREATE TABLE, ALTER TABLE ADD/MODIFY COLUMN, and INSERT statements.
Fixes dolthub/dolt#3425
1015: Thread safe procedure cache
1014: Added field for connected db to logger
1013: Moving transaction initialization before query analysis
Moves transaction creation ahead of query analysis, so that queries can be executed with the latest committed state from other transactions.
Fixes: dolthub/dolt#3402
1011: Add FK Checks for Insert on Duplicate
1009: sql/analyzer: Fix reresolveTables to not drop IndexedTableAccess and DeferredAsOfTable nodes on its transform.
enginetest: Add a test to assert IndexedTableAccess in a prepared statement
behaves correctly when querying a table that has been modified since the
prepare.
1008: Bug fix for rewriting table during modify column
1007: Refactor grant_tables to mysql_db
Mostly renaming variables and files to better reflect what they are now.
Added flatbuffer files for MySQL DB.
1005: Allowing "DEFAULT NULL" for blob column definitions
Fixes: dolthub/dolt#3388
1004: Support for rewriting tables on column modifications
1003: Replace statements that order by primary key, to use indexed table access
1002: New interface to rewrite table on certain schema change operations
Interface isn't quite final but this can be checked in. Complementary dolt changes are done and tested but want to get feedback on this approach first.
1000: Regression test case for dolthub/dolt#3247
dolthub/dolt#3247 reported a panic that our test cases didn't cover. Another commit (324e43b) already fixed the panic, so this PR just adds a quick test to ensure we don't regress with the same bug.
997: adding COLLATE to SHOW CREATE TABLE
Fix for: dolthub/dolt#3351
Also fix for: dolthub/dolt#3352
995: CheckpointHarness and analyzer mutexes
992: Allow unresolved tables and procedures in trigger body in CREATE TRIGGER
In order to allow non-existent tables and non-exitent procedures in trigger body in CREATE TRIGGER statement, we no longer run analyzer on trigger body, instead all validation checks are performed on a single rule, validateCreateTrigger.
Added tests for cases of di...

Assets 2

20 Oct 18:08

zachmu

v0.11.0

400af43

October 2021 Release

This is a normally scheduled quarterly release of the library with many improvements and feature additions.

APIs are not guaranteed to settle until 1.0.

Merged PRs

go-mysql-server

594: sql/plan: Fix join iterators to always Close() their secondary RowIter. Fix Subquery to Dispose its subquery.
593: Make some update queries determinate
591: Introduced a ViewProvider extension
As part of this, got rid of the IndexRegistry and ViewRegistry on the context, put them on the Session instead. These changes significantly simplify the process of constructing an engine and running queries.
590: Add dolt discord to readme
588: Made it possible to use variables in AS OF expressions
As part of this, also pulled resolving variables out of the resolve_columns step into its own rule.
583: Update INNER JOIN Alpha
582: sql/plan: exchange.go: Make Exchange's RowIter wait for all goroutines to shutdown cleanly.
If we do not block on shutting down the goroutines that are handling the
partitions, we cannot guarantee that we will not race with later uses of the
sql.Session, for example.
This converts the implementation to x/sync/errgroup and restructures things
quite a bit.
581: Skip fk validation on CREATE TABLE when fks are disabled
579: Fixed panic on using an alias of a subquery expression in a where clause (now just an error)
578: enginetest: Add BrokenQueries tests for projections in group by nodes not handling subquery expressions well.
577: sql/analyzer: validation_rules: Disable validateSubqueryColumns, which was enabled in the latest releases but is still broken in some cases.
576: Add Read Only transaction functionality
575: Fixed bug in indexed joins
When a query had two copies of the same table, and it could use an index to join them, it was non-deterministic which of the tables' (identical) indexes would be used. This choice doesn't matter for some implementations or even most queries, but in Dolt, if a query involves the same table at two different revisions (because of AS OF), it was arbitrary which table's index got returned. Because the dolt index implementation gets its data from its parent table, if it chose the wrong index it got the wrong data.
This change restricts the choice of index to a single table name.
Need tests demonstrating the bug, but those will have to live in Dolt for the time being.
573: Add several features that unblock mysql workbench
This pr implements
1. Show STATUS
2. Set CHARACTER SET
572: Add the EXISTS Operator for Select Where Filters
Adds the exists operator as described here: https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
568: sql/analyzer: indexed_joins: Keep searching for usable indexes when there are NOT(? = ?) and NOT(? <=> ?) clauses in a join conjunction.
The indexed join logic is perfectly happy to go forward with a join plan if it
doesn't find a perfect index, or even a usable index at all, for one or more
table factors in the join. This logic is probably left over from when that was
not the case, but for now we make it a little more liberal to cover some cases
we need to cover for a customer.
567: sql/plan,analyzer: Fix HashLookup for cases where there is a schema prefix not visible to the direct join parent.
This adds a TransformUpCtx function that passes along a SchemaPrefix in the TransformContext if the schema prefix is scrutable from resolved children at that point in the analysis. apply_hash_lookup makes use of this to make the transformed expressions in the HashLookup lookup node refer to the right place, even when JoinNode.Left().Schema() doesn't have the whole prefix.
This schema prefix is probably useful in other places and I am exploring rationalizing certain places where the analyzer makes use of the schema by using it or something like it.
In the mean time, converted some of the more obscure transform variants (UpWithParent, UpWithSelector) to use TransformUpCtx as well. Held off on moving TransformUp to TransformUpCtx.
Very open to suggestions on names for TransformUpCtx.
566: sql/analyzer: Make SubqueryAlias nodes always cacheable.
565: sql: Removing ctx parameter from Expression.WithChildren.
Also removes it from FunctionFn types, TransformExpression... functions, and Aggregation.NewBuffer.
We think this ctx parameter might have been added a few months ago as part of some optimization work which never made it across the line. Instead of threading a *sql.Context everywhere, if we have need of a *sql.Context during analysis or to precompute or materialize a certain result in the future, I think I'm going to advocate for a specific optional interface that the analyzer is aware of. It could then pass the context through at a specific analyzer phase and the function/expression node would have the opportunity to get ready to do what it needs to.
564: sql/plan: Defer returning error from IndexedInSubqueryFilter.RowIter until the Next() call.
Fixes some interactions between INSERT IGNORE INTO and expressions which will
fail on evaluation.
This might be a credible strategy everywhere we .Eval within RowIter, for
example, in indexed_table_access.
563: Added an extension point for custom function providers
As part of this, embarked on a major refactor:
- Extracted interfaces for sql.Catalog and sql.ProcessList
- Moved existing sql.Catalog to analyzer package
- Moved ProcessList and MemoryManager out of Catalog
- Changed Analyzer and Engine to take a DatabaseProvider instead of a Catalog
560: Update some analyzer rules and expression behavior to deal with tuples in a more principled way.
Subquery expression nodes can now return tuples.
InSubquery expression nodes can work with tuples as expected.
An analyzer validation step now returns operand errors in more cases, expecting
almost all expressions to return one column, but special casing certain
operators and functions which support tuples.
Added some TODO tests for cases where our tuple comparisons are still not
behaving as we want them to. In particular, null safe vs. non-null safe
comparisons and type coercion of tuple subtypes in comparisons still need work.
559: sql/analyzer: aliases.go: Make sure we add the right table alias when traversing a DecoratedNode.
557: sql/analyzer/optimization_rules.go: moveJoinConditionsToFilter: Fix small inaccuracy where computed topJoin could be wrong.
This could result in the optimization pass adding the same Filter node to
multiple places in the join tree.
556: sql/plan/indexed_table_access.go: Change static index lookup nodes to not keep returning their unused key expressions.
These needs take key expressions, but they do not evaluate them. They are not
part of the evaluation tree, but they cause some problems with things like
FixFieldIndexes and prune_columns, where all GetField expressions in the plan
tree are expected to resolve to fields that are actually in scope and
resolvable at the node that is being evaluated.
This fixes a particular evaluation bug where a subquery expression in a join
condition gets moved to the filter above the joins. If the moved subquery
expression made use of a static index table lookup, the moved expression would
fail to rewrite its field indexes appropriately and the query would return
incorrect results.
555: Fix handling of secure_file_priv for LOAD DATA
554: Added locks, todo to mutable session state
553: Wrote proper number conversion logic
Previously we were relying on the cast library, which had a few issues with how we expect SQL numbers to function. One such issue was how number strings were handled (such as interpreting "01000" as a binary number rather than in decimal). There have been more issues in the past that have gone undocumented, but this should fix them (or allow for an easy fix since it's all custom logic now).
552: Fixed actual default collation representative
551: Check Error when building Session
549: Mutable DatabaseProvider refactor
548: Fix Resolve Defaults
[547](https:/...

Contributors

zachmu

Assets 2

02 Jun 18:35

zachmu

v0.10.0

9dbddef

June 2021 Release

This is a development release, including several new features and performance improvements.

Merged PRs

go-mysql-server

445: Allow limits and offsets to use value args (? in prepared statements)
This fixes #439
441: Test fixes for savepoints
440: /{.github,go}: bump min go version to 1.15
437: /.github/workflows/format.yml: enable autoformatting
435: /.github/workflows/bump-dependency.yaml: remove working dir, doesnt exist
434: Rewrote transaction tests to be much more succinct, and added several more
433: Fixed non-deterministic test query
432: Repro test case for ErrFieldMissing bug
431: Fixed foreign key errors in NTC test setup
430: Fixed bug in field indexes related to pushdown of indexes in subqueries
429: Added statement boundaries
427: Initial percent_rank fn
- percent_rank function implemented, which is basically the step function quantile of column-sorted rows within a partition.
- moved helpers to window/window.go
- created WindowExpression interface to facilitate helper function re-use (there are multiple ways of doing that, extra interface isn't super necessary but made sense to me at the time)
  todo:
- bats with great expectation specific query
426: added ErrIndexOutOfBounds test
425: Bug fixes for date comparisons
This fixes dolthub/dolt#1572
424: Transaction support
This change:
- Moves a bunch of transaction and other session management code out of Handler into Engine
- Introduces a new set of tests around transactions
- Fixes the error message for duplicate key violations
- Removes AsyncNode and related code
- Eliminates duplicate query parsing
423: Vinai/distinct expressions
This pr does the following
1. Fix incorrect behavior with AVG
2. Enable the use of DISTINCT within aggregation eg. SUM(DISTINCT )
3. Enables the use of DISTINCT on aggregation that is grouped
422: Fix for hanging joins when the secondary table is empty using an in-memory strategy
This fixes #222
421: fix bug in join search
420: Update sqlogic test harness and bump sqllogic dependency
This pr enables users of gms to run and parse the sqllogic harness against all of the sqlogictests (with filtering). It also bump the sqllogic dependency
419: [WIP]: LastIncrementId and name resolution collision fix
418: Add ISNULL(expr) and compatible with Navicat Premium
When I use Navicat Premium 15.0.28 to design tables.
Get error: 1105 - function: 'isnull, maybe you mean ifnull?' not found
After I implement ISNULL(expr).
Get error: 1105 - column "PARTITION_DESCRIPTION" could not be found in any table in scope
This RP is fixed this problem.
417: set autocommit status flag
416: ReleaseSavepoint fix
415: Rollback and savepoint support
414: Vinai/show create table check constraints
This pr allow for the printing of check constraints during "SHOW CREATE TABLE"
410: Commit transactions without beginning them, to support commit without a selected database
409: Add the partitions table
Adds the empty partition table
404: sql/analyzer: Push filters under SubqueryAliases which they apply to.
This allows optimizations to be applied to the filter clauses within the SubqueryAlias node, including pushdown and index lookup.
403: sql/analyzer: Split analysis of subqueries so that once-after rules are applied uniformly.
This makes is so that rules through default-rules get applied as part of
analyzing the subquery. Later, early in the once-after phase, union and
subquery queries get their own once-after processing.
This allows us to keep the node tree in a state where we can apply
transformations and optimizations across Opaque node boundaries, and come back
for further processing later.
402: Added _binary support & REGEXP_LIKE
Removed REGEXP_MATCHES as it does not exist in MySQL. It is a PostgreSQL function.
5.7 documentation: https://dev.mysql.com/doc/search/?d=12&p=1&q=regexp_matches
8.0 documentation: https://dev.mysql.com/doc/search/?d=201&p=1&q=regexp_matches
PostgreSQL: https://www.postgresql.org/docs/9.6/functions-matching.html
It was added before we forked the project, so I removed it. Not sure why it was ever added to begin with, as no MySQL client will ever expect it nor know how to interact with the results.
401: Vinai/json contains
Adds the json_contains function
400: Support for transactions. Start transaction and Commit implemented so far, as well as @@autocommit handling
Needs tests, which are harder to write than standard engine tests
398: Fixed view test script
397: Changed default handling & delay fk table resolution
396: /server: Unwrap netutil.ConnWithTimeouts in pollForClosedConnection
395: Refactor nil check handling
nil responses won't throw check constraint errors anymore
394: Fixed bug that only allowed some select statements as view definitions
392: Fixed REPLACE logic to now match MySQL
Previously we thought that REPLACE was a DELETE then INSERT, but this is not the actual order that MySQL takes. According to the MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/replace.html):
```
MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):
Try to insert the new row into the table
While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
Delete from the table the conflicting row that has the duplicate key value
Try again to insert the new row into the table
```
Special emphasis on the While, as you have to keep trying until you either get a different error, or it succeeds. So this has now been duplicated.
391: Install a format checker script and workflow
390: Adds the key column usage table
https://dev.mysql.com/doc/refman/8.0/en/information-schema-key-column-usage-table.html
389: Vinai/constraints table
Created new pr due to past problems
388: Added savepoint no-ops
387: server/handler.go: Use exposed mysql.Conn.Conn instead of trying to track it through Accept.
385: fix deadlock with exchange node go routines
This fixes deadlocks in exchange node go routines which can happen when the exchange node is closed before all the data is read as occurs with a query that includes a limit clause. This also can occur if the context is canceled. These deadlocked go routines will retain references and prevent garbage collection.
382: Fixed system variables
381: Vinai/insert ignore into
This PR adds support for INSERT IGNORE INTO. It is currently missing functionality for ignoring typer errors. After scoping the type errors component of this it looked to a lot of work to capture all of them with the correct MySQL notation
I also noticed a lack of partition support in MySQL terms as well so I ignored that.
380: add existing row to unique constraint failure
379: sql/plan/if_else.go: Handle conditional expressions evaluating to null as false.
378: sql/{plan,analyzer}: Add hash lookup functionality for equality condition joins on already cached results.
377: sql/plan/join.go: Improve correctness and tes...

Assets 2

02 Apr 00:54

zachmu

v0.9.0

03e3985

Q2 2021 Release

This is a development release. It contains many features and performance improvements added since the last release.

Merged PRs

353: Column aliases for subqueries
342: Added partial SIGNAL and DECLARE CONDITION support
341: JSON_OBJECTAGG
This pr adds JSON_OBJECTAGG functionality: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-objectagg
340: Vinai/UUID
This pr adds UUID support cc: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_is-uuid
339: Wrap all select expressions in aliases with their original string expression
This makes the result set schema exactly match the select expressions in the query.
338: Vinai/dbeaver fixes
This pr allows for queries like CREATE TABLE x.table(pk int) when the mysql client is not set to x.
337: Vinai/more table status fixes
This pr fixes some mistakes in show table status where
1. memory/table.go did not implement StatisticsTable
2. ShowTableStatus is a part of Engine InformationSchema Tests
335: Updates to SHOW TABLE STATUS
334: Fixed procedure parameters missing in subqueries
333: SHOW CHARSET & SHOW ENGINE
First step in getting some sql engines
332: JSON Values
This change moves the engine towards representing JSON values as Go values rather than strings.
I'm strongly considering a
```
type JSONValue interface{}
```
There seems to be no precedent for this
331: sql/{plan,analyzer}: {join,indexed_join}: Update join logic to work within subqueries when query scope is non-empty.
330: Support for WITH clauses (common table expressions)
329: sql/analyzer: join_search: Update indexed join search to respect structural requirements on left and right joins.
Only commute join nodes when they are actually safe to commute.
328: Vinai/load data quick fixes
Add quick fixes from testing load data on the Dolt side.
327: Add functionality and tests for JSON_ARRAGG.
This pr does not include window function capabilities
326: Added function stubs for unimplemented JSON functions
31 unimplemented JSON functions, including two aggregation functions JSON_ARRAYAGG and JSON_OBJECTAGG.
325: sql/analyzer/indexed_joins.go: Use partial indexes for indexed joins when a full index isn't available.
324: sql/analyzer/filters.go: Never push a filter expression with a subquery down.
323: sql/analyzer/prune_columns.go: Fix scope parameter in fixRemainingFieldsIndexes when fixing Child of a subquery alias.
322: sql/{plan,analyzer}: Cache subquery results when joining against a subquery.
321: Bug fix for parallelization
We have been parallelizing Filter and Project nodes, but this is a problem for such nodes that contain a Subquery expression, since the query plan for that subquery may not tolerate parallelization. This fixes that bug and adds tests.
320: Added proper PROCEDURE nested SELECT behavior + tests
319: sql/analyzer: Avoid pruning columns of nodes under an IndexedJoin, since the IndexedJoin expression itself is inscrutable.
318: sql/analyzer/pushdown.go: Push down appropriate filter predicates to sit right on top of a SubqueryAlias when appropriate.
317: Max/check constraint
check constraints in memory engine
depends on dolthub/vitess#53
TODOs maybe:
- in mysql, an ALTER TABLE ADD CHECK fails if any of the existing rows fail the check. This is what the current implementation does, I'm not sure if toggling that off should be supported.
- when an INSERT row that fails a constraint, mysql errors and does not write any of the values. The current implementation defaults to INSERT IGNORE's behavior, where warnings are issues for failed checks but good rows are still included.
  TODOs in future:
- DropColumn, RenameColumn, ModifyColumn should error if touching a constraint-protected columns
316: Vinai/load data - Part 1 very basic functionality review
This PR introduces basic functionality for LOAD DATA.
315: Added SHOW & DROP PROCEDURE, along with basic IF/ELSE support
315: Added SHOW & DROP PROCEDURE, along with basic IF/ELSE support
314: sql/analyzer/prune_columns.go: Turn off pruneColumns when a Subquery expression exists.
312: Added INOUT/OUT params to procedures & nested procedures
Stored procedures now work with INOUT/OUT directions for their parameters. Not only is this critical for nested CALL statements to work properly, but additional changes were made to handle nested CALLs. Stored procedures are now loaded before analyzing the query body.
311: sql/analyzer/resolve_subqueries.go: Make subqueries which include subqueryaliases cacheable.
310: sql/plan/subquery.go: Make certain that analyzed nodes below a subquery that includes a subquery alias node see the row results they are expecting.
309: Max/float53
Follow specific rules for mysql float/double/error depending on the float ops.
308: Window functions
This is a full implementation of window functions, with only the ROW_NUMBER() function supported. Partitions and order-by work for the OVER() clause, but other constructs (PRECEDING, FOLLOWING, etc.) are not supported.
Other limitations:
- Can't use window functions with GROUP BY
- Can use non-window aggregates with window functions, but only if they are declared with an empty OVER clause
307: Bump vitess
306: Roughest implementation of stored procedures
No need to go into detail regarding what doesn't work (a lot), but it's groundwork to iterate on.
305: sql/analyzer: resolve_subqueries.go: Add a missing stripQueryProcess in resolveSubqueries.
304: pass the context into Close calls
303: sql/analyzer: Make join_search faster
This helps the analyzer actually complete analyzing some of the example queries we're working on that have 9+ table joins. The basic idea is to recursively build every binary tree that has the in-order traversal we want and then attempt to apply join conditions to those trees.
Performance here can be further improved by coupling visitJoinSearchNodes and assignConditions so that we can prune large portions of the subspace as being impossible from a join conditions standpoint. I may work on that next depending upon how further benchmarking goes. Currently a previous query which failed to analyze before take 30s.

301: Naive/hacky show grants
hard-coded SHOW GRANTS handling:

> dolt sql -q "show grants for current_user"
+-------------------------------------------------------------+
| Grants for root@%                                            |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+

300: Added Stored Procedure interface for integrators
299: sql/{plan,analyzer}: Add IndexedInSubqueryFilter for selecting the subquery results first and then making indexed lookups into the child.
298: Vinai/create database
Add functionality for CREATE DATABASE
297: Max/sqlerror codes
Append error codes/sql state to error messages for downstream clients.
296: Fixed BEGIN/END blocks with embedded SET statements
BEGIN/END blocks did not return their results, and also did not build off of previous statements within the block. In addition, they were panicking by carrying a nil c...

Assets 2

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Merged PRs

go-mysql-server

Merged PRs

go-mysql-server

Merged PRs

go-mysql-server

Contributors

Merged PRs

go-mysql-server

Merged PRs

go-mysql-server

Contributors

Merged PRs

go-mysql-server

Merged PRs

go-mysql-server

Merged PRs

go-mysql-server

Contributors

Merged PRs

go-mysql-server

Merged PRs

Releases: dolthub/go-mysql-server

Q2 2024 Release Patch

Merged PRs

go-mysql-server

Q1 2024 Release

Merged PRs

go-mysql-server

Aug 2023 Release

Merged PRs

go-mysql-server

Contributors

Q3 2023 Release

Merged PRs

go-mysql-server

Q2 2023 Release

Merged PRs

go-mysql-server

Contributors

Nov 2022 Release

Merged PRs

go-mysql-server

June 2022 Release

Merged PRs

go-mysql-server

October 2021 Release

Merged PRs

go-mysql-server

Contributors

June 2021 Release

Merged PRs

go-mysql-server

Q2 2021 Release

Merged PRs