1.42.17

Merged PRs

dolt

• 8311: [kvexec] fix more lookup bugs related to schema/projection inconsistencies
  We were using the full table schema to index a covering index KV pair. The fix has to be sensitive to covering, non-covering, and keyless tables, which required extra tests on the GMS side to check all cases.
  fixes: #8308
  re: dolthub/go-mysql-server#2646
• 8303: Perf: Optimize SQL transaction commits in binlog replication applier
  The binlog replication applier process is responsible for applying the replicated updates from a MySQL source to the Dolt replica. Previously, when committing SQL transactions after applying a change, the applier would attempt to commit on all known databases. This worked when the number of databases was small, but when the server is handling hundreds of databases, it adds noticeable and unnecessary delay. This change switches to use the dirty tracking data of the session to more efficiently commit to only the databases that have had changes applied.

  Performance Comparison

  The old SQL transaction commit logic scaled exponentially, while the new logic scales linearly.

  Num Databases	Code Path	Replication Time (s)
  100	Old	11.02
  300	Old	144.34
  500	Old	633.44
  100	New	6.79
  300	New	20.51
  500	New	36.04

• 8294: Feature: Data conflict resolution during interactive rebase
  When data conflicts occur during an interactive rebase, customers can now use Dolt's conflict resolution tools to resolve the conflicts and continue the rebase operation. Note that schema conflicts are not supported yet.
  Example:

  > select * from dolt_rebase;
  +--------------+--------+----------------------------------+----------------------------+
  | rebase_order | action | commit_hash                      | commit_message             |
  +--------------+--------+----------------------------------+----------------------------+
  | 1.00         | drop   | iumpo2t0hd6drcn11jo8osdack1jmafp | inserting row 1 on branch1 |
  | 2.00         | pick   | us2uaji20dj77cnf1i2l698itr5392se | updating row 1 on branch1  |
  +--------------+--------+----------------------------------+----------------------------+
  > call dolt_rebase('--continue');
  data conflict detected while rebasing commit us2uaji20dj77cnf1i2l698itr5392se (updating row 1 on branch1).
  Resolve the conflicts and remove them from the dolt_conflicts_<table> tables, then continue the rebase by calling dolt_rebase('--continue')
  > select * from dolt_conflicts_t;
  +----------------------------------+---------+---------+--------+--------+---------------+----------+----------+-----------------+------------------------+
  | from_root_ish                    | base_pk | base_c1 | our_pk | our_c1 | our_diff_type | their_pk | their_c1 | their_diff_type | dolt_conflict_id       |
  +----------------------------------+---------+---------+--------+--------+---------------+----------+----------+-----------------+------------------------+
  | us2uaji20dj77cnf1i2l698itr5392se | 1       | one     | NULL   | NULL   | removed       | 1        | uno      | modified        | B1dLxGtGIlHRKJo88tigpw |
  +----------------------------------+---------+---------+--------+--------+---------------+----------+----------+-----------------+------------------------+
  > call dolt_conflicts_resolve('--theirs', 't');
  +--------+
  | status |
  +--------+
  | 0      |
  +--------+
  > call dolt_add('t');
  > call dolt_rebase('--continue');
  +--------+-----------------------------------------------------+
  | status | message                                             |
  +--------+-----------------------------------------------------+
  | 0      | Successfully rebased and updated refs/heads/branch1 |
  +--------+-----------------------------------------------------+

  Customer issue: #7820

go-mysql-server

• 2644: Fix Integration Workflow
  This makes a few changes to how the integration workflow is handled:
  • main is now merged into this PR before testing. Previously, we would just use the PR as-is, which created a situation where the main branch included changes that the PR had not yet merged in. This created an issue as Dolt and DoltgreSQL would expect some changes to be present that were not yet merged into the PR, causing compilation errors. By merging main, we bypass this. In addition, the workflow will automatically pass if a merge conflict is detected. I think this is fine, since conflicts must be resolved before the PR can be merged anyway. This does mean that some errors may not be caught for as long as merge conflicts against main exist.
  • We only pulled comments, and the PR description does not count as a comment. This made it seem a bit inconsistent with how PR detection was handled. This has now been added, and we're now doing a basic string search instead of using a JSON parser, as concatenating the comments and description does not result in a valid JSON object.
  • Workflow should automatically run when a comment is added, modified, or deleted. This was already supposed to happen, but the event structure differed between a comment and push in a subtle way, causing the workflow to immediately error and for the UI to continue displaying the previous run. This made it seem as though the workflow did not respond to comment updates.
  • Additional logging messages have been added, so it's easier to debug if something goes wrong in the future.
• 2641: Correctly handle indexes on virtual columns
  Fixes #8276
  Lots of small behaviors around virtual columns were not working correctly:
  • Adding an index on a virtual column triggered a table rebuild even when this wasn't necessary
  • Rebuilding a table that contained virtual columns could lead to incorrect results
  • Inserting into a table with a virtual column could update indexes incorrectly
  • Adding a generated column to the start of a table could lead to incorrect results
    This PR adds tests for these cases and fixes them by tweaking the logic for projections on tables with generated columns.

Closed Issues

• 8308: Empty result set from query with INNER JOIN + WHERE

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.07	0.65	0.3
groupby_scan	13.7	16.71	1.2
index_join	1.37	2.66	1.9
index_join_scan	1.3	2.11	1.6
index_scan	34.95	54.83	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.49	5.77	1.7
select_random_points	0.34	0.67	2.0
select_random_ranges	0.39	0.69	1.8
table_scan	34.95	54.83	1.6
types_table_scan	75.82	144.97	1.9
reads_mean_multiplier			1.6

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	8.13	5.99	0.7
oltp_insert	3.82	2.97	0.8
oltp_read_write	8.58	11.87	1.4
oltp_update_index	3.89	3.02	0.8
oltp_update_non_index	3.89	2.97	0.8
oltp_write_only	5.37	6.09	1.1
types_delete_insert	7.7	6.43	0.8
writes_mean_multiplier			0.9

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	98.27	39.08	2.5
tpcc_tps_multiplier			2.5

Overall Mean Multiple	1.67

1.42.16

Merged PRs

dolt

• 8300: Fix nil panic in using dolt show to inspect secondary indexes.
• 8299: More consistent return types for dolt procedures
  Most procedures that return integers return int64, but there are a few exceptions. The procedures that return int break in doltgres here
• 8297: dolt_workspace_* update and delete support
  This change adds the ability to update dolt_workspace_ tables. Updates can take two forms:
  1. The "staging" column of the table. may be toggled from it's current state. If setting from false to true, the working value will be written into the staged table. Setting from true to false will remove the row from staging, and leave the value in working as is.
  2. You can delete any row which has a "staged" column of false. This will revert the workspace changes and return them to the original value.
• 8186: Add support for Doltgres indexes
  • dolthub/doltgresql#561

go-mysql-server

• 2641: Correctly handle indexes on virtual columns
  Fixes #8276
  Lots of small behaviors around virtual columns were not working correctly:
  • Adding an index on a virtual column triggered a table rebuild even when this wasn't necessary
  • Rebuilding a table that contained virtual columns could lead to incorrect results
  • Inserting into a table with a virtual column could update indexes incorrectly
  • Adding a generated column to the start of a table could lead to incorrect results
    This PR adds tests for these cases and fixes them by tweaking the logic for projections on tables with generated columns.
• 2638: Change ranges to an interface
  • #8186

Closed Issues

• 8276: Indexes on virtual generated columns generate incorrect results.

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.07	0.65	0.3
groupby_scan	13.22	16.41	1.2
index_join	1.37	2.66	1.9
index_join_scan	1.27	2.11	1.7
index_scan	34.33	54.83	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.49	5.77	1.7
select_random_points	0.34	0.65	1.9
select_random_ranges	0.39	0.69	1.8
table_scan	34.33	54.83	1.6
types_table_scan	75.82	142.39	1.9
reads_mean_multiplier			1.6

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	8.13	5.99	0.7
oltp_insert	3.82	3.02	0.8
oltp_read_write	8.58	11.87	1.4
oltp_update_index	3.89	3.02	0.8
oltp_update_non_index	3.89	2.97	0.8
oltp_write_only	5.37	6.21	1.2
types_delete_insert	7.7	6.43	0.8
writes_mean_multiplier			0.9

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	99.27	39.96	2.5
tpcc_tps_multiplier			2.5

Overall Mean Multiple	1.67

1.42.15

Merged PRs

dolt

• 8296: fix case insensitive column match in kv iter
  When building the keyLookupMapper for the special prolly tree kvIter, we match column names by exact casing. This leads to incorrect lookup indexes.
  The fix was to use lowercase when matching.
  companion pr: dolthub/go-mysql-server#2640
• 8295: Update README.md
• 8292: working?
• 8291: [commands] faster sqldump query scanner
  The default sqldump scanner works as follows:
  • (1) read +4k lines into buffer
  • (2) scan entire buffer for delimiter (starting from index 0)
  • (3) failing to find delimiter, go to step 1
    So if the delimiter is at character 16k, we will execute the loop 4 times. I noticed some maybe weird things about the default buffered reader from a cursory glance that I might be misunderstanding: (i) it copies bytes between at least 3 buffers (src, dst, target), (ii) do double IO for reads (the bufio docs say this, I didn't completely follow where it happens), and (iii) when doubling the buffer capacity reread the initial contents again (tracking the size of the buffer, it seems to increase and then decrease slightly at doubling implements. There might be something else going on here).
    Anyway, this prototype just doesn't do any of that. Just stream the bytes seeking a delimiter.
    Prototype goes from ~10seconds to import 10k lines -> 1 second on the motivating test dataset.

  ---

  Update with tests passing:

  before
  maxhoffman@Maxs-MacBook-Pro ~/D/d/indexer> time dolt sql < doltdump.sql &> log
  ________________________________________________________
  Executed in  276.23 secs    fish           external
  usr time  276.57 secs    0.19 millis  276.57 secs
  sys time    2.70 secs    1.72 millis    2.70 secs
  after:
  maxhoffman@Maxs-MacBook-Pro ~/D/d/indexer> time dolt sql < doltdump.sql &> log
  ________________________________________________________
  Executed in   33.79 secs    fish           external
  usr time   38.18 secs    0.21 millis   38.18 secs
  sys time    0.64 secs    1.78 millis    0.64 secs
  

  ---

  Update after not double-parsing every query:

  maxhoffman@Maxs-MacBook-Pro ~/D/d/indexer> time dolt sql < doltdump.sql &> log
  ________________________________________________________
  Executed in   25.60 secs    fish           external
  usr time   30.31 secs    0.28 millis   30.31 secs
  sys time    0.56 secs    1.21 millis    0.56 secs
  

go-mysql-server

Closed Issues

1.42.14

Merged PRs

dolt

• 8289: User Contribution Fixup
  Revised: #8246
  Fixes: #8221
• 8288: Skip filter iter, with more test fixes
  This is the same as #8242 with one change and new test in GMS dolthub/go-mysql-server#2639. The change prevents string comparisons from skipping range.Matches, because the index range iterators can return false positives/prefix matches.
• 8281: Db/dolt ci events init
• 8246: Fixed grammar typos re issue #8221
  Solved ./go typos as requested in #8221. This was my first-ever big project contribution, even if it's simple, so hope I followed procedure appropriately :)

go-mysql-server

• 2637: Adding a test combining duplicate indexes through create table and alter table
  Additional test case for dolthub/go-mysql-server#2634
• 2635: Fix multi-statements in nested triggers
  changes:
  • have different savepoint names
  • this fixes nested triggers overwriting save points and clearing the same savepoint
  • handle aliases independently for each statement in trigger blocks
  • sync up prepend and scope nesting for triggers
  • wrap applyTrigger rule wraps triggerExecutors over individual statements in BeginEndBlocks
  • this prevents wrapping triggerExecutors over the wrong statements (not matching event or table)
    related: #8213
• 2634: Adding tests for supporting duplicate secondary indexes
  New tests asserting that multiple indexes over the same set of columns can be created on tables.
  #8274 fixes Dolt for these tests to pass.

Closed Issues

• 8221: Fix Grammar Typos in Go Comments
• 8213: nested (delete) triggers have the wrong context in the nested call
• 8250: Permission for the home directory: panic: runtime error: invalid memory address or nil pointer dereference

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.18	0.68	0.3
groupby_scan	12.98	16.41	1.3
index_join	1.39	2.66	1.9
index_join_scan	1.3	2.14	1.6
index_scan	34.95	54.83	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.49	5.77	1.7
select_random_points	0.34	0.65	1.9
select_random_ranges	0.39	0.69	1.8
table_scan	34.95	54.83	1.6
types_table_scan	75.82	144.97	1.9
reads_mean_multiplier			1.6

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	7.98	5.99	0.8
oltp_insert	3.75	2.97	0.8
oltp_read_write	8.58	11.87	1.4
oltp_update_index	3.89	3.02	0.8
oltp_update_non_index	3.89	2.97	0.8
oltp_write_only	5.37	6.09	1.1
types_delete_insert	7.7	6.43	0.8
writes_mean_multiplier			0.9

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	100.08	39.36	2.5
tpcc_tps_multiplier			2.5

Overall Mean Multiple	1.67

1.42.13

Merged PRs

dolt

• 8274: Allow duplicate indexes, to match MySQL behavior
  This change allows Dolt to create multiple indexes on a table to cover the same set of columns, to match MySQL's behavior. While duplicate indexes are not generally useful, some MySQL tooling (e.g. Django) can create duplicate indexes as part of generated schema migration code.
  Test for creating duplicate indexes were added to GMS, in the PR below. I also tested merge behavior with duplicate indexes and confirmed that we already have a guardrail in place to prevent merging multiple indexes that cover the same set of columns, and a test for that guardrail.
  GMS PR: dolthub/go-mysql-server#2634
  Customer issue: #8254
• 8273: ResolveDefaultExpression should return a ColumnDefaultValue instance, because it has information about the type of the column and performs conversions.
  Fixes #8269
  We were removing the ColumnDefaultValue expression node when building secondary indexes, which was a problem when the type of the default value needed to be converted to the column type: it's the ColumnDefaultValue expression that does that conversion.
• 8268: Db/dolt ci events
• 8260: Fixes for dolt installer bash-script
  1. Fixed error message mistakenly implying that dolt can be installed on 32-bit systems
  2. Fixed color printing on bash (and zsh running in bash compatibility mode) installed on an OS with non-GNU coreutils (Alpine Linux, macOS)
  3. Linted install.sh and fixed all the errors and warning that had obvious fixes. The only one left is local error_code="$1" in fail function that should be removed but may be reused for debugging purposes by manually editing the script
• 8253: Add dolt_workspace_* system tables
  This is the read only addition of the dolt_workspace_{table} s. These dynamically generated tables are always relative to HEAD for the given session of the caller. There are no commits as a result, and the schema of the output looks like:
  | ID (int) | STAGED (bool) | DIFF_TYPE (string) | to_A | to_B | ... | from_A | from_B | ... |
  Currently there is no mechanism to update this table directly, but in the future that will make it possible to have fine grain modification of your workspace, similar to git add --patch
• 8242: Skip filterIter match check when a key range is contiguous

go-mysql-server

• 2636: Add additional engine tests for indexes on generated columns.
  These tests verify the correctness of #8273.
  Some of the checks in these tests are skipped because they run afoul of #8275 and #8276
• 2634: Adding tests for supporting duplicate secondary indexes
  New tests asserting that multiple indexes over the same set of columns can be created on tables.
  #8274 fixes Dolt for these tests to pass.
• 2633: handle view resolution when the schema is not defined in SELECT stmt
• 2631: include parent scopes in update and delete triggers
  partially fixes #8213
• 2628: Aggregation/range scan perf edits
  • Resolving aggregation functions checks the integrator function provider first. The interface required us to create an expensive error object to resolve any standard functions. Now we only create the error object if a function name is not found in integrator and standard list.
  • Skip duplicate unused functional dependency tracking in coster.
  • Aggregations with no group by and single scopes can only return one row, so use the sql.QFlagMax1Row shortcut.
    benchmarks here: #8241

Closed Issues

• 8254: Adding a duplicate constraint with a new name overwrites the existing constraint rather than coexisting
• 8269: Panic when creating index on generated column
• 7830: Support --empty flag for handling empty commits during rebase
• 8250: Permission for the home directory: panic: runtime error: invalid memory address or nil pointer dereference

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.07	1.06	0.5
groupby_scan	13.22	17.01	1.3
index_join	1.32	2.66	2.0
index_join_scan	1.25	2.14	1.7
index_scan	34.33	54.83	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.49	5.77	1.7
select_random_points	0.34	0.65	1.9
select_random_ranges	0.39	0.7	1.8
table_scan	34.33	54.83	1.6
types_table_scan	74.46	147.61	2.0
reads_mean_multiplier			1.6

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	8.13	5.99	0.7
oltp_insert	3.82	3.02	0.8
oltp_read_write	8.58	11.87	1.4
oltp_update_index	3.89	3.02	0.8
oltp_update_non_index	3.89	2.97	0.8
oltp_write_only	5.37	6.09	1.1
types_delete_insert	7.7	6.43	0.8
writes_mean_multiplier			0.9

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	96.5	38.71	2.5
tpcc_tps_multiplier			2.5

Overall Mean Multiple	1.67

1.42.11

Merged PRs

dolt

• 8261: liuliu/graph-spaces-fix
• 8259: liuliu/graph-remove-extra-s
• 8258: Bug fix: Testing for invalid global configuration dir permissions earlier, to prevent a panic
  Customer issue: #8250
• 8252: go/store/nbs: Fix table_index for table files with so many chunks that certain index slice operations overflow a uint32.
• 8251: liuliu/color-string-fix
• 8248: support \checkout, \merge, \show
  Until now there was an awkward behavior in dolt sql shell where the \checkout and \merge commands didn't really play nice when they could have. Specifically, if you used dolt sql and it was connected to a remote host, then running \checkout would give you an error telling you to stop the server. That is no longer the case. \checkout and \merge will work well in the dolt sql shell when connected to a remote host now.
  Also added the \show command
  And made the expect tests more correct and expanded.
• 8247: Fix displaying AddressMap non-leaf nodes in noms show
  This fixes an index out of bounds panic when trying to display AddressMap non-leaf nodes in noms show.
  I didn't add a regression test, because it turns out that all noms show tests only work against the old deprecated format, so adding more tests to the test suite wouldn't actually do anything. It's also mostly unused: we basically only use it as part splunk.pl to visualize chunks for debugging.
  Instead of fixing the test suite, it would be a better use of my time to fully deprecate noms show and switch splunk.pl to use dolt show instead. Especially now that #8143 added support for visualizing prolly tree chunks in dolt show, bringing it to feature parity with noms show.
• 8245: Feature: support for --empty=[drop|keep] in dolt_rebase()
  Adds support for the --empty option to dolt_rebase(). This option controls how commits that become empty are handled. For example, if a branch is rebased and all the changes in one commit on that branch have already been applied to the upstream branch, then when that commit is reapplied, it will end up being empty. The two initially supported values are drop and keep. (Git also supports a stop value, which lets the user manually intervene.)
  Also adds support for the --allow-empty flag for dolt_cherry_pick(). This flag controls whether Dolt will cherry-pick empty commits (i.e. commits that start off as empty, not commits that become empty after they are applied).
  These behaviors are slightly confusing for two reasons: 1) Git distinguishes between a commit that starts off empty and a commit that becomes empty while applying its changes, and 2) rebase and cherry-pick have slightly different default values for these two options. The differences are summarized below.

  	Commits that start empty	Commits that become empty
  rebase	default: keep, can be overridden with --no-keep-empty (--keep-empty is also supported)	default: drop, can be overridden with --empty=keep. For interactive rebases, the default changes to stop, which is not supported by Dolt yet.
  cherry-pick	default: fail, can be overridden with --allow-empty	default: stop, can be overridden with --empty=keep or --empty=drop. Dolt does not support stop yet, so Dolt's default is to fail.
  Related issue: #7830

• 8239: Rewrite the dolt show implementation.
  dolt show had an issue where it would not correctly display the SerialMessage for commits if provided with a hash. This came about as part of a refactor to make dolt show not depend on the env.DoltEnv object, when only exists on locally running servers, and not when connected to a remote server. Unfortunately, it looks like that refactor didn't actually remove the dependency either, as DoltEnv was still used in every possible invocation of dolt show
  To get it working, I essentially rewrote the implementation of dolt show in such a way that it now actually only uses DoltEnv when it can't get the necessary information from a running server: Basically, if we need to display SerialMessages or resolve branch names, we still rely on a locally running server. This can likely be improved in the future. But calls like dolt show #hash should now work against remote servers.
• 8226: Liuliu/log one line graph
  An example of the graph of us-jails:
  
• 8222: [kvexec] customized operator for count aggregation
  Operators that count the number of rows in a relation don't have to deserialize those KV's from storage (ex: select count(y) from xy where x > 1). There are some circumstances where we have to check for field nullability, but otherwise we can just count the KV's returned by the source iterator.
• 8129: Add optimized diffing and three-way merge of indexed JSON Documents.
  This PR adds some additional tests, but I plan on adding more tests around large documents before merging. Still, the implementation is ready for review.
  This adds a new JSON diffing algorithm designed for IndexedJSONDocument. Because three way merge only operates on values read from a Dolt table, which are always returned as a IndexedJSONDocuemt, this should mean that the original implementation is no longer used.

go-mysql-server

• 2629: normalize column defaults
  This PR adds a new analyzer rule to normalize literal column default values.
  This rule ensures that the default value is consistent for the column type (float defaults over int columns are rounded properly).
  It does this by evaluating the column default, and placing that into a NewLiteral of the proper type.
  Additionally, this ensures that dolt serialization receives consistent values (normalized floats and proper types).
  fixes: #8190
• 2627: Fix error when comparing incompatible types in IndexLookups
  When building lookups for IndexedTableAccess, we always convert the key type to the columns type.
  This is problematic when the key can't be converted to the column type without error.
  The expressions used in Filters properly handle this conversion, so we should default to that.
  Example:

  tmp/main*> create table t (i int primary key);
  tmp/main*> select * from t where i = json_array();
  error: '[]interface {}' is not a valid value type for 'int'

  This doesn't errror in MySQL. Also without a primary key or secondary index, the query succeeds in dolt.
• 2625: Bug fix: the timestamp function should convert to a datetime type
  MySQL's timestamp function, despite its name, actually returns a datetime type and not a timestamp type.
  MySQL example:

  mysql -uroot --protocol TCP -e "select timestamp('1000-01-01 00:00:00');" --column-type-info
  Field   1:  `timestamp('1000-01-01 00:00:00')`
  Catalog:    `def`
  Database:   ``
  Table:      ``
  Org_table:  ``
  Type:       DATETIME
  Collation:  binary (63)
  Length:     19
  Max_length: 19
  Decimals:   0
  Flags:      BINARY
  +----------------------------------+
  | timestamp('1000-01-01 00:00:00') |
  +----------------------------------+
  | 1000-01-01 00:00:00              |
  +----------------------------------+

  Note: We still need to add support for the second, optional parameter to timestamp().
  Customer issue: #8236

Closed Issues

• 4367: Add support for --graph option in dolt log
• 8190: Table schema stores unnormalized expression for default values, which leads to unexpected behaviors.
• 8236: Issue with datetime(6) column using timestamp(6) range

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.07	1.16	0.6
groupby_scan	12.98	16.71	1.3
index_join	1.34	2.66	2.0
index_join_scan	1.27	2.14	1.7
index_scan	34.33	55.82	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.43	5.88	1.7
select_random_points	0.33	0.65	2.0
select_random_ranges	0.39	0.81	2.1
table_scan	34.95	55.82	1.6
types_table_scan	75.82	144.97	1.9
reads_mean_multiplier			1.7

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	7.98	5.88	0.7
oltp_insert	3.75	2.97	0.8
oltp_read_write	8.43	11.87	1.4
oltp_update_index	3.82	2.9...

1.42.10

Merged PRs

dolt

go-mysql-server

• 2625: Bug fix: the timestamp function should convert to a datetime type
  MySQL's timestamp function, despite its name, actually returns a datetime type and not a timestamp type.
  MySQL example:

  mysql -uroot --protocol TCP -e "select timestamp('1000-01-01 00:00:00');" --column-type-info
  Field   1:  `timestamp('1000-01-01 00:00:00')`
  Catalog:    `def`
  Database:   ``
  Table:      ``
  Org_table:  ``
  Type:       DATETIME
  Collation:  binary (63)
  Length:     19
  Max_length: 19
  Decimals:   0
  Flags:      BINARY
  +----------------------------------+
  | timestamp('1000-01-01 00:00:00') |
  +----------------------------------+
  | 1000-01-01 00:00:00              |
  +----------------------------------+

  Note: We still need to add support for the second, optional parameter to timestamp().
  Customer issue: #8236
• 2624: Use ctx.Done() as a faster check for ctx.Err()
  The err call is noticeable for queries that read a lot of rows.
• 2623: Fix anti-join correctess bug
  We had some strange logic for accepting a join anti-match, ripped it out and everything seems to be working correctly now.
• 2621: implement icu_version function
  MySQL Docs: https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_icu-version
• 2619: Assume text index comparisons are exact
  We currently do not eliminate filters of the form column(VARCHAR) = text literal (longtext) when pushing filters into index lookups. The safety check is necessary at least for datetimes, spatial/fulltext and partial TEXT indexes. It's not clear whether it is necessary for full varchar indexes.
  dolt side seems OK: #8218

Closed Issues

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.07	2.86	1.4
groupby_scan	13.22	17.01	1.3
index_join	1.34	2.66	2.0
index_join_scan	1.27	2.11	1.7
index_scan	34.33	53.85	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.49	5.88	1.7
select_random_points	0.34	0.65	1.9
select_random_ranges	0.39	0.83	2.1
table_scan	34.33	54.83	1.6
types_table_scan	74.46	142.39	1.9
reads_mean_multiplier			1.7

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	8.13	5.99	0.7
oltp_insert	3.82	2.97	0.8
oltp_read_write	8.58	12.08	1.4
oltp_update_index	3.89	3.02	0.8
oltp_update_non_index	3.89	2.97	0.8
oltp_write_only	5.37	6.09	1.1
types_delete_insert	7.7	6.43	0.8
writes_mean_multiplier			0.9

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	98.97	39.23	2.5
tpcc_tps_multiplier			2.5

Overall Mean Multiple	1.70

1.42.9

Merged PRs

dolt

• 8219: Bug fix: events should remain enabled after sql-server restarts
  Fixing an event scheduler bug that caused events to be disabled and not run when the sql-server is restarted.
  Fixes #8167
• 8209: add constraint violations to dolt status
  fixes: #8114

go-mysql-server

• 2623: Fix anti-join correctess bug
  We had some strange logic for accepting a join anti-match, ripped it out and everything seems to be working correctly now.
• 2620: implement name_const function
  MySQL docs: https://dev.mysql.com/doc/refman/8.4/en/miscellaneous-functions.html#function_name-const
• 2618: More aggressively elide IN filters used for indexed lookups
  re: #8215
• 2617: More QueryProps, missed max1rowiter usage
• 2613: Query properties rule filtering
  Edit most of the analyzer interfaces to pass a new context object that accumulates query specific properties. Currently the object is called QueryFlags, and accumulates information about the query to inform better rule filtering and more efficient spooling strategies.
  The change that has the biggest effect on oltp_point_select perf is the sql.QFlagMax1Row setting, which lets us skip the default results iter boilerplate when we're only returning one row. Added a couple other skips for rules that are easy to whitelist correctly and show prominently on CPU profiles, like aggregations and subqueries.

vitess

• 361: adding instant as non-reserved keyword
  The INSTANT keyword isn't in the MySQL docs, but it is a non reserved keyword.
  MySQL Docs: https://dev.mysql.com/doc/refman/8.4/en/keywords.html
  fixes: #8220
• 360: Bug fix: Preserve sign for integers in prepared statements
  Bound integer values for prepared statements are parsed from the wire and packaged into int64 values that are then passed to the SQL engine to execute with the prepared statement. For int8, int16, int24, and int32 types those bytes from the wire weren't getting cast to the correct type first, before they were cast to int64, which meant if the signed bit was set, the value was interpreted incorrectly.
  Customer issue: #8085

Closed Issues

• 8220: Correctness Issue, queries run differently on MySQL and Dolt
• 8206: Unknown JSON struct tag in schema_marshaling.go
• 8167: Scheduled events fail to execute after a server restart
• 8114: dolt_status table unhelpful during merge when there are constraint violations.

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.07	3.02	1.5
groupby_scan	13.7	17.63	1.3
index_join	1.37	2.66	1.9
index_join_scan	1.3	2.18	1.7
index_scan	34.95	54.83	1.6
oltp_point_select	0.18	0.3	1.7
oltp_read_only	3.49	5.99	1.7
select_random_points	0.34	0.65	1.9
select_random_ranges	0.39	0.83	2.1
table_scan	34.95	55.82	1.6
types_table_scan	75.82	144.97	1.9
reads_mean_multiplier			1.7

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	8.13	5.99	0.7
oltp_insert	3.82	3.02	0.8
oltp_read_write	8.58	12.08	1.4
oltp_update_index	3.89	3.02	0.8
oltp_update_non_index	3.89	2.97	0.8
oltp_write_only	5.47	6.21	1.1
types_delete_insert	7.7	6.55	0.9
writes_mean_multiplier			0.9

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	98.12	38.64	2.5
tpcc_tps_multiplier			2.5

Overall Mean Multiple	1.70

1.42.8

Merged PRs

dolt

• 8211: Bug fix: binlog replication: logging levels, use MySQL's older date serialization format
  Small bug fixes for binlog replication while testing with the python-mysql-replication library:
  • Adjusting DEBUG logging to be less verbose
  • Using MySQL's older date serialization format for compatibility
  • Adding a nil check for gtidPosition
• 8207: removing unconditional recursive calls
  fixes:
  • #8206
  • #8205
  • #8204
  • #8203
  • #8202
• 8201: chore: update lib:eventsapi 2022-> 2024
  Ran into issues when I ran:

  go install github.com/dolthub/dolt/go/cmd/dolt@main
  # github.com/dolthub/dolt/go/cmd/dolt/commands
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/cherry-pick.go:79:19: undefined: eventsapi.ClientEventType_CHERRY_PICK
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/dump.go:112:19: undefined: eventsapi.ClientEventType_DUMP
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/filter-branch.go:95:19: undefined: eventsapi.ClientEventType_FILTER_BRANCH
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/gc.go:76:19: undefined: eventsapi.ClientEventType_GARBAGE_COLLECTION
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/migrate.go:74:19: undefined: eventsapi.ClientEventType_MIGRATE
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/profile.go:89:19: undefined: eventsapi.ClientEventType_PROFILE
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/rebase.go:74:19: undefined: eventsapi.ClientEventType_REBASE
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/reflog.go:63:19: undefined: eventsapi.ClientEventType_REFLOG
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/show.go:72:19: undefined: eventsapi.ClientEventType_SHOW
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/tag.go:81:19: undefined: eventsapi.ClientEventType_TAG
  /go/pkg/mod/github.com/dolthub/dolt/[email protected]/cmd/dolt/commands/tag.go:81:19: too many errors
  

  Realized the eventsapi library version in go.mod is outdated
• 8187: Feature: Auto-start binlog replication on server restart
  Once replication has been started with START REPLICA;, replication now automatically restarts when the server is restarted. If replication is stopped with STOP REPLICA;, it is not restarted automatically on server restart. This matches MySQL's behavior.
  This PR also includes a few other small improvements to binlog replication:
  • allows 'show replica status' before replication is started
  • moves logging of binlog messages from DEBUG to TRACE level
  • removes several references to GetRunningServer() global function
    Resolves #8168
• 8184: [dtables] fix from_commit index behavior
• 8182: go/libraries/doltcore/env/actions: Improve error message when a clone fails mid-download.
  Fixes a dropped error return in the clone implementation.
• 8181: Bug fix: resolving case-sensitive table name for TableEditor
  A MySQL primary server may send table names in TableMap binlog events that do not match the case of the table name. We use a case-insensitive table name lookup when getting the table's schema, but not when creating the TableWriter that applies row updates/inserts/deletes. This change fixes that so that mixed case table names can replicate correctly.
• 8177: Bump fast-xml-parser and @aws-sdk/client-ses in /.github/actions/ses-email-action
  Bumps fast-xml-parser to 4.4.1 and updates ancestor dependency @aws-sdk/client-ses. These dependencies need to be updated together.
  Updates fast-xml-parser from 4.2.5 to 4.4.1
  Changelog

  Sourced from fast-xml-parser's changelog.

  Note: If you find missing information about particular minor version, that version must have been changed without any functional change in this library.

  4.4.1 / 2024-07-28

  • v5 fix: maximum length limit to currency value
  • fix #634: build attributes with oneListGroup and attributesGroupName (#653)(By Andreas Naziris)
  • fix: get oneListGroup to work as expected for array of strings (#662)(By Andreas Naziris)

  4.4.0 / 2024-05-18

  • fix #654: parse attribute list correctly for self closing stop node.
  • fix: validator bug when closing tag is not opened. (#647) (By Ryosuke Fukatani)
  • fix #581: typings; return type of tagValueProcessor & attributeValueProcessor (#582) (By monholm)

  4.3.6 / 2024-03-16

  • Add support for parsing HTML numeric entities (#645) (By Jonas Schade )

  4.3.5 / 2024-02-24

  • code for v5 is added for experimental use

  4.3.4 / 2024-01-10

  • fix: Don't escape entities in CDATA sections (#633) (By wackbyte)

  4.3.3 / 2024-01-10

  • Remove unnecessary regex

  4.3.2 / 2023-10-02

  • fix jObj.hasOwnProperty when give input is null (By Arda TANRIKULU)

  4.3.1 / 2023-09-24

  • revert back "Fix typings for builder and parser to make return type generic" to avoid failure of existing projects. Need to decide a common approach.

  4.3.0 / 2023-09-20

  • Fix stopNodes to work with removeNSPrefix (#607) (#608) (By [Craig Andrews]https://github.com/candrews))
  • Fix #610 ignore properties set to Object.prototype
  • Fix typings for builder and parser to make return type generic (By Sarah Dayan)

  4.2.7 / 2023-07-30

  • Fix: builder should set text node correctly when only textnode is present (#589) (By qianqing)
  • Fix: Fix for null and undefined attributes when building xml (#585) (#598). A null or undefined value should be ignored. (By Eugenio Ceschia)

  4.2.6 / 2023-07-17

  • Fix: Remove trailing slash from jPath for self-closing tags (#595) (By Maciej Radzikowski)

  4.2.5 / 2023-06-22

  • change code implementation

  4.2.4 / 2023-06-06

  • fix security bug

  4.2.3 / 2023-06-05

  • fix security bug

  ... (truncated)

  Commits

  • d40e29c update package d...

1.42.7

Merged PRs

dolt

• 8174: check for pg_catalog table when resolving a table
  Fixes:
  dolthub/doltgresql#442
  dolthub/doltgresql#513
• 8172: Reveal dolt archive command
  Move the dolt admin archive command to dolt archive
• 8164: archive guard rails
  Add features to ensure that archive files are not used when running a remotesrv or when pushing backups.
  Also provide the --revert flag which can return a database back to the classic format.
• 8137: Dolt index searchable, lookup caching
  Two main changes
  • cache schema hashes on root values, so that we don't have to a table lookup to get a schema hash
  • Implement sql.IndexSearchable in a way that we cache strict key lookups for a given table schema. The lifecycle for these objects is the span between ALTER TABLE statements

go-mysql-server

• 2610: use datetime precision in information_schema.columns.datetime_precision
  When determining if a schema change occurred, one of the tables Prisma looks at is the information_schema.columns.
  Here, we incorrectly mark all datetime and timestamp columns as with a precision of 6.
  If a table has type DATETIME(3), Prisma would think there was a schema change, and perform a migration when one isn't needed.
  This PR addresses this issue by having the information_schema.columns table accurately reflect the datetime preicision of the columns,
  fixes: #8173
• 2604: Index searchable edits
  We previously added support for integrators choosing their own indexes with an sql.IndexSearchable interface. This was for a customer use case. This PR expands the interface to let Dolt cache information about strict key lookups.
  The motivation is that (1) strict key lookups will always be the best-case scenario result of index costing, (2) caching this information in-between ALTER statements is usually a long enough lifecycle for the overhead to be worth it.
  I added a streamlined range builder as part of this optimization that only accepts literal values in the order expected by the target lookup. The user of this range builder takes responsibility for feeding the values in the correct order. As a result, we sidestep expensive string formatting, map creating, and map lookups during range building.
  Follow-on fixes to functional dependencies permuted plans a bit more. Inner joins are chosen more frequently in some of our test plans now that we're reflecting strict key max-1-row cardinalities.

vitess

• 360: Bug fix: Preserve sign for integers in prepared statements
  Bound integer values for prepared statements are parsed from the wire and packaged into int64 values that are then passed to the SQL engine to execute with the prepared statement. For int8, int16, int24, and int32 types those bytes from the wire weren't getting cast to the correct type first, before they were cast to int64, which meant if the signed bit was set, the value was interpreted incorrectly.
  Customer issue: #8085
• 359: fix detection of multi-statements in ComPrepare
  Currently, preparing multi-statements is not supported; so we can't prepare a query like select ?; select ?;.
  However, the check for this condition just looked for any characters after the first ;, which meant that queries like select ?; \n would incorrectly throw an error.
  This was made apparent using the Prisma ORM, which runs the query:

  SELECT TABLE_NAME AS view_name, VIEW_DEFINITION AS view_sql
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_SCHEMA = ?;

  The above query ends in a newline character.
  The fix is to use SplitStatementToPieces(), which trims these white space characters, and check if there's exactly one piece; this was taken from the vitessio repo: https://github.com/vitessio/vitess/blob/main/go/mysql/conn.go#L1204
  fixes #8157

Closed Issues

• 4127: Support for date and time literals
• 8173: prisma migrate dev, generating changes to columns of the date type in loop
• 7200: Weird behavior for datetime literal syntax.
• 8157: Error when using Prisma ORM: "Error: unknown error: can not prepare multiple statements"

Performance

Read Tests	MySQL	Dolt	Multiple
covering_index_scan	2.11	3.02	1.4
groupby_scan	13.22	17.01	1.3
index_join	1.34	2.81	2.1
index_join_scan	1.27	2.22	1.7
index_scan	38.25	54.83	1.4
oltp_point_select	0.18	0.44	2.4
oltp_read_only	3.49	7.56	2.2
select_random_points	0.34	0.78	2.3
select_random_ranges	0.39	0.92	2.4
table_scan	38.94	55.82	1.4
types_table_scan	80.03	144.97	1.8
reads_mean_multiplier			1.9

Write Tests	MySQL	Dolt	Multiple
oltp_delete_insert	8.13	6.09	0.7
oltp_insert	3.82	3.02	0.8
oltp_read_write	8.58	13.7	1.6
oltp_update_index	3.89	3.07	0.8
oltp_update_non_index	3.89	3.02	0.8
oltp_write_only	5.47	6.32	1.2
types_delete_insert	7.7	6.67	0.9
writes_mean_multiplier			1.0

TPC-C TPS Tests	MySQL	Dolt	Multiple
tpcc-scale-factor-1	99.65	36.43	2.7
tpcc_tps_multiplier			2.7

Overall Mean Multiple	1.87