I ran into an issue attempting to load a csv file, which unusually hadn't happened previously despite identical steps taken

After doing some investigation, it seems that new tables created with the below schema definition are not allowing me to import the attached csv. What is strange is that this previously worked fine, and I have located a particular branch on one of the test repos I had set up, where despite an identical schema the csv still imports.

Repo: geweldon/Source3 branch with error: main branch that still somehow works: history5

//Schema SBQQ__ProductOption__c @ working CREATE TABLE SBQQ__ProductOption__c ( Id varchar(18), OwnerId varchar(18), SBQQ__AppliedImmediatelyContext__c varchar(255), SBQQ__AppliedImmediately__c tinyint DEFAULT "0", SBQQ__Bundled__c tinyint DEFAULT "0", SBQQ__ComponentCodePosition__c decimal(6,0), SBQQ__ComponentCode__c varchar(60), SBQQ__ComponentDescriptionPosition__c decimal(6,0), SBQQ__ComponentDescription__c varchar(255), SBQQ__ConfiguredSKU__c varchar(18), SBQQ__DefaultPricingTable__c varchar(255), SBQQ__DiscountAmount__c decimal(14,2), SBQQ__DiscountSchedule__c varchar(18), SBQQ__Discount__c decimal(8,2), SBQQ__DiscountedByPackage__c tinyint DEFAULT "0", SBQQ__ExistingQuantity__c decimal(12,2), SBQQ__Feature__c varchar(18), SBQQ__MaxQuantity__c decimal(12,2), SBQQ__MinQuantity__c decimal(12,2), SBQQ__Number__c decimal(5,0), SBQQ__OptionalSKU__c varchar(18), SBQQ__QuantityEditable__c tinyint DEFAULT "0", SBQQ__Quantity__c decimal(12,2), SBQQ__QuoteLineVisibility__c varchar(255), SBQQ__RenewalProductOption__c varchar(18), SBQQ__Required__c tinyint DEFAULT "0", SBQQ__Selected__c tinyint DEFAULT "0", SBQQ__SubscriptionScope__c varchar(255), SBQQ__System__c tinyint DEFAULT "0", SBQQ__Type__c varchar(255) DEFAULT "Component", SBQQ__UnitPrice__c decimal(14,2), SBQQ__UpliftedByPackage__c tinyint DEFAULT "0", CPQ_Custom_1__c tinyint DEFAULT "1", CPQ_Custom_2__c tinyint DEFAULT "0", Custom_Picklist__c varchar(255), Example_Record__c tinyint DEFAULT "0", Pre_Existing__c tinyint DEFAULT "0", ProdRecordSeedExtID__c varchar(255), ATGExtId__c varchar(255), ATGSourceId__c varchar(255) NOT NULL, PRIMARY KEY (ATGSourceId__c), KEY Id (Id), KEY OwnerId (OwnerId), KEY SBQQ__ConfiguredSKU__c (SBQQ__ConfiguredSKU__c), KEY SBQQ__DiscountSchedule__c (SBQQ__DiscountSchedule__c), KEY SBQQ__Feature__c (SBQQ__Feature__c), KEY SBQQ__OptionalSKU__c (SBQQ__OptionalSKU__c), KEY SBQQ__RenewalProductOption__c (SBQQ__RenewalProductOption__c) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

partner_org___dep_train_3-backup-sbqq__productoption__c-20220413205903.csv

I start a new server and I can connect to mysql with cli. But when I create a connect to mysql with navicat, it report an error that Excuse me, why is this?

Customer is trying to use StarRocks DB:

https://github.com/StarRocks/starrocks

They are able connect but apparently as of queries don't work. This might need a change to their code.

Harness and CI for testing system table performance. Scripts are codegen'd in https://github.com/dolthub/systab-sysbench-scripts. We compare native system tables to materialized versions with logically equivalent rows.

+-------------------------------------+-----------+----------+-------+
| name                                | mean_mult | med_mult | stdd  |
+-------------------------------------+-----------+----------+-------+
| dolt_commit_ancestors_commit_filter | 6.89      | 7.65     | 0.377 |
| dolt_commits_commit_filter          | 6.08      | 6.74     | 0.311 |
| dolt_diff_log_join_on_commit        | 1.39      | 1.36     | 5.834 |
| dolt_diff_table_commit_filter       | 37.77     | 40.8     | 1.756 |
| dolt_diffs_commit_filter            | 25.8      | 29.53    | 0.738 |
| dolt_history_commit_filter          | 25.52     | 27.95    | 0.799 |
| dolt_log_commit_filter              | 4.64      | 5.14     | 0.401 |
+-------------------------------------+-----------+----------+-------+

Trying to fetch non-HEAD commits produces an error if schema has been changed when using the doltr R package.

Error: The number of parameters in bound buffers differs from number of columns in resultset [2057]

doltr issue #46

I'll post dolt server logs this evening

I have a GROUP BY query with multiple JOINs which runs quite slow in Dolt (~90 sec) compared to MySQL (~400 ms) when executed against the data in https://www.dolthub.com/repositories/knutwannheden/lobbywatch:

select rechtsform,
       count(1)                                                  anz,
       count(i.id)                                               anz_bind,
       count(distinct pa.id)                                     anz_parl,
       count(m.id)                                               anz_mand,
       count(distinct p.id)                                      anz_pers,
       count(case when i.id is null and m.id is null then 1 end) anz_ohne
from organisation o
         left join interessenbindung i
                   on o.id = i.organisation_id and now() between coalesce(i.von, now()) and coalesce(i.bis, now())
         left join parlamentarier pa
                   on pa.id = i.parlamentarier_id and now() between pa.im_rat_seit and coalesce(pa.im_rat_bis, now())
         left join mandat m on o.id = m.organisation_id and now() between coalesce(m.von, now()) and coalesce(m.bis, now())
         left join person p on p.id = m.person_id and now() >= coalesce(p.zutrittsberechtigung_von, now())
group by rechtsform
order by 2 desc
;

Here is the link to the named query ("slow analytics query"): https://www.dolthub.com/repositories/knutwannheden/lobbywatch/query/master?q=select+rechtsform%2C++++++++count%281%29++++++++++++++++++++++++++++++++++++++++++++++++++anz%2C++++++++count%28i.id%29+++++++++++++++++++++++++++++++++++++++++++++++anz_bind%2C++++++++count%28distinct+pa.id%29+++++++++++++++++++++++++++++++++++++anz_parl%2C++++++++count%28m.id%29+++++++++++++++++++++++++++++++++++++++++++++++anz_mand%2C++++++++count%28distinct+p.id%29++++++++++++++++++++++++++++++++++++++anz_pers%2C++++++++count%28case+when+i.id+is+null+and+m.id+is+null+then+1+end%29+anz_ohne+from+organisation+o++++++++++left+join+interessenbindung+i++++++++++++++++++++on+o.id+%3D+i.organisation_id+and+now%28%29+between+coalesce%28i.von%2C+now%28%29%29+and+coalesce%28i.bis%2C+now%28%29%29++++++++++left+join+parlamentarier+pa++++++++++++++++++++on+pa.id+%3D+i.parlamentarier_id+and+now%28%29+between+pa.im_rat_seit+and+coalesce%28pa.im_rat_bis%2C+now%28%29%29++++++++++left+join+mandat+m+on+o.id+%3D+m.organisation_id+and+now%28%29+between+coalesce%28m.von%2C+now%28%29%29+and+coalesce%28m.bis%2C+now%28%29%29++++++++++left+join+person+p+on+p.id+%3D+m.person_id+and+now%28%29+%3E%3D+coalesce%28p.zutrittsberechtigung_von%2C+now%28%29%29+group+by+rechtsform+order+by+2+desc+%3B&active=Queries

Specifying the -u command line parameter no longer works. Dolt version 0.40.25 . I kept working my way backward through earlier releases to finally make it work as documented (version .39.5)

 Main PID: 30022 (dolt)
   CGroup: /system.slice/doltdb.service
           └─30022 /usr/local/bin/dolt sql-server -H10.1.6.210 -uroot

We observed a strange problem where a view we had established to union three long running tables suddenly started always referring to HEAD when using as of. After some exploration we found out that this occurs when a view is dropped and then added back in. It appears that a newly created view can't use as of to reach further back into the commit history than it's own creation commit.

I also wonder if this is a more general issue with as of. For example, we've been dropping tables then immediately re-adding them back in without an intervening commit in order to handle changes to the schema. This was a cool trick but can dolt's as of handle that? Or will it stop at the last time a table was added?

reprex:

dolt sql -q "create table test (pk int, c1 int, primary key(pk))" 
dolt sql -q "insert into test values (1,2), (2,4), (3,6)"
dolt commit -am "create test table and add values"
dolt sql -q "select * from test" 

dolt sql -q "create view test_view as select * from test where c1 < 3"
dolt sql -q "select * from test_view" 

dolt sql -q "insert into test values (4,1), (5,3), (6,5)"
dolt commit -am "add some more values"

dolt sql -q "select * from test" 
dolt sql -q "select * from test_view as of 'HEAD'"
dolt sql -q "select * from test_view as of 'HEAD^'"

dolt sql -q "alter table test add column c2 int"
dolt commit -am "Added column c2"
dolt sql -q "select * from test_view as of 'HEAD'"
dolt sql -q "select * from test_view as of 'HEAD^'"
dolt sql -q "select * from test_view as of 'HEAD~2'"

dolt sql -q "drop view test_view"
dolt commit -am "drop test_view"

dolt sql -q "create view test_view as select * from test where c1 < 3"
dolt commit -am "re-add test_view"
dolt sql -q "select * from test_view as of 'HEAD'"
dolt sql -q "select * from test_view as of 'HEAD^'"
dolt sql -q "select * from test_view as of 'HEAD~2'" 

So diving into updating tables from a csv file via the dolt command line. I imported and created a series of tables from some csv exports from another database, and then attempted to update using a comparable but unique csv. However I keep running into this error "Error creating reader for csv file:partner_org___dep_train_3-backup-sbqq__productoption__c-20220308222108.csv." I have included the full set of commands and results below, but I am baffled as to what is going on here.

I am able to to import the csv's in question using the dolthub ui, and I can import them using the create option in an empty branch, but trying to update a table just keeps giving me an error.

grantweldon@MacbookPros-MacBook-Pro Org-record-backup-3 % dolt ls
Tables in working set: partner_org___dep_train_3-backup-product2 partner_org___dep_train_3-backup-sbqq__customaction__c partner_org___dep_train_3-backup-sbqq__priceaction__c partner_org___dep_train_3-backup-sbqq__pricecondition__c partner_org___dep_train_3-backup-sbqq__pricerule__c partner_org___dep_train_3-backup-sbqq__productfeature__c partner_org___dep_train_3-backup-sbqq__productoption__c

grantweldon@MacbookPros-MacBook-Pro Org-record-backup-3 % dolt table import -u partner_org___dep_train_3-backup-sbqq__productoption__c partner_org___dep_train_3-backup-sbqq__productoption__c-20220308222108.csv

Error creating reader for csv file:partner_org___dep_train_3-backup-sbqq__productoption__c-20220308222108.csv. cause: syntax error at position 41 near 'partner_org___dep_train_3' When attempting to move data from csv file:partner_org___dep_train_3-backup-sbqq__productoption__c-20220308222108.csv to partner_org___dep_train_3-backup-sbqq__productoption__c, could not open a reader.

grantweldon@MacbookPros-MacBook-Pro Org-record-backup-3 % dolt table import -r partner_org___dep_train_3-backup-sbqq__productoption__c partner_org___dep_train_3-backup-sbqq__productoption__c-20220308222108.csv Error creating reader for csv file:partner_org___dep_train_3-backup-sbqq__productoption__c-20220308222108.csv.

Autocommit (on by default) prevents transactions from executing when conflicts exists. This causes problems from select * from dolt_conflicts query which is needed to resolve said queries.

Repro below

create database test_db;
use test_db;

select dolt_checkout('main');
create table state_populations (state varchar(14) not null,population int,rate double,primary key (state));
insert into state_populations (state, population, rate) values ('Delaware', 59096, 0.98),('New Hampshire', 141885, 1.007);
select dolt_commit('-a', '-m', 'initial table for merge conflict path');
select dolt_checkout('-b', 'mybranch_2', 'main');
select dolt_checkout('-b', 'mybranch_1', 'main');

update state_populations set population = 200000 where state like 'New%';
select dolt_commit('-a', '-m', 'commit data to mybranch_1');
select dolt_checkout('main');
select dolt_merge('mybranch_1');
select dolt_commit('-m', 'dolt_commit merge mybranch_1');

select dolt_checkout('mybranch_2');

update state_populations set population = 300000 where state like 'New%';
select dolt_commit('-a', '-m', 'commit data to mybranch_2');
select dolt_checkout('main');
select dolt_merge('mybranch_2');
select * from dolt_conflicts;

Currently the maximum size for a VARCHAR is 16383 and one needs to specify VARCHAR(16383) for it. It would be nice to support the VARCHAR(MAX) syntax so one doesn't have to remember the number.

Bumps github.com/aws/aws-sdk-go from 1.32.6 to 1.33.0.

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.

schema:

CREATE TABLE `image_labels` (
  `image_id` int NOT NULL,
  `label` varchar(100) NOT NULL,
  `top_left_x` int,
  `top_left_y` int,
  `bottom_right_x` int,
  `bottom_right_y` int,
  PRIMARY KEY (`image_id`,`label`),
  CONSTRAINT `chk_esd5b974` CHECK ((`top_left_x` < `bottom_right_x`)),
  CONSTRAINT `chk_r4sbrdks` CHECK ((`top_left_y` < `bottom_right_y`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

image_labels2 @ working
CREATE TABLE `image_labels2` (
  `image_id` int NOT NULL,
  `label` varchar(100) NOT NULL,
  `top_left_x` int,
  `top_left_y` int,
  `bottom_right_x` int,
  `bottom_right_y` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

When running:

INSERT INTO image_labels 
SELECT * from image_labels2 
ON DUPLICATE KEY UPDATE 
image_labels.top_left_x = image_labels2.top_left_x,
image_labels.top_left_y = image_labels2.top_left_y,
image_labels.bottom_right_x = image_labels2.bottom_right_x,
image_labels.bottom_right_y = image_labels2.bottom_right_y;

I get:

error on line 1 for query INSERT INTO image_labels
SELECT * from image_labels2
ON DUPLICATE KEY UPDATE
image_labels.top_left_x = image_labels2.top_left_x,
image_labels.top_left_y = image_labels2.top_left_y,
image_labels.bottom_right_x = image_labels2.bottom_right_x,
image_labels.bottom_right_y = image_labels2.bottom_right_y: ambiguous column name "top_left_x", it's present in all these tables: image_labels, image_labels2
ambiguous column name "top_left_x", it's present in all these tables: image_labels, image_labels2

Schema is:

CREATE TABLE `image_labels` (
  `image_id` int NOT NULL,
  `label` varchar(100) NOT NULL,
  `top_left_x` int,
  `top_left_y` int,
  `bottom_right_x` int,
  `bottom_right_y` int,
  PRIMARY KEY (`image_id`,`label`),
  CONSTRAINT `chk_esd5b974` CHECK ((`top_left_x` < `bottom_right_x`)),
  CONSTRAINT `chk_r4sbrdks` CHECK ((`top_left_y` < `bottom_right_y`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

Run

CREATE TABLE image_labels2 AS SELECT * FROM image_labels;

Result:

CREATE TABLE `image_labels2` (
  `image_id` int NOT NULL,
  `label` varchar(100) NOT NULL,
  `top_left_x` int,
  `top_left_y` int,
  `bottom_right_x` int,
  `bottom_right_y` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

:coffee: An Automated Dependency Version Bump PR :crown:

Initial Changes

The initial changes contained in this PR were produced by `go get`ing the dependency.

``` $ cd ./go $ go get github.com/dolthub//go@ ```

Before Merging

This PR must have passing CI and a review before merging.

SQL:

use db;

SET @testValue = 809826404100301269648758758005707100;
CREATE TABLE t(a INT, b DECIMAL(40, 2));
INSERT INTO t(a, b) VALUES (1, 1), (2, 42);
SELECT *, 'expecting 2 rows' FROM t WHERE b <= @testValue;

INSERT INTO t(a, b) VALUES (3, @testValue - 100);
SELECT *, 'expecting 3 rows' FROM t WHERE b < @testValue;

MySQL output:

+------+-------+------------------+
| a    | b     | expecting 2 rows |
+------+-------+------------------+
|    1 |  1.00 | expecting 2 rows |
|    2 | 42.00 | expecting 2 rows |
+------+-------+------------------+
+------+-----------------------------------------+------------------+
| a    | b                                       | expecting 3 rows |
+------+-----------------------------------------+------------------+
|    1 |                                    1.00 | expecting 3 rows |
|    2 |                                   42.00 | expecting 3 rows |
|    3 | 809826404100301269648758758005707000.00 | expecting 3 rows |
+------+-----------------------------------------+------------------+

dolt output:

error on line 3 for query 

SET @testValue = 809826404100301269648758758005707100: strconv.ParseUint: parsing "809826404100301269648758758005707100": value out of range
strconv.ParseUint: parsing "809826404100301269648758758005707100": value out of range

If we change @testValue to cast from a string to decimal, we can see that dolt handles decimals just fine.

SQL difference:

SET @testValue = CAST("809826404100301269648758758005707100" AS DECIMAL(40, 2));

dolt output:

Query OK, 2 rows affected (0.00 sec)
+---+-------+------------------+
| a | b     | expecting 2 rows |
+---+-------+------------------+
| 1 | 1.00  | expecting 2 rows |
| 2 | 42.00 | expecting 2 rows |
+---+-------+------------------+

Query OK, 1 row affected (0.00 sec)
+---+-----------------------------------------+------------------+
| a | b                                       | expecting 3 rows |
+---+-----------------------------------------+------------------+
| 1 | 1.00                                    | expecting 3 rows |
| 3 | 809826404100301269648758758005707000.00 | expecting 3 rows |
| 2 | 42.00                                   | expecting 3 rows |
+---+-----------------------------------------+------------------+

MySQL output stays the same:

+------+-------+------------------+
| a    | b     | expecting 2 rows |
+------+-------+------------------+
|    1 |  1.00 | expecting 2 rows |
|    2 | 42.00 | expecting 2 rows |
+------+-------+------------------+
+------+-----------------------------------------+------------------+
| a    | b                                       | expecting 3 rows |
+------+-----------------------------------------+------------------+
|    1 |                                    1.00 | expecting 3 rows |
|    2 |                                   42.00 | expecting 3 rows |
|    3 | 809826404100301269648758758005707000.00 | expecting 3 rows |
+------+-----------------------------------------+------------------+