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

Merged PRs

go-mysql-server

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

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

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

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

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

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

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

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

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

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

1688: Support for recursive CTEs with no union
Ex:

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

Re: dolthub/dolt#5657

1687: Fix left merge join bug
Re: dolthub/dolt#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: dolthub/dolt#5654
1683: Fix distinctness/sort enforcing
The original bug: dolthub/dolt#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 dolthub/dolt#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 dolthub/dolt#5623
Builds upon dolthub/vitess#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: dolthub/dolt#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 trinodb/trino#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
dolthub/dolt#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 dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#5530
companion: dolthub/vitess#226
1651: types: fix JSON NULL comparisons
1650: Replace false filter with an empty table with the schema of the filter.
Fixes dolthub/dolt#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: dolthub/dolt#5433
Context: #1642 (comment)
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 dolthub/dolt#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: dolthub/dolt#5514
1641: make st_within use spatial indexes
1639: Handle Quotes in Default Column Values
See Issue: dolthub/dolt#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 dolthub/dolt#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: dolthub/dolt#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 dolthub/dolt#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
[![Dependabot compatibility score](https://dependabot-badges.githubapp.com/badges/compatibility_score?dependency-name=golang.org/x/text&package-manager=go_modules&previous-version=0.3.7&new-version=0.3.8)](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 dolthub/dolt#5390
1608: Delete Join Support
Adds support for multi-table deletes.
Dolt CI Tests: dolthub/dolt#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 (dolthub/dolt#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: dolthub/dolt#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 dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#5291
fixes: dolthub/dolt#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 dolthub/dolt#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 dolthub/dolt#5214
1569: release all locks on session end
Bats test for this PR is in dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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 dolthub/dolt#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 dolthub/dolt#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 dolthub/ld#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 dolthub/dolt#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: #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: dolthub/dolt#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: dolthub/dolt#4950
1491: Revert "resolve cte before resolving tables"
Reverts #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: dolthub/dolt#3116
1485: resolve cte before resolving tables
Changes:
- resolve the CTE before validating procedures
- handle SubqueryAlias
  Fixes: dolthub/dolt#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 dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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 #1449
1448: implementing PREPARE, EXECUTE, and DEALLOCATE
fx for: dolthub/dolt#4452
1447: Disambiguate aggregation expressions
Disambiguate aggregation expressions so different columns with same sources don't end up clobbering each other.
Fixes dolthub/dolt#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: dolthub/dolt#4484
companion pr: dolthub/vitess#204
1441: Fix unresolved insert source panic
PR overlays two commits:
1. revert faulty PR #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: dolthub/dolt#1448
  and: dolthub/dolt#4604
1436: Adding TestJoinQueriesPrepared in GMS
Adding a new TestJoinQueriesPrepared enginetest function and unskipping a few join tests that are working now.
Dolt update: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#4874
Dolt CI Test Run: dolthub/dolt#4887
1430: fix declaration so the compiler checks it properly
1429: implementing time
fix for: dolthub/dolt#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: dolthub/dolt#4873
tests for: dolthub/dolt#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: dolthub/dolt#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 dolthub/dolt#4716
1419: errors to date_add() and date_sub() are warnings
fixes part of: dolthub/dolt#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: dolthub/dolt#4412
1415: support show status
Fix for: dolthub/dolt#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: dolthub/dolt#4816
Dolt Tests: dolthub/dolt#4859
1408: hashlookups can hash byte slices
fix for: dolthub/dolt#4682
1407: adding tests for unique indexes
verifies this was fixed: #571
pulls a duplicate key update test from dolt
1406: in memory implementation of prefix indexes
1405: join table functions
tests here: dolthub/dolt#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: dolthub/dolt#4776
1403: Add tests for column truncation of bad values with INSERT IGNORE
Associated Dolt PR:
#1403
1402: give HAVING visibility into outer scope
fix for: dolthub/dolt#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: #1396
1399: Add tests to verify that we call call qualified procs.
Adding tests for dolthub/dolt#4725
go.mod is pointing to this PR's branch: dolthub/vitess#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: dolthub/dolt#4739
1395: Add tests for year type behavior
Adding tests for dolthub/dolt#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: dolthub/dolt#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 dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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 f179734.
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 dolthub/dolt#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 (dolthub/dolt#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: #1693
229: Added support for ALTER TABLE ... COLLATE
Fixes dolthub/dolt#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: dolthub/dolt#5441
And this: dolthub/dolt#3029
This one too (kinda): dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#5479
224: reorganizing time units and add extract syntax support
syntax to fix: dolthub/dolt#3006
223: Made signal conditions accept vars
Partially addresses dolthub/dolt#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:
- dolthub/dolt#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 dolthub/dolt#5151
Fixes dolthub/dolt#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 (dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#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: dolthub/dolt#4185
201: adding parser support for prepared, execute, and deallocate
supporting syntax for: dolthub/dolt#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 dolthub/dolt#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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Q2 2023 Release

Merged PRs

go-mysql-server

Remaining Work:

vitess

Description

Related Issue(s)

Checklist

Deployment Notes

Closed Issues

Contributors