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 dac7262c9b7904b931f9792ac8ce3eb52f6f9ccf from PR https://github.com/dolthub/go-mysql-server/pull/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: https://github.com/dolthub/dolt/issues/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: https://github.com/dolthub/dolt/issues/5735 Related Dolt change: https://github.com/dolthub/dolt/pull/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: https://github.com/dolthub/dolt/issues/3081
• 1710: Detect invalid uses of * and window functions in queries. This PR is the GMS side of the fix for https://github.com/dolthub/dolt/issues/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 https://github.com/dolthub/dolt/issues/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: https://github.com/dolthub/dolt/issues/5711
• 1704: fix group by over empty result sets fix for: https://github.com/dolthub/dolt/issues/5683
• 1702: sql/plan: track column renames on alter/modify column fixes https://github.com/dolthub/dolt/issues/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: https://github.com/dolthub/dolt/issues/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: https://github.com/dolthub/dolt/issues/5631
• 1695: Fix value out of range bug re: https://github.com/dolthub/dolt/issues/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: https://github.com/dolthub/dolt/issues/5570 related: https://github.com/dolthub/vitess/pull/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: https://github.com/dolthub/dolt/issues/5568
• 1690: Use ordinals to force stable TopN heap sort Re: https://github.com/dolthub/dolt/issues/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: https://github.com/dolthub/dolt/issues/5633.
• 1688: Support for recursive CTEs with no union Ex:

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

  Re: https://github.com/dolthub/dolt/issues/5657
• 1687: Fix left merge join bug Re: https://github.com/dolthub/dolt/issues/5652
• 1686: Fix issue where special characters within JSON were encoded.
• 1684: Fix hoisted scope renaming error Disambiguating a hoisted scope requires renaming all references. We were missing intra-scope filter renames. re: https://github.com/dolthub/dolt/issues/5654
• 1683: Fix distinctness/sort enforcing The original bug: https://github.com/dolthub/dolt/issues/5651 duplicates a RIGHT_SEMI_LOOKUP_JOIN row because we were distincting right full row rather than the subset of join attributes. This PR adds some more tests around ordering and sort enforcing in the memo. The overview is that DISTINCT is weird because it is something in-between a property of a relational expression and the property of a relational group. It is an enforcer that we can implement as an ORDERED_DISTINCT or ditch altogether when child nodes provide supportive sort orders. We could imagine bifurcating the memo into buckets, with expression groups sectioned into groups based on sort orders, and costing considering the cardinality of children plus conditional sort enforcers. More work needed to think through how PG and CRDB do this generally.
• 1682: Rework how we create JSON strings to match MySQL. Fixes https://github.com/dolthub/dolt/issues/4499
• 1680: Fix partial join hints ResolvedTable and TableAlias must carry comments if we want to hint partial joins created by unnesting subqueries.
• 1679: Small fix for a join-related regression in Dolt
• 1677: fix for duplicate hash tuple in This is the old explain:

  tmp> explain select * from t where i in (1, 1);
  +---------------------------------------+
  | plan                                  |
  +---------------------------------------+
  | Filter                                |
  |  ├─ (t.i HASH IN (1, 1))              |
  |  └─ IndexedTableAccess(t)             |
  |      ├─ index: [t.i]                  |
  |      ├─ filters: [{[1, 1]}, {[1, 1]}] |
  |      └─ columns: [i]                  |
  +---------------------------------------+
  6 rows in set (0.00 sec)
  

  We just had duplicate Ranges inside the filters, which cause multiple lookups for the same value. A side-effect of this change is that the filters are now sorted in the explain query, so that's nice. Example: https://github.com/dolthub/go-mysql-server/pull/1677/files#diff-eae6109880e132a9197c2b536f62e9572fc34d8e7c65e957dd37f48657790a94L2392-R2392
• 1676: Added support for ALTER TABLE ... COLLATE Fixes https://github.com/dolthub/dolt/issues/5623 Builds upon https://github.com/dolthub/vitess/pull/229
• 1675: SET multiple system variables including 'NAMES' and 'CHARSET'
• 1670: Adding a new GH action to label incoming issues and PRs from customers This same workflow has been running in the dolt repo for a couple weeks now. This PR rolls it out to go-mysql-server. The intent is to label issues and pull requests from customers to help us see them more easily in all the other issues and pull requests we create.
• 1669: clean up hoisting Variety of refactors related to hoisting subqueries and unnesting scopes.
  • remove SingleSelectRel
  • empty join expansion
  • limits + filters should be tested in memo now
  • fix to anti join correctness for empty right-hand rel
• 1667: Allow logging base64-encoded queries, instead of replacing bad chars.
• 1666: join operator hints Join order (JOIN_ORDER(a,b)) and operator hints (LOOKUP_JOIN(a,b)) play nice together when there is a plan that satisfies all hints. If order and operator hints are are incompatible, no hints are applied. Example:

  > explain
  select /*+ JOIN_ORDER(a,b,c,d) LOOKUP_JOIN(d,c) MERGE_JOIN(b,a) HASH_JOIN(c,a)*/ 1
  from xy a
  join uv b on a.x = b.u
  join xy c on a.x = c.x
  join uv d on d.u = c.x;
  QueryProcess
  └─ Project
  ├─ columns: [1 (tinyint)]
  └─ HashJoin
  ├─ Eq
  │   ├─ a.x:0!null
  │   └─ c.x:2!null
  ├─ MergeJoin
  │   ├─ cmp: Eq
  │   │   ├─ a.x:0!null
  │   │   └─ b.u:1!null
  │   ├─ TableAlias(a)
  │   │   └─ IndexedTableAccess(xy)
  │   │       ├─ index: [xy.x]
  │   │       ├─ static: [{[NULL, ∞)}]
  │   │       └─ columns: [x]
  │   └─ TableAlias(b)
  │       └─ IndexedTableAccess(uv)
  │           ├─ index: [uv.u]
  │           ├─ static: [{[NULL, ∞)}]
  │           └─ columns: [u]
  └─ HashLookup
  ├─ source: TUPLE(a.x:0!null)
  ├─ target: TUPLE(c.x:0!null)
  └─ CachedResults
  └─ LookupJoin
  ├─ Eq
  │   ├─ d.u:3!null
  │   └─ c.x:2!null
  ├─ TableAlias(c)
  │   └─ Table
  │       ├─ name: xy
  │       └─ columns: [x]
  └─ TableAlias(d)
  └─ IndexedTableAccess(uv)
  ├─ index: [uv.u]
  └─ columns: [u]
  

• 1665: Fix filter hoisting issue Before hoisting a filter condition inside a subquery, check if the condition is using any of the subquery tables. This also includes a minor renaming change: when hoisting a table and there's a possibility of a name collision, we now add an unambiguous table alias. Fixes: https://github.com/dolthub/dolt/issues/5342
• 1663: Return column size in datetime type During testing go-mysql-server with Trino and I have noticed that it does work when I have either timestamp or datetime. I have asked them https://github.com/trinodb/trino/issues/16575 whether it's ok for them to implement default column size like they did for Phoenix connector, but they did not want to add it. I still think it should be fine without column size, since most of the clients (except the ones which are written in java) accept date time without column size. In order to be fully compatible about it for now I have added 0 as column size for timestamp and datetime
• 1661: AlterIndex completely resolves column defaults https://github.com/dolthub/dolt/issues/5478
• 1660: Added a permissive collation coercibility model This PR adds support for collation coercion by granting every expression, node, and type the ability to report their own coercibility. In addition, we're extremely permissible now, and do not report any coercibility errors, which deviates from MySQL. This still attempts to adhere to MySQL's coercibility rules as much as possible though.
• 1659: When creating Recursive CTE schema, promote types. Fixes https://github.com/dolthub/dolt/issues/5549
• 1656: De Morgan's Laws for NOT filter hoisting Hoisting filters in NOT expressions is subject to violating De Morgan's Laws. The current fix makes sure that when we pull filters from a subquery into a parent scope, we will include the hoisted filters in the NOT conjunction. So:

  select * from xy where not exists (select * from uv where x = 1)
  =>
  select * from xy where not (exists (select * from uv) and x = 1)
  

  And in the more general case:

  select * from xy where not exists (select * from uv where not exists (select * from uv where x = 1))
  =>
  select * from xy where not (exists (select * from uv) and not (exists (select * from uv) and x = 1))
  

  Further simplification would be helpful.
• 1654: Clearing the session's transactionDb field at the end of the DropDB iterator Dolt PR: https://github.com/dolthub/dolt/pull/5556 Dolt needs to start clearing dbState for all sessions when a database is dropped, otherwise incorrect data can be returned. As part of clearing that state in all sessions, it means that after the DropDB node has run its logic, the previous TransactionCommittingNode would try to run logic that relied on the dropped database state still being there. This change clears the transactionDb field of a session after we've dropped that db so that the transaction commit logic doesn't try to run.
• 1652: lookup uservars when parsing prepareds fix for: https://github.com/dolthub/dolt/issues/5530 companion: https://github.com/dolthub/vitess/pull/226
• 1651: types: fix JSON NULL comparisons
• 1650: Replace false filter with an empty table with the schema of the filter. Fixes https://github.com/dolthub/dolt/issues/5522
• 1649: fix batched insert ... on duplicate update ... We create new table edit accumulators for inserter and updater fields for the insert node, which messes up batched inserts as certain edits are not visible between the two. The fix is two do what dolt does, and share edit accumulators. fix for: https://github.com/dolthub/dolt/issues/5433 Context: https://github.com/dolthub/go-mysql-server/pull/1642#discussion_r1131219965
• 1648: recursively hoist out of scope filters from subqueries Subqueries have all filters with GET_FIELD attributes referencing outer scopes tables hoisted into the parent scope. Adds tests for recursively applying this rule. Small regression for unnesting correlated scopes with no filters, but those should be much easier to unnest now that they are decorrelated. Example:

  select * from xy where exists (select * from uv where x = 1)
  -- =>
  select * from xy where x = 1 and exists (select * from uv)
  

• 1647: prevent spatial index for fk MySQL won't pick SPATIAL indexes as the underlying index for foreign keys; it throws a "Missing index constraint for ..." error. I couldn't find any docs specifically saying this. Based off how SPATIAL indexes work for our stuff, it seems like it would work just fine for us. However since MySQL doesn't support it, we won't either.
• 1646: Streamlined FK RESTRICT processing One of the strategies I mentioned in https://github.com/dolthub/dolt/issues/5508 was foregoing loading child editors when all referential actions for a foreign key are RESTRICT. Turns out that all we needed to do is to not add the updater (which is housed inside of the editor) to the chain. I was going to implement something a bit more complex, as we should be able to ignore all updaters that aren't actually used using runtime analysis, however I backtracked those changes as it was getting quite complex, and the added complexity did not seem worth it at all. There are no additional tests, as this is strictly a performance improvement. Foreign keys are one of our most well-tested features, so I'm confident that it is correct with all of the tests passing.
• 1645: SHOW PROCESSLIST improvements. Show the correct database and client address of the connection. Include idle and connecting connections in the list, instead of just connections which are currently running queries. Allow KILL [ID] to kill connections which are currently connecting.
• 1643: shift expandStars getFields by scopeLen Select * was being converted into get fields without an outer scope offset, preventing certain unnesting transformations.
• 1642: add tests for on duplicate key update for keyless tables small change to in memory table edit accumulators to only remove rows once from adds and deletes for Insert() and Delete() companion pr: https://github.com/dolthub/dolt/pull/5514
• 1641: make st_within use spatial indexes
• 1639: Handle Quotes in Default Column Values See Issue: https://github.com/dolthub/dolt/issues/5411 Both Default values and check constraints are impacted by the fact that we write strings to storage without escaping the single quote character or backslash appropriately. This change encodes both and adds tests to verify correct behavior. Tests were added for procedures, views, and triggers, they they weren't impacted by this particular defect.
• 1638: Fixing incorrect size for SOURCE_HOST member of SHOW REPLICA STATUS
• 1637: Workaround for Issue 5482 This is a workaround for https://github.com/dolthub/dolt/issues/5482. This isn't a true fix as the behavior is wrong, but will at least unblock the user. I'm currently working on proper collation coercion handling, so this will be replaced in relatively short order.
• 1635: Better error message for cannot drop index
• 1634: add support for EXTRACT fix for: https://github.com/dolthub/dolt/issues/3006 We don't support 0 for year, month, day, but MySQL does... I didn't fix here, because it seems to be a problem stemming from the time library, and the behavior is consistent with our existing DAY(), MONTH() and YEAR() functions.
• 1631: Nil operand in outer join smarter about identifying nil's source rel We falsely attributed a nil operand to the left relation, early triggering the left outer join nil return path. Rather than iterate the row from left to right, use the comparison expression to directly evaluate for whether left comparison expr is nil.
• 1630: Fix update resolve table bug
• 1629: add support for ! and fix NOT operator not working for UPDATE trigger
  • ! was not supported for IF clause in TRIGGER statements
  • NOT clause was not working for UPDATE TRIGGER statements
• 1627: Made signal statements work with user vars Fixes https://github.com/dolthub/dolt/issues/5452
• 1625: return error for attempting to drop view with drop table statement
  • using DROP TABLE to drop view returns error
  • dropping non existent table with DROP TABLE IF EXISTS creates note warning.
• 1624: Update logger field when a session's database is changed We use a connectionDb field to log the session's current database, but we don't always consistently update that logger field in every place where we set the session's current db. This change moves that logger field update directly into BaseSession.SetCurrentDatabase(string) to ensure it always stays in sync.
• 1623: Fixed Issue #1621 When using the example server, we had primary key indexes disabled for the in-memory database, so this just enables them as that is what people would expect.
• 1622: fix / and div operations on value of decimal type column The cases fixed are:
  • the scale of decimal type column is bigger than the value stored. For example, the decimal column type is DECIMAL(25,10) and the value stored is 4990. This value is evaluated with 0 scale, whereas this should be evaluated as value with scale of 10.
  • the IntPart() function of decimal package returns 0 as undefined value for out of range values. This causes it hard to differentiate between cases where the final result is valid value of 0 and the final result is out of range value.
• 1620: expose some parts of show create table statement for dolt usage
• 1619: Memo includes costs and stars best plan Debugger memo print includes physical expression incremental processing costs, and stars the group best. The value corresponds to the incremental processing delta of executing an operator compared to a sequential processing of the inputs. The total for a group will be the group costs plus child group costs (recursively). Example:

  memo:
  ├── G1: (tableAlias: r 11444774.0)*
  ├── G2: (tableAlias: i 161.0)*
  ├── G3: (mergeJoin 1 2 114447.7) (mergeJoin 2 1 1.6) (hashJoin 1 2 114930.7) (hashJoin 2 1 34334323.6) (lookupJoin 1 2 23003834.7) (lookupJoin 2 1 -11444450.2)* (innerJoin 2 1 1861034699.1) (innerJoin 1 2 1861034699.1)
  ├── G4: (tableAlias: c 12293.0)*
  ├── G5: (hashJoin 3 4 36879.2) (hashJoin 2 12 4058.3) (hashJoin 12 2 496.5) (hashJoin 4 3 176.1) (lookupJoin 3 4 -12257.4)* (lookupJoin 12 2 2557.0) (innerJoin 4 3 219885.1) (innerJoin 12 2 219885.1) (innerJoin 2 12 219885.1) (innerJoin 3 4 219885.1)
  ├── G6: (tableAlias: pm 646.0)*
  ├── G7: (hashJoin 5 6 1938.0) (hashJoin 2 15 22.9) (hashJoin 15 2 483.1) (hashJoin 4 14 128.2) (hashJoin 14 4 36879.0) (hashJoin 6 5 11.8) (lookupJoin 5 6 -642.4) (lookupJoin 15 2 -146.7) (lookupJoin 14 4 -12289.4)* (innerJoin 6 5 1154.5) (innerJoin 14 4 21987.6) (innerJoin 4 14 21987.6) (innerJoin 15 2 1154.5) (innerJoin 2 15 1154.5) (innerJoin 5 6 1154.5)
  ├── G8: (tableAlias: trf 39874374.0)*
  ├── G9: (hashJoin 7 8 119623122.0) (hashJoin 2 22 3.7) (hashJoin 22 2 483.0) (hashJoin 4 21 123.5) (hashJoin 21 4 36879.0) (hashJoin 6 19 7.0) (hashJoin 19 6 1938.0) (hashJoin 8 7 398744.3) (lookupJoin 7 8 -39874373.6) (lookupJoin 22 2 -159.6) (lookupJoin 21 4 -12292.6) (lookupJoin 19 6 -645.6)* (innerJoin 8 7 7132368.2) (innerJoin 19 6 114.6) (innerJoin 6 19 114.6) (innerJoin 21 4 2197.9) (innerJoin 4 21 2197.9) (innerJoin 22 2 114.6) (innerJoin 2 22 114.6) (innerJoin 7 8 7132368.2)
  ├── G10: (tableAlias: nt 8735.0)*
  ├── G11: (hashJoin 9 10 26205.0) (hashJoin 2 30 1.8) (hashJoin 30 2 483.0) (hashJoin 4 29 123.0) (hashJoin 29 4 36879.0) (hashJoin 6 27 6.5) (hashJoin 27 6 1938.0) (hashJoin 7 23 2882.6) (hashJoin 23 7 10.1) (hashJoin 10 9 87.4) (lookupJoin 9 10 -8735.0)* (lookupJoin 30 2 -160.8) (lookupJoin 29 4 -12293.0) (lookupJoin 27 6 -646.0) (innerJoin 10 9 155.2) (innerJoin 23 7 170.9) (innerJoin 7 23 170.9) (innerJoin 27 6 11.7) (innerJoin 6 27 11.7) (innerJoin 29 4 240.9) (innerJoin 4 29 240.9) (innerJoin 30 2 11.7) (innerJoin 2 30 11.7) (innerJoin 9 10 155.2)
  ├── G12: (mergeJoin 1 4 114447.7) (mergeJoin 4 1 122.9) (hashJoin 1 4 151326.7) (hashJoin 4 1 34334444.9) (lookupJoin 1 4 22991702.7) (lookupJoin 4 1 -11420052.8)* (innerJoin 4 1 142097512848.8) (innerJoin 1 4 142097512848.8)
  ├── G13: (mergeJoin 1 6 114447.7) (mergeJoin 6 1 6.5) (hashJoin 1 6 116385.7) (hashJoin 6 1 34334328.5) (lookupJoin 1 6 23003349.7) (lookupJoin 6 1 -11443474.9)* (innerJoin 6 1 7467257243.0) (innerJoin 1 6 7467257243.0)
  ├── G14: (hashJoin 2 13 214.8) (hashJoin 13 2 483.7) (hashJoin 3 6 1938.2) (hashJoin 6 3 59.6) (lookupJoin 13 2 -18.2) (lookupJoin 3 6 -610.4)* (innerJoin 6 3 11554.1) (innerJoin 3 6 11554.1) (innerJoin 13 2 11554.1) (innerJoin 2 13 11554.1)
  ├── G15: (hashJoin 4 13 336.1) (hashJoin 13 4 36879.7) (hashJoin 12 6 1951.5) (hashJoin 6 12 4063.2) (lookupJoin 13 4 -12150.2)* (lookupJoin 12 6 2072.0) (innerJoin 6 12 882275.0) (innerJoin 12 6 882275.0) (innerJoin 13 4 882275.0) (innerJoin 4 13 882275.0)
  ├── G16: (mergeJoin 1 8 114447.7) (mergeJoin 8 1 398743.7) (hashJoin 1 8 119737569.7) (hashJoin 8 1 34733065.7) (lookupJoin 1 8 -16870378.3)* (lookupJoin 8 1 68702717.7) (innerJoin 8 1 460916730809689.8) (innerJoin 1 8 460916730809689.8)
  ├── G17: (hashJoin 2 16 3433433.8) (hashJoin 16 2 11927.8) (hashJoin 3 8 119623122.2) (hashJoin 8 3 398796.9) (lookupJoin 16 2 2300238.6) (lookupJoin 3 8 -39874338.4)* (innerJoin 8 3 713236914.2) (innerJoin 3 8 713236914.2) (innerJoin 16 2 186103469.0) (innerJoin 2 16 186103469.0)
  ├── G18: (hashJoin 4 16 3433555.1) (hashJoin 16 4 48323.8) (hashJoin 12 8 119623135.5) (hashJoin 8 12 402800.4) (lookupJoin 16 4 2288106.6) (lookupJoin 12 8 -39871656.0)* (innerJoin 8 12 54458518000.6) (innerJoin 12 8 54458518000.6) (innerJoin 16 4 14209751284.0) (innerJoin 4 16 14209751284.0)
  ├── G19: (hashJoin 2 18 407.3) (hashJoin 18 2 484.4) (hashJoin 4 17 128.2) (hashJoin 17 4 36879.0) (hashJoin 5 8 119623122.0) (hashJoin 8 5 398749.1) (lookupJoin 18 2 110.8) (lookupJoin 17 4 -12289.4)* (lookupJoin 5 8 -39874370.4) (innerJoin 8 5 71323690.5) (innerJoin 5 8 71323690.5) (innerJoin 17 4 21987.6) (innerJoin 4 17 21987.6) (innerJoin 18 2 21987.6) (innerJoin 2 18 21987.6)
  ├── G20: (hashJoin 6 16 3433438.7) (hashJoin 16 6 13382.8) (hashJoin 13 8 119623122.7) (hashJoin 8 13 398956.9) (lookupJoin 16 6 2299753.6) (lookupJoin 13 8 -39874231.2)* (innerJoin 8 13 2861807745.6) (innerJoin 13 8 2861807745.6) (innerJoin 16 6 746725723.4) (innerJoin 6 16 746725723.4)
  ├── G21: (hashJoin 2 20 22.9) (hashJoin 20 2 483.1) (hashJoin 6 17 11.8) (hashJoin 17 6 1938.0) (hashJoin 14 8 119623122.0) (hashJoin 8 14 398749.1) (lookupJoin 20 2 -146.7) (lookupJoin 17 6 -642.4)* (lookupJoin 14 8 -39874370.4) (innerJoin 8 14 71323690.5) (innerJoin 14 8 71323690.5) (innerJoin 17 6 1154.5) (innerJoin 6 17 1154.5) (innerJoin 20 2 1154.5) (innerJoin 2 20 1154.5)
  ├── G22: (hashJoin 4 20 144.2) (hashJoin 20 4 36879.1) (hashJoin 6 18 412.1) (hashJoin 18 6 1939.4) (hashJoin 15 8 119623122.1) (hashJoin 8 15 398765.1) (lookupJoin 20 4 -12278.7) (lookupJoin 18 6 -374.2) (lookupJoin 15 8 -39874359.7)* (innerJoin 8 15 286180773.7) (innerJoin 15 8 286180773.7) (innerJoin 18 6 88226.6) (innerJoin 6 18 88226.6) (innerJoin 20 4 88226.6) (innerJoin 4 20 88226.6)
  ├── G23: (mergeJoin 8 10 398743.7) (mergeJoin 10 8 87.4) (hashJoin 8 10 424948.7) (hashJoin 10 8 119623209.3) (lookupJoin 8 10 80178631.1) (lookupJoin 10 8 -39856807.9)* (innerJoin 10 8 351785683457.9) (innerJoin 8 10 351785683457.9)
  ├── G24: (hashJoin 1 23 117330.3) (hashJoin 23 1 34334331.6) (hashJoin 16 10 37649.8) (hashJoin 10 16 3433519.6) (lookupJoin 23 1 -11442842.7)* (lookupJoin 16 10 2292809.1) (innerJoin 10 16 10096980188.9) (innerJoin 16 10 10096980188.9) (innerJoin 23 1 11106678207.9) (innerJoin 1 23 11106678207.9)
  ├── G25: (hashJoin 2 24 289.9) (hashJoin 24 2 484.0) (hashJoin 3 23 2882.7) (hashJoin 23 3 62.7) (hashJoin 17 10 26205.0) (hashJoin 10 17 92.7) (lookupJoin 24 2 32.1) (lookupJoin 17 10 -8731.4)* (innerJoin 10 17 15623.4) (innerJoin 17 10 15623.4) (innerJoin 23 3 17185.8) (innerJoin 3 23 17185.8) (innerJoin 24 2 15623.4) (innerJoin 2 24 15623.4)
  ├── G26: (hashJoin 4 24 411.2) (hashJoin 24 4 36880.0) (hashJoin 12 23 2896.1) (hashJoin 23 12 4066.3) (hashJoin 18 10 26206.4) (hashJoin 10 18 493.0) (lookupJoin 24 4 -12099.9)* (lookupJoin 18 10 -8463.1) (innerJoin 10 18 1192983.6) (innerJoin 18 10 1192983.6) (innerJoin 23 12 1312282.1) (innerJoin 12 23 1312282.1) (innerJoin 24 4 1192983.6) (innerJoin 4 24 1192983.6)
  ├── G27: (hashJoin 2 26 30.4) (hashJoin 26 2 483.1) (hashJoin 4 25 123.5) (hashJoin 25 4 36879.0) (hashJoin 5 23 2882.6) (hashJoin 23 5 14.9) (hashJoin 19 10 26205.0) (hashJoin 10 19 87.9) (lookupJoin 26 2 -141.7) (lookupJoin 25 4 -12292.6) (lookupJoin 19 10 -8734.6)* (innerJoin 10 19 1561.4) (innerJoin 19 10 1561.4) (innerJoin 23 5 1717.7) (innerJoin 5 23 1717.7) (innerJoin 25 4 2417.7) (innerJoin 4 25 2417.7) (innerJoin 26 2 1561.4) (innerJoin 2 26 1561.4)
  ├── G28: (hashJoin 6 24 294.7) (hashJoin 24 6 1939.0) (hashJoin 13 23 2883.3) (hashJoin 23 13 222.8) (hashJoin 20 10 26205.1) (hashJoin 10 20 108.7) (lookupJoin 24 6 -452.9) (lookupJoin 20 10 -8720.7)* (innerJoin 10 20 62690.6) (innerJoin 20 10 62690.6) (innerJoin 23 13 68959.8) (innerJoin 13 23 68959.8) (innerJoin 24 6 62690.6) (innerJoin 6 24 62690.6)
  ├── G29: (hashJoin 2 28 4.0) (hashJoin 28 2 483.0) (hashJoin 6 25 7.0) (hashJoin 25 6 1938.0) (hashJoin 14 23 2882.6) (hashJoin 23 14 14.9) (hashJoin 21 10 26205.0) (hashJoin 10 21 87.9) (lookupJoin 28 2 -159.4) (lookupJoin 25 6 -645.6) (lookupJoin 21 10 -8734.6)* (innerJoin 10 21 1561.4) (innerJoin 21 10 1561.4) (innerJoin 23 14 1717.7) (innerJoin 14 23 1717.7) (innerJoin 25 6 126.1) (innerJoin 6 25 126.1) (innerJoin 28 2 126.1) (innerJoin 2 28 126.1)
  └── G30: (hashJoin 4 28 125.3) (hashJoin 28 4 36879.0) (hashJoin 6 26 35.3) (hashJoin 26 6 1938.1) (hashJoin 15 23 2882.6) (hashJoin 23 15 30.9) (hashJoin 22 10 26205.0) (hashJoin 10 22 89.5) (lookupJoin 28 4 -12291.4) (lookupJoin 26 6 -626.7) (lookupJoin 22 10 -8733.6)* (innerJoin 10 22 6268.2) (innerJoin 22 10 6268.2) (innerJoin 23 15 6895.1) (innerJoin 15 23 6895.1) (innerJoin 26 6 6268.2) (innerJoin 6 26 6268.2) (innerJoin 28 4 9704.0) (innerJoin 4 28 9704.0)
  

• 1617: Adding privilege checks for binlog commands Added support for binlog replication privilege checks, tests to assert dynamic privileges are only used at a global scope, and opChecker support for testing dynamic privileges.
• 1615: update drop column logic to drop constraints first Updates the ordering of the drop column logic to ensure that constraints always get checked for references and dropped when columns are dropped.
• 1614: Bump golang.org/x/text from 0.3.7 to 0.3.8 Bumps golang.org/x/text from 0.3.7 to 0.3.8.
  Commits

  • 434eadc language: reject excessively large Accept-Language strings
  • 23407e7 go.mod: ignore cyclic dependency for tagging
  • b18d3dd secure/precis: replace bytes.Compare with bytes.Equal
  • 795e854 all: replace io/ioutil with io and os package
  • b0ca10f internal/language: bump script types to uint16 and update registry
  • ba9b0e1 go.mod: update x/tools to HEAD
  • d03b418 A+C: delete AUTHORS and CONTRIBUTORS
  • b4bca84 language/display: fix Tag method comment
  • ea49e3e go.mod: update x/tools to HEAD
  • 78819d0 go.mod: update to golang.org/x/text v0.1.10
  • Additional commits viewable in compare view
  
  [](https://docs.github.com/en/github/managing-security-vulnerabilities/about-dependabot-security-updates#about-compatibility-scores) Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting `@dependabot rebase`. [//]: # (dependabot-automerge-start) [//]: # (dependabot-automerge-end) ---
  Dependabot commands and options

  
  You can trigger Dependabot actions by commenting on this PR: - `@dependabot rebase` will rebase this PR - `@dependabot recreate` will recreate this PR, overwriting any edits that have been made to it - `@dependabot merge` will merge this PR after your CI passes on it - `@dependabot squash and merge` will squash and merge this PR after your CI passes on it - `@dependabot cancel merge` will cancel a previously requested merge and block automerging - `@dependabot reopen` will reopen this PR if it is closed - `@dependabot close` will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually - `@dependabot ignore this major version` will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself) - `@dependabot ignore this minor version` will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself) - `@dependabot ignore this dependency` will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself) - `@dependabot use these labels` will set the current labels as the default for future PRs for this repo and language - `@dependabot use these reviewers` will set the current reviewers as the default for future PRs for this repo and language - `@dependabot use these assignees` will set the current assignees as the default for future PRs for this repo and language - `@dependabot use this milestone` will set the current milestone as the default for future PRs for this repo and language You can disable automated security fix PRs for this repo from the [Security Alerts page](https://github.com/dolthub/go-mysql-server/network/alerts).
• 1611: Added support for dynamic privileges
• 1610: Project reports correct outputCols
• 1609: Added missing analyzer rule for block expressions Fixes https://github.com/dolthub/dolt/issues/5390
• 1608: Delete Join Support Adds support for multi-table deletes. Dolt CI Tests: https://github.com/dolthub/dolt/pull/5406

  Remaining Work:

  Triggers: Trigger execution needs some refactoring in order to support: 1) updating GetField indexes in the trigger logic to account for a larger row coming into the TriggerExecutor when multiple tables are joined together, and 2) running triggers for multiple tables on the same child row from the wrapped iterator. Currently, if triggers are needed for a DELETE JOIN statement, the query will return an error message about the lack of support (engine tests assert this, too). Triggers are not currently supported for UPDATE JOIN statements either (https://github.com/dolthub/dolt/issues/5400); we should look at both of these together in a follow up PR, since TriggerExecutor will need the same changes to support both of these.
• 1605: Fix permissions on all Go files Some of the .go files don't have the usual 0644 permissions. Fix the permissions of all .go files: git ls-files | grep '.go$' | xargs chmod 0644
• 1604: memory: use map lookup for rename index Slight improvement in the memory/ db implementation, instead of iterating over all the indexes, use a map lookup.
• 1603: driver/: implement last insert id for ok result Fixes #1602
• 1601: allow safe drops when column is referenced in check constraint MySQL allows columns which are referenced in check constraints to be dropped if they are the only column referenced in the constraint. This change updates dolt to match this behavior. fixes: https://github.com/dolthub/dolt/issues/3147
• 1600: enginetest: Small cleanup for DROP DATABASE and DROP TABLE tests to be a little more hygienic in their use of state across the tests.
• 1598: refactor(driver): adjust comments and interfaces to clarify behavior Improves the readability of the code w.r.t what purposes the Provider handles and points to the fact that the Provider can implement ContextBuilder. Adds a new DSN function to access the original dsn on the Connector. Fixes #1596
• 1597: Restore driver tests This PR restores and updates the driver package's tests and example. I included tests and an example when I submitted my original PR but since then they were removed.
• 1595: Attempt to identify and fix alias ambiguity issues with subqueries. Fixes https://github.com/dolthub/dolt/issues/5138 The new file sql/analyzer/aliases_disambiguate.go has a header with documentation on how the new rule works.
• 1594: Fix join hints for scope unnesting Fixes join hints for recent subquery unnesting and semiJoin -> innerJoin rules, and fixes invalid testing setup issues. Tracking Dolt bump: https://github.com/dolthub/dolt/pull/5361
• 1593: driver/conn: implement conn, execer, and queryer interfaces Implement more of the driver.Conn interfaces:
  • driver.Conn
  • driver.Execer
  • driver.ExecerContext
  • driver.Queryer
  • driver.QueryerContext
• 1592: name unnamed values properly Fixes #1591 Signed-off-by: Dave Henderson dhenderson@gmail.com
• 1589: Separate cardinality from costing, convert semi to inner join This started out as a semiJoin optimization: semiJoin(xy ab) => project(ab) -> innerJoin(xy, distinct(ab)). Adding project and distinct to memo groups was hard because we did not differentiate between operator cost (estimation of compute time) and cardinality (number of rows returned by a relational expression). Refactoring costing to distinguish the two caused a lot of bugs. As a result, I converted many of the join tests into a format that will run each with a biased coster. All of those join op tests should be run with every join operator possible in its expression tree. The biggest effect is that we now choose merge and hash joins a lot more often for small tables, which are most of our preexisting tests. We also change a lot of integration test plans. The increase in testing makes me less worried about correctness and more worried about perf. But the current changes are necessary to bring us closer to histogram-based costing. I would rathe do this now, when we have more leeway for increasing perf compared to a month ago. There is still room for improvements organizing costing, merge join, and join tests as a whole.
• 1588: partially validate and create underlying indexes even if foreign_key_checks=0 When FOREIGN_KEY_CHECKS is disabled, we should still create the underlying secondary index or assign the primary key indexes. Currently, we don't do that, but the moment any query is run with FOREIGN_KEY_CHECKS enabled, we create the indexes and assign any index references. Additionally, we need to do some of the validation according to MySQL Docs Related: https://github.com/dolthub/dolt/issues/5316
• 1587: Changed how empty set of projected columns is handled Started with tightening the semantics of the ProjectedTable interface as it relates to an empty projection (nil v. empty slice). Then made changes to printing of ResolvedTable and IndexedTableAccess. This revealed problems in the prune columns rule when all columns were being pruned. Fixed those, which had been masking other bugs, where we hadn't been pruning when we could have been. This was in turn caused by other bugs in the prune rule dealing with case sensitivity. We should now be able to prune more columns than before, and we can see an empty column projection in plans.
• 1586: Unnest IN_SUBQUERY GROUP_BY into partial join Resolving group by expressions used to happen in two phases, and now happens in one so that we can detect cacheable groupby subqueries at applyJoin time. Fix a miscellaneous bug in RightSemiLookupJoin. The right left node has to return distinct results, otherwise we will duplicate results. Dolt tests pass locally.
• 1584: not perform optimization_rule on like clause for utf8mb3 charset optimization rule uses ÿ last character in the charset utf8mb4, which does not work for utf8mb3 charset values.
• 1583: literal.String() returns hex string for binary type data DataGrip sets the character_set_results system variable to utf8, which raw binary strings cannot be encoded into. MySQL returns binary type strings in hex format.
• 1581: adding setter function for queryTime in sqlContext adds setter function for the queryTime field in sqlContext so that the correct starting time for queries can be updated related: https://github.com/dolthub/dolt/pull/5291 fixes: https://github.com/dolthub/dolt/issues/5241
• 1580: Fix panic by rewriting how we move filter conditions up. Attempt #2, this fix properly includes the new filter between topJoin and its parent. Fixes dolthub/dolt#5214
• 1576: New analyzer rule to replace count(*) with count(1) wherever it is found This fixes https://github.com/dolthub/dolt/issues/5171
• 1574: Subquery caching correctness Rerunning cacheSubqueries recursively on subquery expressions led to an incorrect cacheability labelling. The rule should only be run once on a top-level tree.
• 1573: add support for SHOW GLOBAL VARIABLES
• 1570: Fix panic by rewriting how we move filter conditions up. Fix panic by rewriting how we move filter conditions up. Fixes https://github.com/dolthub/dolt/issues/5214
• 1569: release all locks on session end Bats test for this PR is in https://github.com/dolthub/dolt/pull/5257
• 1568: Merge join selects do not filter left join A join filter that evaluates to false or nil can still return rows in certain cases. One of the cases we evaluated incorrectly were LEFT_MERGE_JOINs with multiple filters, which should always return the left row even if a filter returns false. MERGE_JOIN's first filter is specially selected because its Left/Right expressions reference attributes for two tables that will arrive sorted given the indexes we chose to read. We use that first filter for the merge comparison direction.
• 1566: set utf8mb3_general_ci as information_schema database's collation DataGrip expects utf8mb3_general_ci as information_schema's default collation for MySQL.
• 1564: fixes handling of null set values Null set values were being ignored completely. This PR fixes this issue and addresses the display issue which shows an extra comma when null set values are present in multi-member set values. fixes: https://github.com/dolthub/dolt/issues/4966
• 1563: Fix unsafe table pointer access in applyJoin
• 1562: Varchars don't error for failed IN cast to smaller varchar This does not address conversions between strings and non-string types, and type casting for index lookups, which look somewhat more complex in mysql.
• 1561: Small tweaks for binlog replication A few more small changes to support binlog replication:
  • Increasing the length of the Last_Error field returned by SHOW REPLICA STATUS so that we can accommodate longer messages (the other two error message fields had already been increased).
  • Making the @@GLOBAL.gtid_executed system var writable, so that the Dolt binlog processor can keep it updated.
  • Adding the @@sql_log_bin system var so that mysqldumps can process cleanly (mysqldump includes SET @@SESSION.SQL_LOG_BIN= 0; in the dump file when gtid_mode is enabled on the server).
• 1560: fix expression round trip bug scalar expressions need to round-trip for CHECK constraints, which expect to re-parse what they serialize with String().
• 1559: small fix for information_schema.parameters table This column name was updated on MySQL docs
• 1558: apply spatial index lookup Changes:
  • Add support for displaing SPATIAL KEY for SHOW CREATE TABLE.
  • Allow IndexedTableAccess for in-memory tables that have spatial indexes
  • dolt pr with interface changes: https://github.com/dolthub/dolt/pull/5164
  • added new testing suite for Spatial Indexes
  • some fixes to prepared queries for filtered tables
• 1557: make the length of logged queries configurable and default to non-tru… …ncated
• 1556: fix multiple column partition fix for: https://github.com/dolthub/dolt/issues/5209
• 1555: Fix dolt panic when dealing with top-most left join When moving join conditions to filter, update top join even if a join is left or degenerate. Fixes https://github.com/dolthub/dolt/issues/5136
• 1553: Merge join inclusion correctness Merge join was not principled selecting 1) monotonic filters and 2) sorting indexes that aligned with the filter of choice. We also failed to push filters referencing a single table out of join conditions.
• 1552: fixes from diffs between mysql and dolt info_schema dumps
  • Fixed incorrect values in Dolt that did not match MySQL result on information_schema dump
  • Unskipped fixed test
• 1546: add information_schema.parameters table Added information_schema.parameters table. Removing grant option privileges being granted incorrectly in grant all statement.
• 1545: Merge join panicked with non-comparator filter expression Merge joins are only created with a subset of expression.Comparer filters today (EQUALS). We previously expected a single comparer filter at exec time. But when there are multiple filters, the parent filter expression will be an AND, and the type cast fails. Instead of force casting the filter to a comparer during exec iter building, we will now expect a conjunction of comparers. For simplicity, we join on the first filter and add a parent filter iter with the rest. Alternatively, we could limit merge joins to single filters, or expend the memo to accommodate filters moved out of join conditions during join exploration.
• 1544: fix for information schema tables collation and charset values
• 1543: Validate lookup selection before adding to memo
• 1542: Support decimal literals. Fixes https://github.com/dolthub/dolt/issues/5068
• 1541: truncate really long queries
• 1540: Validate the lookup expression for indexedTableAccess The interface for converting a table scan to an indexedTableAccess disregarded whether the index implementation supported a specific type of range expression. The introduction of commit hash indexes for system tables requires us to prevent arbitrary range lookups, permitting only point lookups.
• 1539: if privilegeSet is nil, return empty row for info schema tables
  • privilegeSet was nil for ld repository tests as there were no privileges granted and was trying to test on information_schema.views table.
  • the now exported method is used in ld to grant all privileges for the test user fixes test in https://github.com/dolthub/ld/pull/12642
• 1538: implement st_intersects MySQL Reference: https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-intersects
• 1537: Covered @@GLOBAL.time_zone in CONVERT_TZ function
• 1536: adding spatial index checks
• 1535: add some information_schema privileges and constants tables Tables added:
  • SCHEMA_PRIVILEGES
  • TABLE_PRIVILEGES
  • USER_PRIVILEGES
  • KEYWORDS
  • ST_GEOMETRY_COLUMNS
  • ST_SPATIAL_REFERENCE_SYSTEMS
  • SCHEMATA_EXTENSIONS
  • ST_UNITS_OF_MEASURE
  • USER_ATTRIBUTES - attributes column is missing.
  • COLUMNS_EXTENSIONS - engine_attribute and secondary_engine_attribute are reserved for future use.
  • TABLES_EXTENSIONS - engine_attribute and secondary_engine_attribute are reserved for future use.
  • TABLES_CONSTRAINTS_EXTENSIONS - engine_attribute and secondary_engine_attribute are reserved for future use.
• 1532: implement st_within() for Point Part 1/7 This is the implementation of st_within(sql.Point(), <any geometry>). MySQL Reference The other geometry types are left as unsupported for now, to both reduce the size of the PR and because they're hard...
• 1530: mysql_db: binlog replication configuration table binlog replication requires a persistent store for replication configuration so that a server can be restarted and still retain the replication configuration settings a user provided. MySQL uses a table in the mysql database for this, so I've added that table to our implementation of mysql_db. I added a test for the new table's schema, but skipped the JSON loading test that other entities implemented, since I think the JSON serialization support is deprecated and everything is serializing through flatbuffers now. Happy to implement that test if it's still need though.
• 1529: Fix resolve subq issue with empty parent scope
• 1528: Decorrelate filtered subqueries with single rel This decorrelates simple single relation scopes with filter expressions. Unnesting scopes can have dramatic effects on join planning. For the query that motivated this work (a changed integration query plan), latency went from 90 seconds -> 2 seconds. This PR uses a SelectSingleRel node to represent the folded scope, which passes through join planning before being converted back into Filter(Table) by normalizeSelectSingleRel after all rules.
• 1527: Catch missed antijoins A resolve irregularity tries to resolve subqueries before resolving the outer scope. The result was subscopes with off-by-scope index field counts. Add a skip to delay this so we can catch more cacheable subqueries for decorrelation. A proper fix will need to rewrite resolve and field setting rules. The runtime for the query of interest reduces from 29 min -> 18 min, despite the second breaking the join hint order.
• 1526: Added CALL ... AS OF
• 1524: Fix empty cachedResult/existIter issues MAX_1_ROW no longer uses a CachedResult, and eagerly loads its child on the first call RowIter(). Three things can happen:
  1. the child returns one row, which we cache in the node state
  2. the child returns zero rows, which we note with a flag in the node state
  3. the child returns more than one row, and we error RowIter returns the result row or an emptyIter if no child rows were returned. The emptyIter sentinal is important for antiJoin correctness and join short-circuit performance. Original error from system table benchmarks:

  error running query {connectTime=2023-01-07T01:12:10Z, connectionDb=sbtest, error=attempted to call RowIter() on a disposed Node: *plan.CachedResults, query=select * from dolt_commit_ancestors where commit_hash = (select commit_hash from dolt_log limit 1 offset 599)}
  

• 1523: Change coster for non-stats tables dolt side https://github.com/dolthub/dolt/pull/5112
• 1522: Fixed ENUM issue when using INSERT INTO ... SELECT
• 1521: implementing more st_... functions implements:
  • st_startpoint
  • st_endpoint
  • st_isclosed
• 1520: implementing st_distance MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-distance
• 1519: partially implementing st_within Apparently, I've already added support for st_within() for Point vs Geometry, so this is just adds a bunch of test cases for every other geometry.
• 1517: Added the remaining statements for stored procedures Also added labels to BEGIN...END blocks, which were missing.
• 1514: binlog replication interface between GMS and Dolt This PR adds a BinlogReplicaController interface that defines how GMS interacts with the binlog replication code in Dolt when a user enters a replication SQL command, such as CHANGE REPLICATION SOURCE TO ..., START REPLICA, etc. The biggest remaining TODO in this code is adding support for CheckPrivileges functions. In the future, we may want to refactor out the generic binlog replication code to GMS and keep the Dolt/storage specific components in Dolt with a more detailed interface, but that felt like overkill for the initial binlog implementation.
• 1512: partially implementing st_equals MySQL docs for reference: https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-equals This adds support for st_equals() only in the Point vs Point case. There are skipped tests for all other cases.
• 1511: Fix nested subquery expression caching regression
• 1510: verify and fix info schema tables - 2
  • Complete tables: CHARACTER_SETS COLLATIONS ENGINES - only the InnoDB engine is supported SCHEMATA COLLATION_CHARACTER_SET_APPLICABILITY
  • Some info missing: COLUMN_STATISTICS - histogram json column only has buckets key values. PROCESSLIST - status is not defined completely; "Query" command is supported only.
• 1509: Remove IndexedInSubqueryFilter, add SemiLookupJoin, AntiJoin
  • IN_SUBQUERY and EQUALS_SUBQUERY (and NOT ...) are converted to joins when the subquery does not reference the outer scope
  • RIGHT_SEMI_JOIN and RIGHT_SEMI_LOOKUP_JOIN are added as a replacement for IndexedInSubqueryFilter
  • MAX_1_ROW added for runtime-enforcement, previously handled by EvalMultiple
  • fix various bugs in join iter and lookup generation Note:
  • RIGHT_SEMI_LOOKUP_JOIN is only safe when the left side of the join is converted into a unique index lookup, otherwise duplicate rows will have been missed TODO:
  • selection, projection, etc rearrangements moving towards JoinApply for correlated expressions
  • collapse scopes without losing MAX_1_ROW enforcement
  • deduplicate tables in joins without losing MAX_1_ROW and row duplication constraints
• 1507: typos
• 1505: Bug fix for null handling in group by Fixes: https://github.com/dolthub/go-mysql-server/issues/1503
• 1504: Rewrote readme and related docs
• 1502: add support for features and feature collection for geojson make our st_geomfromgeojson() parse formats that contain type Feature and FeatureCollection reference: https://www.rfc-editor.org/rfc/rfc7946 (sections 3.2 and 3.3)
• 1502: add support for features and feature collection for geojson make our st_geomfromgeojson() parse formats that contain type Feature and FeatureCollection reference: https://www.rfc-editor.org/rfc/rfc7946 (sections 3.2 and 3.3)
• 1501: verify and add more info schema tables Complete tables:
  • Fixed and verified row values for tables: TRIGGERS, CHECK_CONSTRAINTS, KEY_COLUMN_USAGE, KEY_COLUMN_USAGE, TABLE_CONSTRAINTS and SCHEMATA
  • Added REFERENTAIL_CONSTRAINTS table Fixed, but has missing info tables:
  • TABLES - missing columns/does not match MySQL values: avg_row_length, data_length, max_data_length, max_data_length, data_free
  • ROUTINES - missing stored functions
  • VIEWS - view_definition value does not match MySQL Changes:
  • added CreateViewStmt member to View definition to keep the information for algorithm, definer and security when it's created first. These are needed for information_schema.views table.
  • added PeekNextAutoIncrementValue to AutoIncrementTable interface for getting the next auto_increment value without incrementing the current value. This is implemented for both memory.Table and WritableDoltTable.
• 1499: merge join dolt bump: https://github.com/dolthub/dolt/pull/5061
• 1498: /{go.sum, sql}: allow time conversions for golang zero time https://go.dev/play/p/nzS0vAAI5yd remove call of time.IsZero() to allow 0001-01-01 00:00:00 as valid datetime.
• 1496: skip once after for stored procedures
• 1494: add 'privileges' column for information_schema.columns table Made information_schema database and its tables accessible to all users. Note:
  • column privileges is not supported yet.
  • view columns not supported yet.
• 1493: cte with same name in subquery fix for: https://github.com/dolthub/dolt/issues/4950
• 1491: Revert "resolve cte before resolving tables" Reverts dolthub/go-mysql-server#1485
• 1490: /sql/datetimetype: fix ranges to match mysql
• 1489: Greatly simplified stored procedure analyzing and variable referencing Practically everything is implemented using interfaces now, so we're not special casing every node that has some sort of "divergent" behavior from a standard sql.Node. Additionally, instead of associating every variable with a global id, there's a sort of stack implementation using nested scopes while executing a stored procedure. This simplifies analysis (the step assigning ids now just validates) and makes it easier to add the remaining stored procedure statements.
• 1486: allow naked identifiers for as of statements added a special case for unresolved columns for statements using as of <identifer> fix for: https://github.com/dolthub/dolt/issues/3116
• 1485: resolve cte before resolving tables Changes:
  • resolve the CTE before validating procedures
  • handle SubqueryAlias Fixes: https://github.com/dolthub/dolt/issues/4480
• 1481: Truncate now checks system variable foreign_key_checks Truncate now checks system variable foreign_key_checks and does not throw if the variable is not 1. This fixes https://github.com/dolthub/dolt/issues/5007
• 1480: add some supported columns for STATISTICS and COLUMNS info schema tables
  • Added sub_part for STATISTICS table.
  • Fixed column_default, datetime_precision, extra and other columns for COLUMNS table.
  • Skipped cardinality column for STATISTICS table as it's not supported in Dolt yet.
  • Skipped privileges column for COLUMNS table for now. (in progress)
  • Modified all Dolt supported MySQL functions to use its FunctionName() method to create the string statement of the function itself. The FunctionName() method returns the name of the function in all lower cases. This makes it so that all Dolt supported MySQL functions are consistent to what MySQL returns for queries such as SHOW CREATE TABLE or the column_default of information_schema.COLUMNS table. Notes:
  • Dolt does not support Time length yet.
  • Dolt currently handles BOOL and TINYINT data types the same with INT8, but MySQL explicitly defines BOOL as tinyint(1), which is different from tinyint for TINYINT data types.
  • View columns are not available in COLUMNS table yet.
• 1479: check that all bindings are used Fix for: https://github.com/dolthub/dolt/issues/4481
• 1477: added collation support for STRCMP adding collation support for STRCMP
• 1476: group_by: collation-aware grouping Hi doltfriends! Here's another compatibility fix. This one I'm more confident about: GROUP BY is not collation aware when grouping! The included test reproduces the issue trivially, but for completeness: when using a GROUP BY statement that includes a textual column, the column's collation needs to be taken into account when creating the aggregation groups. E.g., for the following schema and query

  CREATE TABLE test (id bigint, title varchar(255) collate utf8mb4_0900_ai_ci, PRIMARY KEY(id));
  INSERT INTO `test` (title) VALUES ('val1'), ('VAL1'), ('val2'), ('VAL2')
  SELECT COUNT(*) FROM test GROUP BY title
  

  We should expect the result of the SELECT to contain two rows, as there are only two groups when grouping by title. Even though we've inserted 4 unique values, the utf8mb4_0900_ai_ci with which the column is collated is case insensitive, so in practice, 'val1' = 'VAL1' and 'val2' = 'VAL2' when grouping. This is not the current behavior. The fix is straightforward: use a weight string when hashing to generate the grouping key instead of using the expression's literal value. I did a small refactoring in collations.go so that we can write the weight string directly into the grouping key's hasher and continue using the same code for the existing HashUint API. cc @dbussink
• 1475: Fixed stored procedure panics This fixes two panics that were discovered for stored procedures. The first comes from this issue: https://github.com/dolthub/dolt/issues/4980. The second deals with an issue with external stored procedures, where the usage of an uninitialized user variable would throw a panic over the nil value.
• 1474: fix hexadecimal literal value conversion MySQL docs: In numeric contexts, MySQL treats a hexadecimal literal like a BIGINT UNSIGNED (64-bit unsigned integer).
• 1470: Subquery caching rules onceAfterAll We cannot cache subquery results for a scope until the child scope has been finalized. A child scope cannot be finalized until the parent scope is finalized. Caching subqueries has to happen after all scopes are finalized. Applying hash lookups in the old way is much more difficult with this setup. This also exposed some bugs related to cacheability checks and subquery/union finalization recursion. Rewrite rules that depend on subquery caching to be in order traversals in OnceAfterAll. The original bug sourced from a bad resolve in a query like this:

  select ab.* from ab join pq on a = p where b = (select y from xy where y in (select v from uv where v = b)) order by a;
  

  The join and two scopes appear necessary to trigger the original bug. The join's children will be unresolved when we index the inner scopes, whereas a single relation scope's child will be a resolved table. The two scopes provide a gap for determining cachability in the middle scope while the child scope still had a deferredColumn, which previously permitted caching. GMS bump: https://github.com/dolthub/dolt/pull/4987
• 1469: bringing back assert prepared For some reason, I deleted AssertErrPrepared, which allowed many tests to incorrectly pass. This PR adds that back and fixes the failing tests. There were a couple validation rules that were never run, which seems wrong (validateUnionSchemasMatch is still never run for prepared queries). I also added a helper TestSingleScriptPrepared to more easily debug prepared script tests There are prepared tests in dolt that also fail because of these changes, so I also had to fix them Companion PR: https://github.com/dolthub/dolt/pull/4968
• 1468: Update README.md added team link and dolt docs link
• 1466: fixes bug where TIMEDIFF wasn't returning null when given null
  • added unit tests for null arguments for TIMEDIFF
  • added unit tests for null arguments for DATEDIFF
  • updated TIMESTAMPDIFF to throw an error when given a null unit
  • added unit tests for null arguments for TIMESTAMPDIFF
• 1464: clean up on current information schema Updated information_schema tables' schemas. Fixed some table values for the schema changes
• 1463: fix sq finalize ordering bug Transform table + filter into table lookup in a subquery expression should happen in finalizeSubquery, not in resolveSubquery. Otherwise we will push a filter with a GetField index that could be rearranged when the parent scope is finalized.
• 1462: sqa finalize self before child recursion bug We must finalize a subquery [/ expression]'s scope before finalizing any child subquery [/expression]. Otherwise, the child may reference invalid parent scope indexes that are not fixed until the parent is finalized.
• 1461: Implemented LOOP, LEAVE, and ITERATE, partially implemented DECLARE ... HANDLER This needs many, many, many more tests. Also should rewrite how stored procedures are analyzed as well, but that's a larger project overall.
• 1460: add STRCMP functionality fixes: https://github.com/dolthub/dolt/issues/4478
• 1459: Bug fixes for CrossJoin scope visibility and disposing mem caches for IndexedInSubqueryFilter crossJoinIter was the only join iterator that wasn't taking in a parentRow with outer scope values and passing that row prefix to its children. There's some low-hanging fruit to refactor the joinIters to reduce duplication, but it didn't seem high priority, so I stopped short of that. Fixes: https://github.com/dolthub/dolt/issues/4926 While testing that, I also hit a bug with a mem cache not being released by IndexedInSubqueryFilter and included that fix here. There were also some dispose member and Dispose functions in the join iterators that I verified were not used anywhere, so I pulled those out to tidy up. Dolt CI Tests: https://github.com/dolthub/dolt/pull/4947
• 1458: Reorder joins before finalizing subqueries We cannot set field indexes for subquery expressions until the parent scope has been finalized. The query that surfaced this bug was a subquery expression with a dependency on a join child, whose order is rearranged after we finalize subquery. Ex: the query below swaps the schema from under the subquery expression; y and is_one should match, but we reorder the join for a lookup, changing the scope schema from [xy,uv] -> [uv,xy]. The subquery expression now selects the wrong column for comparison:

  > select y, (select 1 where y = 1) is_one
  from xy join uv on x = v
  order by y;
  +---+--------+
  | y | is_one |
  +---+--------+
  | 0 | 1      |
  | 0 | 1      |
  | 1 | NULL   |
  | 1 | NULL   |
  +---+--------+
  

  We select the correct field now when the subquery is finalized after the parent scope is fixed:

  > select y, (select 1 where y = 1) is_one
  from xy join uv on x = v
  order by y;
  +---+--------+
  | y | is_one |
  +---+--------+
  | 0 | NULL   |
  | 0 | NULL   |
  | 1 | 1      |
  | 1 | 1      |
  +---+--------+
  

• 1457: simplify LIKE expressions to take advantage of indexes I needed this for my blog :) It might give performance benefits 🤷 Certain LIKE expressions over string columns should take advantage of prefix indexes. TODO: there are a few more optimization we could make, but they'd take a bit more effort (I'd also need to confirm that MySQL does them as well).
• 1456: writing tests with non-determinism
• 1455: fix incorrect result from arithmetic ops The result of the query select 2000.0 / 250000000.0 * (24.0 * 6.0 * 6.25 * 10.0); is fixed, but the decimal scale is incorrect (made issue for it)
• 1454: enforce test ordering
• 1452: Order the test results. This fixes the currently-failing dolt tests: https://github.com/dolthub/dolt/actions/runs/3622688428/jobs/6107705037
• 1451: fix visibility for on duplicate key update with cte fix for: https://github.com/dolthub/dolt/issues/4562 adding a special case in analyzer when insert.Source is a *plan.With.
• 1450: If a window does not have "ORDER BY" specified, use a default frame. If a window does not have "ORDER BY" specified, use a default frame. This fixes https://github.com/dolthub/go-mysql-server/issues/1449
• 1448: implementing PREPARE, EXECUTE, and DEALLOCATE fx for: https://github.com/dolthub/dolt/issues/4452
• 1447: Disambiguate aggregation expressions Disambiguate aggregation expressions so different columns with same sources don't end up clobbering each other. Fixes https://github.com/dolthub/dolt/issues/4140
• 1445: fix type handling for derived table columns derived table column is now created from the most permissive type and the values are casted into that type at Eval. have skipped Prepared test as sort field type does not match non-prepared execution.
• 1443: Clean up CachedResult Dispose lifecycle Dispose should only be called on iterators whose resources are transient. Resources that outlive a single iterator should be Disposed at the end of the query through the Node (not iter). A CachedResult can be safely Disposed after the reseult is transferred to the parent HashLookup. A HashJoin should not be disposed when its parent is an iterative node that can call RowIter again. But if that is mistaken for some yet unforseen edge case, disposing the CachedResult returns it to a state where it will correctly recompute its contents rather than cycling on a nil'd cache where |cacheDone| is left as true.
• 1442: gms support for filters on SHOW DATABASES fix for: https://github.com/dolthub/dolt/issues/4484 companion pr: https://github.com/dolthub/vitess/pull/204
• 1441: Fix unresolved insert source panic PR overlays two commits:
  1. revert faulty PR https://github.com/dolthub/go-mysql-server/pull/1434
  2. fix panic in a different way
• 1440: Implemented DECLARE support for variables and cursors This adds support for DECLARE variables and cursors. I implemented nesting support by giving all variables a procedure-unique ID, with the scope determining which ID is resolved. A flat slice of IDs are far simpler to track than a proper tree structure. I did skip one test for GMS, as a RowIter reads the current state of the table, rather than the state that the RowIter generated with.
• 1437: add any_value() and option to enforce strict group by Added support for another option in @@sql_mode called ONLY_FULL_GROUP_BY, which makes it so select statements over group by must contain columns that are part of the group by. Before, dolt was always strict about group by selects, now (to match MySQL) we are not strict by default. Additionally, there is now support for the "aggregate" function any_value() which bypasses the ONLY_FULL_GROUP_BY if it is set. Also, changes GetGlobalVariable and GetSessionVariable to convert sql.SetTypes to strings. MySQL docs for reference:
  • any_value(): https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html
  • ONLY_FULL_GROUP_BY: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by fix for: https://github.com/dolthub/dolt/issues/1448 and: https://github.com/dolthub/dolt/issues/4604
• 1436: Adding TestJoinQueriesPrepared in GMS Adding a new TestJoinQueriesPrepared enginetest function and unskipping a few join tests that are working now. Dolt update: https://github.com/dolthub/dolt/pull/4898
• 1435: trim collation for information_schema.columns.dataType and information_schema.columns.colType Our dataType column in the information_schema.columns table didn't match MySQL when there were columns that had collations. dBeaver reads in metadata from information_schema.columns, and when there was an error, it threw an error saying that the table was read-only. fix for: https://github.com/dolthub/dolt/issues/4891
• 1434: Fixes issue 4857: insert qualify CTE source bug Edge case where we failed to resolve columns in the insert body if the source had a certain nested CTE/table alias/column alias structure. Something about how CTEs get parsed with TableAlias (while a regular subquery does not) triggers a path where we attempt to index an unresolved Subquery alias. This PR puts the resolveInsert rule before resolveColumns, and adds fixes for a richer post-analyze insert Source while resolving the root insert scope. Re: https://github.com/dolthub/dolt/issues/4857
• 1433: Fix nested subquery join index in subquery expression w/ outer scope visibility The plan representation and exec scope passing diverged for subqueries. We were indexing assuming no scope passing, but at runtime still prepending scope rows, which indirectly snuck around not explicitly passing scope through the subquery alias.
• 1432: get better convertToType for mergeUnionSchema rule Instead of converting every values to string, the type can be determined as appropriate as possible to avoid precision loss in number types.
• 1431: Changing handling of type conversion to better match MySQL's behavior Fixes: https://github.com/dolthub/dolt/issues/4874 Dolt CI Test Run: https://github.com/dolthub/dolt/pull/4887
• 1430: fix declaration so the compiler checks it properly
• 1429: implementing time fix for: https://github.com/dolthub/dolt/issues/4643 changes:
  • allow conversion between sql.Time and sql.Datetime
  • return NULL instead of errors, and throw warnings
  • added time()
• 1426: Have JSONDocument implement driver.Valuer
• 1425: tests for non-unique indexes over keyless tables Non-unique indexes work as expected for in-memory tables, but there were no tests. companion pr: https://github.com/dolthub/dolt/pull/4873 tests for: https://github.com/dolthub/dolt/issues/4861
• 1424: Refactor check for canonical join
• 1422: add uptime not quite sure how to test or if it's the best way to have system variables that dynamically update... fix for: https://github.com/dolthub/dolt/issues/4473
• 1420: Fix issue where case expression is evaluated too early Fix issue where case expression is evaluated and the result is being treated as a literal. Fixes https://github.com/dolthub/dolt/issues/4716
• 1419: errors to date_add() and date_sub() are warnings fixes part of: https://github.com/dolthub/dolt/issues/4643 Will implement TIME() and sql.Time type in a separate PR.
• 1417: Fixed integer handling when given a floating number Integers are supposed to round floating numbers, but we just did a simple conversion so we were truncating the decimal portion.
• 1416: add histogram column to information_schema.column_statistics fix for: https://github.com/dolthub/dolt/issues/4412
• 1415: support show status Fix for: https://github.com/dolthub/dolt/issues/4473 The way I get and display the variables in the right scope is a little inefficient, but not enough to really matter. TODO:
  • Need a good way to test these queries.
  • we are missing a ton of variables, worth adding?
• 1414: limit arithmetic literals are all ints but different types for query with ... limit 0, 200 in TopN plan, arithmetic was returning decimal type because 200 was handled asuint8 and 0 was int8 types, so if the both the values are any non-matching int type, the values are converted to int64 for + and - operations.
• 1412: prevent modifying columns to blob/text without prefix length in index There's an edge case in where there's an index defined over a column without a prefix length, and if you modify that colunm to a text/blob it should throw an error. Additionally, it should preserve prefix lengths when it's still a string type. If the new type is too short, the prefix length should be dropped.
• 1411: Fix for commutative join types getting stuck in analysis loop This change fixes an issue where the analyzer can get stuck in a loop bouncing back and forth between the original and commuted plan and being unable to stabilize the plan. As part of that fix... it also deduplicates logically identical relExprs in exprGroups and changes the finalizeSubqueries analyzer rule so that it only runs once on each subquery, from the bottom of the plan tree, up. Fixes: https://github.com/dolthub/dolt/issues/4816 Dolt Tests: https://github.com/dolthub/dolt/pull/4859
• 1408: hashlookups can hash byte slices fix for: https://github.com/dolthub/dolt/issues/4682
• 1407: adding tests for unique indexes verifies this was fixed: https://github.com/dolthub/go-mysql-server/issues/571 pulls a duplicate key update test from dolt
• 1406: in memory implementation of prefix indexes
• 1405: join table functions tests here: https://github.com/dolthub/dolt/pull/4795 The main correctness risk here is if any integrator table functions do not commute/associate during join reordering. We already manually handle JSON_TABLE, but there could be others.
• 1404: revert parallel ita sister PR here: https://github.com/dolthub/dolt/pull/4776
• 1403: Add tests for column truncation of bad values with INSERT IGNORE Associated Dolt PR: https://github.com/dolthub/go-mysql-server/pull/1403
• 1402: give HAVING visibility into outer scope fix for: https://github.com/dolthub/dolt/issues/4723
• 1400: Adding error code mapping and more tests for ErrNonAggregatedColumnWithoutGroupBy Adding a SQL error code mapping for ErrNonAggregatedColumnWithoutGroupBy and tests for validation with opaque nodes. From PR feedback in: https://github.com/dolthub/go-mysql-server/pull/1396
• 1399: Add tests to verify that we call call qualified procs. Adding tests for https://github.com/dolthub/dolt/issues/4725 go.mod is pointing to this PR's branch: https://github.com/dolthub/vitess/pull/200
• 1398: prevent foreign keys from referencing indexes with prefix lengths MySQL does not support foreign keys that reference indexes with prefixes, but we could? https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#:~:text=Index%20prefixes%20on%20foreign%20key%20columns%20are%20not%20supported
• 1397: fix intDiv div operation and more edge cases with precision for all arithmetic operations IntDiv does operations in decimal type values and returns the int part of the decimal value result. If the value goes out of bound of int64, it errors. Fixed few more edge cases for all arithmetic operations including:
  • any time type value gets parsed as int value with pattern of YYYYMMDDHHMMSS or YYYYMMDD depending on what's defined. This applies to all arithmetic operations.
  • invalid string tries to parse available numbers from the beginning of the string value and used in the arithmetic operation. 12.4ag3.2jgw499 is parsed as number 12.4, and a942.6ng488 is parsed as 0.
• 1396: Adding validation for illegal use of aggregation functions GMS currently panics if an aggregation function that relies on the implicit, all-rows grouping is mixed aggregation functions that rely on a window. This PR adds validation to detect these invalid queries and return the exact same error message as MySQL. Fixes: https://github.com/dolthub/dolt/issues/4739
• 1395: Add tests for year type behavior Adding tests for https://github.com/dolthub/dolt/issues/4742
• 1394: Fix test determinism
• 1393: handle float number for bit arithmetic ops MySQL rounds any float value to integer before any bit operation and returns non-negative result only. Int64 values are converted to Uint64 type values including negative numbers. Note: All results will be in uint64 type as _binary introducer conversion is not supported yet. E.g. these are not supported:

  _binary b'0001' | b'0010'
  _binary X'0008' << 8
  BINARY NULL & NULL
  BINARY NULL >> 4
  

  We don't support bit-wise compliment operation ~ and BIT_COUNT function yet.
• 1392: Insert dependency passthrough cols inbetween aggregation and projection Re: https://github.com/dolthub/dolt/issues/4735 Aggregation flattening dropped passthrough column dependencies, causing the parent Projection to throw GetField resolve errors. From the new docstring:

  // The aggregation node must provide input dependencies for parent projections.
  // Each parent expression can depend on zero or many aggregation expressions.
  // There are two basic kinds of aggregation expressions:
  // 1) Passthrough columns from scope input relation.
  // 2) Synthesized columns from in-scope aggregation relation.
  

  This is only relevant for expression that reference aggregations, which is somewhat curious. So for example, in select x/y, x/count(y) from x, the x/y expression is executed entirely in the child aggregation node. x/count(y) is separated into two steps, 1) evaluate count(y) and passthrough x, and then evaluate arithmetic x/count(y) in the parent projection. The error here is we were failing to expose the x passthrough to the parent operator that evaluated the division.
• 1390: allow decimal values for mod operation % can be operated on decimal values.
• 1389: Provide better errors when attempting to use stored procedures without a DB Initial attempt at fixing https://github.com/dolthub/dolt/issues/3832 by throwing a better-worded error if no database is selected.
• 1388: Fix groupby on decimal col failing See detailed discussion here.
• 1387: extend Index interface to handle prefix lengths, and add in-memory implementation SHOW CREATE TABLE displays the prefix lengths specified for secondary indexes
• 1386: more error checks for prefix lengths improves errors when creating prefix indexes
• 1385: Moving qualifyColumns up higher in analyzer rules order This change moves the qualifyColumns analyzer rule up to the second position in the DefaultRules batch (resolveNaturalJoins still needs to run first to resolve duplicate column names.) The main issue I hit was that pushdownGroupByAliases was handling identifying aliases in GroupBy grouping expressions, and because GroupBy combines its projection expressions and grouping expressions in its Expressions() implementation, I had to special case GroupBy in qualifyColumns to handle identifying alias references in the grouping expressions that were defined in the projection expressions. The other interesting point is an open question around the right way to resolve ambiguity in the order by clause between an alias name and a column name. Documentation and testing shows that an alias should be preferred in that case, but MySQL behaves differently and prefers the column if the ambiguous reference is the argument to a function. For now, I changed the one test this affects to qualify the reference in the order by clause with a table name, and I opened an issue on MySQL's bug tracker to try and get confirmation if this is a bug or working as expected. Dolt CI tests: https://github.com/dolthub/dolt/pull/4721
• 1383: Moved privilege caching to session This accomplishes two things:
  1. This changes how we cache privilege sets. The previous behavior was fine for the existing feature set, but it didn't support roles all too well. Right now all roles are applied to any user that they're assigned to, but in a future PR roles will be selectable (like they are in MySQL). Role selection is at the session level, so it makes sense that the cached privilege set is also at the session level. It's straightforward to update a sesson's roles and invalidate the cache in the same place.
  2. If an integrator wanted to see what privileges a session had, then they'd need to query the engine, which meant keeping a reference to the engine in multiple places. Now, they just query the session on the context, which they're already passing around.
• 1382: Flag for finished but empty CachedResult Hashed joins can have a CachedResult that filters to zero rows. We currently try to recompute the map every time when we find a nil result. This adds a short-circuit both to the CachedResult, and the parent join (n x 0 = 0). Every RowIter that attempts to re-rowIter a cachedResult that can be zero now should check for the ErrEmptyCachedResult sentinel. This is only possible in joinIter currently.
• 1381: Adding support for setting 'ANSI' as a sql_mode Fixes: https://github.com/dolthub/dolt/issues/4663
• 1380: sql: Added IndexDef to sql/index.go, sql/core.go
• 1379: prepend rows for only projector nodes This PR was reverted but now it has fixed tests that passes for dolt engine tests. The table is keyless table, which causes output result to be in randomized order in dolt compared to gms, so the tests now include order by clauses to persist the order of the output rows.
• 1377: make json ordering tests less strict We shouldn't include tests for ordering between two MySQL "json object" types. MySQL notes that their ordering is unspecified but deterministic. The new storage format has a different deterministic ordering than the old storage format in dolt.
• 1376: Revert "remove having clause from prepending rows (#1368)" This reverts commit f179734a7fe27eae530e6e6cf6525e1f236824ea.
• 1375: don't escape <, >, or & when printing json
• 1374: enum zero() is 1
• 1373: Validate stored procedures, but only throw errors when they are invoked. This fixes https://github.com/dolthub/dolt/issues/4670
• 1372: allow server to start if unix socket file path is in use without unix socket set up
• 1371: improve errors for invalid index prefixes We currently allow specifying prefix lengths on primary and secondary keys defined on VARCHAR and CHAR text types despite not actually using the prefix for lookups. This PR allows dolt to throw the right errors. Additionally, we also allow specifying prefix lengths for non-string columns, which doesn't make sense. Hopefully nobody has created tables that depend on this buggy behavior.
• 1369: Deleted handler_test_common.go (only used in handler_test.go) This prevents testify libraries from being included in binaries that depend on the server package.
• 1368: remove having clause from prepending rows
• 1367: Fix logic for replacing expressions with alias references in OrderBy clause The previous code in pushdownSort was able to replace a missing column reference with an alias reference when that column was aliased, but it wasn't able to correctly replace compound expressions such as ORDER BY BINARY(mytable.mycol) due to how we tracked only the missing column references and didn't consider the sort field expression as a whole. This change expands that alias substitution support so that it looks at the entire sort field expression (i.e. not just the individual named references inside it) and replaces missing sort fields with alias references only when the entire sort field expression matches the aliased expression. This fixes an issue with Prisma compatibility with Dolt (https://github.com/dolthub/dolt/issues/4511).
• 1366: Fixes filtering for null values on json extract This PR fixes two issues:
  • JSON_EXTRACT returning json null values when it should be returning sql null values if a path is unresolvable.
  • The relative ordering between json null values, sql null values, and json values.
• 1365: division / operation result in decimal type except for float type column reference Division operator, /, always results in decimal type value. Only exception is that if there is float type column reference on either side of the operator, it should result in float type value. Added decimal.Decimal type handling for ROUND and FLOOR functions
• 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.
• 1346: Altered the transaction interface to occur on the Session instead of on a Database This work is preparatory to supporting multiple databases in a single transaction. Also rewrites much of test engine test setup.

vitess

• 232: go/mysql/conn.go: Improve server-side cursor support.
  1. Send LastRowSent with the result set batch in which the cursor is actually exhausted.
  2. Only send CursorExists in the response to COM_STMT_EXECUTE which creates the cursor.
• 231: support 'show events' statement parsing
  • Added support for SHOW EVENTS statement parsing
  • Added support for SHOW CREATE EVENT statement parsing
  • Removed FULL option from SHOW TRIGGERS as it's not supported in MySQL.
• 230: update separator parsing for group_concat Updates separator parsing for group_concat to indicate whether the default separator should be used. This change is needed to allow '' as a separator. related: https://github.com/dolthub/go-mysql-server/pull/1693/
• 229: Added support for ALTER TABLE ... COLLATE Fixes https://github.com/dolthub/dolt/issues/5623
• 228: simulate server side cursors To be able to use the newest MySQL foreign data wrapper for Postgres, we have to support server side cursors. This PR emulates them, and hopefully gets us far enough... Additionally, this includes a fix for Windows clients (and other clients that don't have the DeprecatedEOF client capability flag set) where we send an extra EOF packet when we shouldn't. Fix for: https://github.com/dolthub/dolt/issues/5441 And this: https://github.com/dolthub/dolt/issues/3029 This one too (kinda): https://github.com/dolthub/dolt/issues/4840
• 227: add create and drop event parsing Add support parsing CREATE EVENT and DROP EVENT statements.
• 226: supporting syntax for user variables in PREPARE statements fix for: https://github.com/dolthub/dolt/issues/5530
• 225: fix parser to handle unique constraint names properly support these statements:
  • alter table <table_name> add constraint unique (<column_list>)
  • alter table <table_name> add constraint <name> unique (<column_list>)
  • alter table <table_name> add constraint unique <name> (<column_list>)
  • alter table <table_name> add constraint <name> unique <preferred_name> (<column_list>) fix for: https://github.com/dolthub/dolt/issues/5479
• 224: reorganizing time units and add extract syntax support syntax to fix: https://github.com/dolthub/dolt/issues/3006
• 223: Made signal conditions accept vars Partially addresses https://github.com/dolthub/dolt/issues/5452
• 222: Added known dynamic privileges Getting a general STRING rule to work without running into shift/reduce errors proved too time consuming, so I just added all known dynamic privileges according to MySQL's documentation (except SUPER_USER, which is also giving errors).
• 220: Exposing SQL Option and SQL Mode flags for Query events Query binlog events are sent from the primary when the replica needs to execute a SQL statement directly. This is only for DDL operations, since DML operations are handled with a structured data message that indicates exactly which rows changed. There's extra metadata passed in Query binlog messages that wasn't getting exposed by Vitess that we need to execute the statements correctly (e.g. foreign_key_checks disabled). This PR exposes the SQL Options and SQL Mode metadata so that we can access them from Dolt.
• 219: Backporting client support for caching_sha2_password auth The MySQL client API in our fork of vitess is limited to the mysql_native_password auth plugin, but the default auth plugin as of MySQL 8.0 is caching_sha2_password. This means that to use Dolt binlog replication, customers either have to change the default auth plugin in MySQL or they have to create a user that is identified with the older mysql_native_password auth plugin. This additional configuration step adds friction for customers wanting to try out Dolt binlog replication. This PR pulls in selected changes from the tip of vitessio/vitess to pick up client support for connecting to MySQL servers that default to caching_sha2_password auth plugin (and will still fall back to mysql_native_password). All of these changes are pulled directly from vitessio/vitess, without any other changes mixed in. I'm not aware of anywhere else in our codebase where we're using the MySQL client API from vitess. As far as I know, it's only used by the binlog implementation. That means this change shouldn't have any effect on other parts of Dolt/GMS, and I've gone ahead and run the Dolt CI tests against this change as a sanity check:
  • https://github.com/dolthub/dolt/pull/5339 I've pulled over additional tests from the tip of vitess for this logic. I've also tested locally and confirmed that my binlog replication integ tests all pass with this change, and no longer require overriding the default auth plugin for the MySQL server. I've also started on some additional binlog replication integ tests to make sure we cover password/no-password and ssl/no-ssl.
• 218: Fixed bug in COM_FIELD_LIST command implementation for table names that need quoting in a statement Fixes https://github.com/dolthub/dolt/issues/5151 Fixes https://github.com/dolthub/dolt/issues/4464
• 217: Adding implicit string concatenation for a variable assignment needed for binlog replication When a mysqldump is taken for a database tracking GTIDs, it outputs GTID tracking information via a line like:

  SET @@GLOBAL.GTID_PURGED= /*!80000 '+'*/ 'beabe64c-9dc6-11ed-8021-a0f9021e8e70:1-126';
  

  In MySQL 8.0+, the MySQL comment inserts a '+' between the = and the assignment value, which gets implicitly concatenated with the adjacent quoted string in MySQL. GMS/Dolt doesn't support this implicit string concatenation, but I wanted to get this query to work – otherwise customers will have to manually edit their mysql dump files to remove this syntax before it can be imported into Dolt. I didn't think it was worth taking on the larger change right now to support implicit quoted string concatenation everywhere (https://github.com/dolthub/dolt/issues/5232), so instead, I added special handling for this case since it's important for the binlog setup experience.
• 216: Fix int overflow on 32-bit architectures

  $ GOARCH=386 GOOS=linux go build ./...
  # github.com/dolthub/vitess/go/bucketpool
  go/bucketpool/bucketpool.go:57:15: math.MaxUint32 (untyped int constant 4294967295) overflows int
  

• 215: Adding two new binlog replication statements: RESET REPLICA and CHANGE REPLICATION FILTERS
• 214: Added CALL ... AS OF
• 213: Added additional CASE form Forgot that there are two forms to CASE statements, so I added the missing version.
• 212: add GRANT OPTION for GRANT STATEMENTS
• 211: Added last statements for stored procedures Of note, RETURN is used by stored functions and not stored procedures, but I added it anyway since it was super simple and we'll need it at some point.
• 210: Grammar updates for replication statements Adds support for:
  • CHANGE REPLICATION SOURCE TO
  • START REPLICA
  • STOP REPLICA
  • SHOW REPLICA STATUS
• 208: Allow charsets/collations in single or double quotes for create database/alter database Fixes: https://github.com/dolthub/dolt/issues/4977
• 206: Update README.md added blurb about Dolt's use of vitess and links
• 205: Added LOOP, LEAVE, and ITERATE Finding a workaround to allow : to parse resulted in the changes you see in token.go. There may be a better way to go about it, but I don't want to spend more time on it since this appears to work.
• 204: adding parser support for show databases like and where partial fix for: https://github.com/dolthub/dolt/issues/4484
• 203: Added OPEN, CLOSE, FETCH These are needed for CURSOR support.
• 202: support explaining tables, and adding extended keyword Even though it is deprecated, we can support EXPLAIN EXTENDED, since it does the same thing as normal EXPLAIN https://dev.mysql.com/doc/refman/5.7/en/explain.html#:~:text=In%20older%20MySQL%20releases%2C%20extended%20information%20was%20produced%20using%20EXPLAIN%20EXTENDED. Additionally, EXPLAIN, DESCRIBE, and DESC are all interchangable, so we should be able to EXPLAIN tbl Fix for: https://github.com/dolthub/dolt/issues/4185
• 201: adding parser support for prepared, execute, and deallocate supporting syntax for: https://github.com/dolthub/dolt/issues/4452
• 200: Support calling procedures from specified database.

  Description

  Support calling procedures from specified database, for example call mydb.test();

  Related Issue(s)

  This fixes https://github.com/dolthub/dolt/issues/4725

  Checklist

  • "Backport me!" label has been added if this change should be backported
  • Tests were added or are not required
  • Documentation was added or is not required

  Deployment Notes

  No deployment notes, this adds missing SQL support.
• 199: Added ALTER DATABASE parsing Adds support for parsing ALTER DATABASE queries.

Closed Issues

• 1533: convert_tz is not working as expected with @@GLOBAL.time_zone
• 1657: panic if project imports vitess.io/vitess@v0.9.0
• 1662: aliases overwriting original column
• 1632: Alter table drop constraint name complains the contraint does not exist
• 1317: README Rewrite
• 1602: driver/: LastInsertId is not supported
• 1621: Missing index for foreign key error
• 251: LastInsertId always returns 0
• 1596: driver/: dsn database name appears to be ignored
• 1591: unbound variable "v1" in query
• 1503: panic: interface conversion: interface {} is nil, not string group by collation
• 247: Memory table should error on UPDATE when it generates duplicate keys
• 1200: Testify getting included in binary builds.
• 1449: Windows without ORDER BY return wrong results
• 571: Unique indexes do not work in the in-memory implementation