A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.
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.
@@server_id default value match MySQLbinlog_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").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: https://github.com/dolthub/dolt/issues/7692
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: https://github.com/dolthub/dolt/issues/7646
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.Com_delete, Com_insert, Com_update status variables
related: https://github.com/dolthub/dolt/issues/7646
@@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 https://github.com/dolthub/dolt/issues/7651
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: 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.
https://github.com/dolthub/dolt/issues/7646
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.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: https://github.com/dolthub/dolt/issues/7651
BinlogPrimaryController interface. This new interface pretty closely mirrors the existing callback interface for replica callbacks, the BinlogReplicaController interface.
Related to https://github.com/dolthub/dolt/issues/7512
json_search()
MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/json-search-functions.html#function_json-search
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: https://github.com/dolthub/dolt/issues/7634
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.dbName in the example is actually being used, instead of having a hardcoded "mydb" in createTestDatabase.
fixes #2402CREATE TABLE ... LIKE ... needs to preserve table opts, like commentsalter table add column ... auto_increment does not work when there are already rowsON 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'
sql_mode = 'NO_AUTO_VALUE_ON_ZERO'
This PR implements the sql_mode NO_AUTO_VALUE_ON_ZERO.
This makes it so that 0 values (not NULL) do not increment the auto_increment counter.
MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/example-auto-increment.html
fixes https://github.com/dolthub/dolt/issues/7600
memory.NewSessionBuilder method, now used in the example code.IF NOT EXISTS and table exists
This PR addresses various issues related to CREATE TABLE IF NOT EXISTS ... queries.
Before, we simply ignored the table exists error, and continued creating indexes, foreign keys, and checks.
This led to errors when attempting to create indexes/foreign keys/checks that already exists.
Additionally, it would errorneously create indexes/foreng keys/checks that did exist.
The correct behavior is to do nothing if IF NOT EXISTS is specified and the table exists.
Also this contains some refactors and simplifications.
fixes https://github.com/dolthub/dolt/issues/7602
oos: linux
goarch: amd64
pkg: github.com/dolthub/go-mysql-server/sql
cpu: AMD Ryzen 9 7900 12-Core Processor
│ b1 │ b2 │
│ sec/op │ sec/op vs base │
HashOf-24 79.65n ± 4% 70.86n ± 7% -11.03% (p=0.002 n=6)
ParallelHashOf-24 10.47n ± 4% 11.85n ± 19% ~ (p=0.368 n=6)
geomean 28.88n 28.98n +0.32%
│ b1 │ b2 │
│ B/op │ B/op vs base │
HashOf-24 4.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
ParallelHashOf-24 4.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
geomean 4.000 ? ¹ ²
¹ summaries must be >0 to compute geomean
² ratios must be >0 to compute geomean
│ b1 │ b2 │
│ allocs/op │ allocs/op vs base │
HashOf-24 2.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
ParallelHashOf-24 2.000 ± 0% 0.000 ± 0% -100.00% (p=0.002 n=6)
geomean 2.000 ? ¹ ²
¹ summaries must be >0 to compute geomean
² ratios must be >0 to compute geomean
type.Promote() for all types.
Additionally, there are some inconsistencies with MySQL when performing these checks with NUL characters (\0). They are skipped tests for now.
related https://github.com/dolthub/dolt/issues/7588
json_pretty
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-pretty
COM_BINLOG_DUMP_GTID command
Stubbing out support for two new mysql.Handler methods to support streaming binlog events from a server to a client.
Depends on Vitess PR: https://github.com/dolthub/vitess/pull/317
Related to https://github.com/dolthub/dolt/issues/7512
straight join to inner join
This PR temporarily remaps STRAIGHT_JOIN operator to INNER_JOIN operator.
fixes https://github.com/dolthub/dolt/issues/7580
binary cast to maintain length
We were improperly dropping CAST node during comparison.
It might be fine in many cases, but it is definitely not for comparing BINARY column types.SystemVariable and SystemVariableScope interfaces.
This allows doltgres to use these interfaces for defining and handling all the configuration parameters.
The SystemVariable struct is renamed to MysqlSystemVariable.
The SystemVariableScope byte is renamed to MysqlSVScopeType.charset table option tests
In GMS, we reparse table options with a regular expression, but we only cover CHARACTER SET and not its synonym CHARSET.
As a result, we just ignore table options for CHARSET.
The fix is in https://github.com/dolthub/vitess/pull/315
TODO: maybe should just address this TODO instead...JSON_QUOTE()
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-quote
SHOW ENGINES; vs select * from information_schema.engines;
Fortunately, the way we do SHOW ENGINES; is actually a SELECT ... under the hood.
So the hacky fix is to just use an alias :)
related https://github.com/dolthub/dolt/issues/7574
JSON_MERGE() and JSON_MERGE_PATCH()
MySQL Docs:
JSON_OVERLAPS()
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-overlaps
go-mysql-server requires a different way of instantiating the database server. Notably, a function that provides sessions must now be provided.
This change updates our README with the latest working example code from _example/main.go.
Related to https://github.com/dolthub/go-mysql-server/issues/2364
random_bytes()
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_random-bytes
last_insert_uuid() function
UUIDs are often used in place of auto_increment IDs, but MySQL doesn't provide an easy way to get the last generated UUID that was used in an insert. This change introduces a new function, last_insert_uuid() that operates similarly to last_insert_id(). For a column identified as a UUID column, callers can use last_insert_uuid() to retrieve the last generated UUID value that was inserted into that column. In order to be considered a UUID column, a column must be part of the primary key and it must meet one of the following type signatures:
VARCHAR(36) or CHAR(36) with a default value expression of UUID()
VARBINARY(16) or BINARY(16) with a default value expression of UUID_to_bin(UUID()) (optionally, the swap_flag for UUID_to_bin may also be specified)create table t (pk binary(16) primary key default (UUID_to_bin(UUID())), c1 varchar(100));
insert into t (c1) values ("one"), ("two");
select last_insert_uuid();
select c1 from t where pk = uuid_to_bin(last_insert_id());
ADDDATE()
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate
now() string
The BATS tests import-mysqldump.bats was failing due to a recent string replace case added by https://github.com/dolthub/go-mysql-server/pull/2357.
I didn't account for when NOW() would be used with other functions inside a DEFAULT column expression, and was replacing the "NOW(" with "CURRENT_TIMESTAMP(". This was causing problems as we reparse ColumnDefaultExprs, resulting in unparenthesized default expressions, which threw an error.ON UPDATE CURRENT_TIMESTAMP precision
I had mistaken assumed that MySQL did not allow any precision arguments for NOW() (and synonyms) for column DEFAULT and ON UPDATE expressions. It turns out MySQL only requires that the column type precision match the expression.
Additionally, we did not perform these error checks for DEFAULT expression, so now we do.
fixes https://github.com/dolthub/dolt/issues/7555
JSON_TYPE()
MySQL Docs:
https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
Companion PR:
https://github.com/dolthub/vitess/pull/314
Length() function in the BinlogEvent interface so calling code can access the event size present in a binlog event's header. Needed for calculating binlog file position.FakeBinlogStream → BinlogStream so calling code can use it when serializing binlog events.time.Time and decimal in bindvars
We were unable to use time.Time and decimal type variables bind vars, so they couldn't be used as arguments to prepare statements.
This PR addresses that issue.
fixes: https://github.com/dolthub/dolt/issues/7665
SOURCE_AUTO_POSITION
When acting as a replica, Dolt implicitly assumes SOURCE_AUTO_POSITION is set to 1. This adds parser support so that it can be explicitly specified. Adding this so that when we're configuring a MySQL replica, we can use the same setup code without having to special case this parameter.vitess.io/vitess – support for the COM_REGISTER_REPLICA command.COM_BINLOG_DUMP_GTID command.String functions to help with debugging.COM_BINLOG_DUMP_GTID command.
Related to https://github.com/dolthub/dolt/issues/7512
binary as a valid charset option.
Additionally, this changes CHARSET to expand to synonym CHARACTER SET for parsing in GMS.
fixes https://github.com/dolthub/dolt/issues/7576
FROM s.account, in which account is non-reserved keywordFunctional Key Parts index ?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.
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: https://github.com/dolthub/dolt/pull/6547#issuecomment-1686795603
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.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.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
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:
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
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.db> select JSON_OBJECT("a", Now());
unsupported type: time.Time
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: https://github.com/dolthub/dolt/issues/6455
AntiHashJoin and LeftOuterHashExcludeNullsJoin should always evaluate at least one secondary row if the secondary table is nonempty. This guarentees the same behavior as MySQL for where x not in (...) expressions when x is nullable.
Some joins (AntiJoins and some joins converted from AntiJoins) have an "excludes NULL" property, which means that if a filter condition on the join evaluates to NULL, the corresponding primary row can't be included in the result set.
This means that if the primary row has a NULL value used in a filter, we need to know whether or not the secondary table has at least one row. Using a HashJoin makes that impossible without special handling.
We had a test designed to catch this, but the test wasn't actually using a HashJoin, so this was missed. This PR also fixes the test to use a hash join.JSON_ARRAY correctly handles CHAR bindvar params
The JSON_ARRAY function wasn't able to handle a []byte literal expression in a bind var. I also noticed that although our script tests can specify Bindings, they weren't getting used by our prepared query runners. I also fixed that, so that I could add a new test for this issue.
Fixes: https://github.com/dolthub/go-mysql-server/issues/1855
Related Vitess change: https://github.com/dolthub/vitess/pull/261
Dolt CI Checks: https://github.com/dolthub/dolt/pull/6441
ANSI_QUOTES support for go-mysql-server
Now that our parser has support for parsing in ANSI_QUOTES mode and treating double quotes as identifier quotes, this PR starts using that support when we parse queries. The main changes in this PR are:
SqlMode type to make it easier to load SQL_MODE and inspect enabled modes.FULLTEXT indexes. The ordering bug is fairly major, as it could require a drop/readd of the index, so I want to get this out now rather than waiting until I've gotten more tests/fixes in.seqIOCostFactor feels like a bit of a misnomer, but cpuCostFactor is too low to reflect this behavior, and this is what costInnerJoin uses.add column .. unique, but the same approach should also work with auto_increment and other clauses.
Relies on changes in https://github.com/dolthub/vitess/pull/260
USING clause during joins.
It is mostly equivalent to creating a conjunction over equality statements over each column in an ON clause.
Additionally, this properly supports NATURAL LEFT/RIGHT JOINs.
So we now have
... JOIN ... USING (...)
... LEFT JOIN ... USING (...)
... RIGHT JOIN ... USING (...)
... NATURAL JOIN ...
... NATURAL LEFT JOIN ...
... NATURAL RIGHT JOIN ...
fix for: https://github.com/dolthub/dolt/issues/5789
[]byte have been converted to string literals with the default collation (utf8mb4_0900_bin) by types.CreateStringWithDefaults().
I think types.CreateBinary() should be used, which generates literals with binary collation.ANSI_QUOTES SQL mode
PR https://github.com/dolthub/vitess/pull/256 adds support at the Vitess layer for parsing SQL statements using ANSI_QUOTES SQL mode, where double quotes are used as identifier quotes, and not string literal quotes. This change updates the implementations of mysql.Handler for the new function needed to parse prepared statement commands with ANSI_QUOTES mode. A future PR will enable the ability to turn on the ANSI_QUOTES support in Vitess.WHERE filter over SHOW VARIABLES
Fix for: https://github.com/dolthub/dolt/issues/6379
reflect.Value.Int() on an unsigned typed value will panic.
The reflect.Value.Uint() must be used.plan.EmptyTable implement sql.DeletableTable
We throw an error when trying to delete from EmptyTable.
This error is partially caused by https://github.com/dolthub/go-mysql-server/pull/1885
The error didn't show up in GMS as there are Exchange nodes that appear in dolt side that don't appear here.Offset nodes and drop 0 Offsets
This PR applies the index sort optimization to plans that look like LIMIT(OFFSET(SORT())).
Additionally, this PR drops OFFSET nodes that start at 0, as they don't change the output.
Fix for: https://github.com/dolthub/dolt/issues/6347
plan.EmptyTable implement sql.Updatable interface
In order to not throw table doesn't support UPDATE error, the EmptyTable node should implement the Updatable interface.
Additionally, there are other private noop iterators to prevent nil panics.
Fix for: https://github.com/dolthub/dolt/issues/5397
%v format option in the fmt packages defaults to scientific notation, so like 1.234e567.
This does not work well with our (hacky) string code.
The %f option doesn't work very well, as it likes to append .00000.
It appears strconv.FormatFloat does what we want, so I just picked that.
fmt package docs: https://pkg.go.dev/fmt
Format Float docs: https://pkg.go.dev/strconv#FormatFloat
fix for: https://github.com/dolthub/dolt/issues/6322
mysql.help_ tables
This first pass adds the table schemas to the mysql database for help_keyword, help_category, help_topic, and help_relation. There is no support for data in the tables yet; we're starting with just table schemas to see if that's enough for tool compatibility.
Related to: https://github.com/dolthub/dolt/issues/6308
I still need to do acceptance testing with the exact repro in the issue linked above. I'll follow up with Max on that tomorrow to confirm. I'm hoping just supporting the schema will be enough for the FusionAuth tool, but we may end up needing to populate these tables, too.TODOs), but this shows the broad strokes of how it's implemented, along with most of the "difficult" design choices being implemented. The major choice that has not yet been finalized is how to deal with FTS_DOC_ID, as it's an AUTO_INCREMENT column in MySQL, but that would not play well with Dolt merging. I already have ideas on how to handle that (taking into account remotes, etc.), but that would come from a later PR.
https://docs.google.com/document/d/1nGyYg461AhxQjFLzhEEj01XMz0VaTBaBaA44WNu0fc4/edit
Quite a few things have changed from the initial design doc, mostly based on feedback during the meeting, however some of it was post-meeting. There are three tables instead of 1: Config (stores table-specific information shared across all indexes), WordToPos (maps words to an ID and position, not fully used in the default search), and Count (used to calculate relevancy, also not fully used in the default search). I was planning on converting MATCH ... AGAINST ... to a join between the tables, which would work when fetching results, but MATCH ... AGAINST ... may also be used as a result, which necessitated writing all of the functionality anyway, so the join plan was dropped.
Last thing to mention, is that I'm pretty sure that Full-Text indexes actually do a full table scan. It seems weird, but AFAICT the indexes are used to quickly calculate relevancy for each search mode. It seems that, for overly large tables, the search time increases even when other index operations continue to operate nearly instantaneously.
I've tagged two people for review to make it a bit easier. Of course, feel free to take a look at more if you desire.
@reltuk The sql/fulltext/fulltext.go file is an expansion of the file you've previously reviewed (all still kept to a single file for now). To complement it and see how it'll be implemented on the Dolt side, you can look at memory/table.go. Dolt's table editor will be similar, and the merge paths will only use the FulltextEditor, which special logic to interface with it from those paths.
@max-hoffman Take a look at the analyzer changes, along with the sql/plan/ddl.go file. You'll probably need to reference sql/fulltext/fulltext.go as well.Sourced from google.golang.org/grpc's releases.
Release 1.53.0
API Changes
- balancer: support injection of per-call metadata from LB policies (#5853)
- resolver: remove deprecated field
resolver.Target.Endpointand replace withresolver.Target.Endpoint()(#5852)
- Special Thanks:
@kylejbNew Features
- xds/ringhash: introduce
GRPC_RING_HASH_CAPenvironment variable to override the maximum ring size. (#5884)- rls: propagate headers received in RLS response to backends (#5883)
Bug Fixes
- transport: drain client transport when streamID approaches MaxStreamID (#5889)
- server: after GracefulStop, ensure connections are closed when final RPC completes (#5968)
- server: fix a few issues where grpc server uses RST_STREAM for non-HTTP/2 errors (#5893)
- xdsclient: fix race which can happen when multiple load reporting calls are made at the same time. (#5927)
- rls: fix a data race involving the LRU cache (#5925)
- xds: fix panic involving double close of channel in xDS transport (#5959)
- gcp/observability: update method name validation (#5951)
Documentation
- credentials/oauth: mark
NewOauthAccessas deprecated (#5882)
- Special Thanks:
@buzzsurfrRelease 1.52.3
Bug Fixes
- Fix user-agent version
Release 1.52.2
Bug Fixes
- xds: fix panic involving double close of channel in xDS transport (#5959)
Release 1.52.1
Bug Fixes
- grpclb: rename grpclbstate package back to state (#5963)
Release 1.52.0
New Features
- xdsclient: log node ID with verbosity INFO (#5860)
- ringhash: impose cap on
max_ring_sizeto reduce possibility of OOMs (#5801)Behavior Changes
... (truncated)
dba26e1 Change version to 1.53.0 (#5983)2a1e934 server: after GracefulStop, ensure connections are closed when final RPC comp...e2d69aa tests: fix spelling of variable (#5966)a6376c9 xds/resolver: cleanup tests to use real xDS client 3/n (#5953)bf8fc46 xds/resolver: cleanup tests to use real xDS client 5/n (#5955)3930549 resolver: replace resolver.Target.Endpoint field with Endpoint() method (#5852)894816c grpclb: rename grpclbstate package back to state (#5962)e5a0237 encoding: fix duplicate compressor names (#5958)4adb2a7 xds/resolver: cleanup tests to use real xDS client 2/n (#5952)52a8392 gcp/observability: update method name validation (#5951)TestSimpleQueries, TestsJoinOps, TestJoinPlanning, TestColumnAliases, TestDerivedTableOuterScopeVisibility, TestAmbiguousColumnResolution, TestReadOnlyVersionedQueries with the new name resolution strategy.
Many of the query plans are slightly different but mostly equivalent. Join rearrangements and un-nesting in particular are better after this change, because I needed the transform logic work for both. There are a variety of other bugs the slight plan differences exposed that are fixed now.
This does not fix every set of enginetests, there is still a lot to do. But I'm locking in compatibility for most of the core tests to prevent backsliding.
The next follow-up is probably replacing the old name resolution. I will need to figure out if triggers, procs, prepared statements need any sort of special treatment.useSelectExpressionLiteral seems to behave differently.VARBINARY, including CHAR, which makes it look like binary data was sent and not a char string, like the caller indicated.
This change stops converting sqltypes.Char bind var type info into VARBINARY, which enables go-mysql-server to see that a char string was passed. Without this type information, go-mysql-server doesn't seem able to differentiate between legitimate binary data sent by the client versus a character string. Since these types need to be handled differently, and we can't assume that all VARBINARY types can be converted into strings, it seems like we need to respect the SQL type the caller indicated for the bind var and pass that up to integrators.
It seems like this change could be helpful for other SQL types, too, but I wanted to start with just sqltypes.Char to see if this approach causes any other issues.
Fixes: https://github.com/dolthub/go-mysql-server/issues/1855
Related go-mysql-server PR: https://github.com/dolthub/go-mysql-server/pull/1919
Related Dolt PR: https://github.com/dolthub/dolt/pull/6441
cast function calls
When formatting a parsed cast node back into a SQL string, we were using the form CAST(<arg>, <arg>), but that won't roundtrip back to MySQL. It needs to be either CONVERT(<arg>, <arg>) or CAST(<arg> as <arg).AS OF, FROM .. TO, BETWEEN), this PR also implements support for the 2 extensions added by SQLServer, ALL and CONTAINED IN.
https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16
Additionally, VERSION is now a synonym for SYSTEM_TIME, a Dolt pseudo-extension.ANSI_QUOTES parsing mode
The ANSI_QUOTES SQL mode changes the behavior of the double quote character. By default in MySQL, ANSI_QUOTES is not enabled and the double quote character is used to quote string literals. When ANSI_QUOTES is enabled, the double quote character may only quote identifiers. The ANSI_QUOTES mode does not change the behavior for backtick quote chars (they always quote identifiers) or single quote chars (they always quote string literals).
MySQL Reference Docs for ANSI_QUOTES
Related to: https://github.com/dolthub/dolt/issues/6305 (This is the first step towards supporting ANSI_QUOTES mode in Dolt/GMS)
GMS PR https://github.com/dolthub/go-mysql-server/pull/1896 stubs out the new Handler interface function, but actually using ANSI_QUOTES mode in Dolt or GMS won't be possible until a few more changes to Dolt/GMS.mysql> select "a" 'b' "c";
+-----+
| a |
+-----+
| abc |
+-----+
CREATE TABLE portion in their non-string form. This adds support for the string form.LATERAL syntax
syntax for: https://github.com/dolthub/dolt/issues/6194
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.
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.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.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).CREATE DATABASE to integrators
Remove AddDatabase functionality from Catalog and DatabaseProvider. Must have a DatabaseProvider with a static set of Databases to construct a Catalog.
New Databases can be created with CreateDatabase, which is now an integrator function.Distinct for example.
This PR updates AVG, FIRST, LAST, MAX, MIN and SUM to do this. COUNT(DISTINCT ...) is handled by a special expression node instead, and nothing has been changed in Count or CountDistinct. group_concat also seems to handle DISTINCT itself, and so I have not changed anything there. Json aggregation did not look immediately amenable to combining with DISTINCT, because the Update functions seemed to error when the child expression returned nil, so I have not changed them either.str_to_date
Closes #518
This PR implements the STR_TO_DATE MySQL function. In places where the spec is ambiguous, I'm attempting to match the behavior of MySQL version 8 from my manual testing. I need to implement a few more parsers and add more test cases to cover the expected behavior.
go test -cover github.com/dolthub/go-mysql-server/sql/parse/dateparse
ok github.com/dolthub/go-mysql-server/sql/parse/dateparse 0.163s coverage: 89.3% of statements
go-mysql-server is not compatible with insert query like:
INSERT INTO `users` (`id`, `deleted`) VALUES (1, DEFAULT)
DEFAULT is just to specify deleted columns should use column default value explicitly.
The issue could be demostrated using below code:
package main
import (
dbsql "database/sql"
sqle "github.com/dolthub/go-mysql-server"
"github.com/dolthub/go-mysql-server/auth"
"github.com/dolthub/go-mysql-server/memory"
"github.com/dolthub/go-mysql-server/server"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := dbsql.Open("mysql", "root:@tcp(127.0.0.1:3307)/test")
defer db.Close()
query := `CREATE TABLE users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
deleted tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4`
stmt, _ := db.Prepare(query)
stmt.Exec()
_, err := db.Exec("INSERT INTO `users` (`id`, `deleted`) VALUES (1, DEFAULT)")
if err != nil {
panic(err.Error())
}
stmtOut, _ := db.Prepare("SELECT `deleted` FROM `users` WHERE `id` = 1")
defer stmtOut.Close()
deleted := true
err = stmtOut.QueryRow().Scan(&deleted)
if err != nil {
panic(err.Error())
}
if deleted == true {
panic("Wrong deleted value")
}
}
var engine *sqle.Engine
func init() {
engine = sqle.NewDefault()
db := memory.NewDatabase("test")
engine.AddDatabase(db)
config := server.Config{
Protocol: "tcp",
Address: "localhost:3307",
Auth: auth.NewNativeSingle("root", "", auth.AllPermissions),
}
s, _ := server.NewDefaultServer(config, engine)
go s.Start()
}
`db.Exec("INSERT INTO `users` (`id`, `deleted`) VALUES (1, DEFAULT)")` will result in error:
Error 1105: plan is not resolved because of node '*plan.Values'
DEFAULT in insert query to implicit.UPDATE test SET col = 2 WHERE col = 1; would fail for ENUM and SET types. For the FILTER node, we only consider the values for comparison, but the aforementioned types can match 'horse' with the number 50, so we have to consider the column type.
Now this PR only affects ENUM and SET, although it's probably correct that all types should be used, with direct literal comparisons being used only when no other type can be derived from a column or variable. However, either MySQL has context-aware comparisons (my guess), or our comparison logic is wrong for other types, so I'm leaving those alone for now.DatabaseProvider abstraction, allowing integrators to add their own logic to database resolutionindexed_col = ... OR different_indexed_col = ....Script tests that work over the server, but we can do that at some later point.CREATE TABLE mytable(pk int) AS SELECT.... That is tables with their own schemas are not interpreted due to parsing limitations.value and status keywords
needed two features for mlflow:
alter table t modify value float(53) not null)alter table a drop check status)sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1105, "syntax error at position 205 near 'PRIMARY'")
[SQL:
CREATE TABLE experiments (
experiment_id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
artifact_location VARCHAR(256),
lifecycle_stage VARCHAR(32),
CONSTRAINT experiment_pk PRIMARY KEY (experiment_id),
CONSTRAINT experiments_lifecycle_stage CHECK (lifecycle_stage IN ('active', 'deleted')),
UNIQUE (name)
)
auto increment ddl supportSTR_TO_DATE
This is a development release, including several new features and performance improvements.
window/window.go
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: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.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.netutil.ConnWithTimeouts in pollForClosedConnectionREPLACE 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
/proc/sys/kernel/osrelease, then it'll be unlikely to be able to access any other /proc files such as /proc/net/tcp and /proc/net/tcp6.
For a more specific check for Android, one can check if the getprop command exists in the PATH environment as getprop is an Android specific command to check the build properties for the system.GLOBAL/SESSION or Dynamic information for them, but this is like 98% of the variables.
Now to hook them up and replace our current variable handling.sql.Sessions are created, and it needs to modify the *sql.Context prior to query execution. This MR adds two interfaces:
driver.SessionBuilder
driver.ContextBuilder
When a driver is created, with driver.New, if the provider implements either of these interfaces, then the provider's implementation will be used for creating sessions/contexts. Otherwise, a default implementation is used. The Dolt provider can then implement those two interfaces and do the necessary work to setup the session/context:doltSession.AddDB
doltDatabase.SetRoot
sqle.RegisterSchemaFragments
Prior to adding SessionBuilder, attempting to execute a query would panic, when Dolt attempts to coerce the session into a Dolt session. Prior to adding ContextBuilder, some features would not work - for example, queries had no access to uncommitted changes.
For reference, this is the Dolt driver implementation I'm working on.ERDbCreateExists(1007) when trying to create a database that already existsdatabase/sql/driver.Driver. With this driver, sqle.Engines can be queried via database/sql.
Also closes #361This is a development release. It contains many features and performance improvements added since the last release.
CREATE TABLE x.table(pk int) when the mysql client is not set to x.type JSONValue interface{}
JSON_ARRAYAGG and JSON_OBJECTAGG.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.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.SHOW GRANTS handling:
> dolt sql -q "show grants for current_user"
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
CREATE DATABASE
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 context that was assumed to be non-nil elsewhere, so this fixes those issues./*!40101 SET NAMES utf8 */
MAX(a as b) illegal, allowed before.FLOAT(prec), which will help with pandas table creation.CREATE PROCEDURE
SHOW PROCEDURE STATUS
SHOW FUNCTION STATUS
DROP PROCEDURE
CALL
SHOW...CODE statements are being skipped for now, as they're relatively unused compared to the above statements. Additionally, the CREATE statements do not have a fully fleshed-out routine body, as we currently do not parse all valid statements anyway.