Weird behavior with WHERE clause

[tbantle22]

I noticed that sometimes deleting a row using the Dolt Workbench sometimes doesn't work.

To repro, follow the Doltgres getting started guide here

Add schemas:

newdb=> create table employees (
    id int8,
    last_name text,
    first_name text,
    primary key(id));
CREATE TABLE
newdb=> create table teams (
    id int8,
    team_name text,
    primary key(id));
CREATE TABLE
newdb=> create table employees_teams(
    team_id int8,
    employee_id int8,
    primary key(team_id, employee_id),
    foreign key (team_id) references teams(id),
    foreign key (employee_id) references employees(id));
CREATE TABLE
newdb=> \d
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | employees       | table | postgres
 public | employees_teams | table | postgres
 public | teams           | table | postgres
(3 rows)

newdb=> select dolt_commit('-Am', 'Created initial schema');
            dolt_commit
------------------------------------
 {dmgebsbblrlcs3o0ju314d46c6qkgjer}
(1 row)

Insert rows:

newdb=> insert into employees values
    (0, 'Sehn', 'Tim'),
    (1, 'Hendriks', 'Brian'),
    (2, 'Son','Aaron'),
    (3, 'Fitzgerald', 'Brian');
INSERT 0 4
newdb=> select * from employees where first_name='Brian';
 id | last_name  | first_name
----+------------+------------
  1 | Hendriks   | Brian
  3 | Fitzgerald | Brian
(2 rows)

newdb=> insert into teams values
    (0, 'Engineering'),
    (1, 'Sales');
INSERT 0 2
newdb=> insert into employees_teams(employee_id, team_id) values
    (0,0),
    (1,0),
    (2,0),
    (0,1),
    (3,1);
INSERT 0 5
newdb=> select dolt_commit('-am', 'Populated tables with data');
            dolt_commit
------------------------------------
 {7eeno6vrmqg0rg17ep3sc5pidbsttjfq}
(1 row)

Now the employees_teams table looks like this:

newdb=> select * from employees_teams;
 team_id | employee_id
---------+-------------
       0 |           0
       0 |           1
       0 |           2
       1 |           0
       1 |           3
(5 rows)

But if I want to select * from employees_teams with a where clause, it works for some IDs and not for other IDs.

Works:

newdb=> select * from employees_teams where team_id=0 and employee_id=0;
 team_id | employee_id
---------+-------------
       0 |           0
(1 row)

Returns 0 rows when it shouldn't:

newdb=> select * from employees_teams where team_id=1 and employee_id=0;
 team_id | employee_id
---------+-------------
(0 rows)

I also cannot delete that row:

newdb=> delete from employees_teams where team_id=1 and employee_id=0;
DELETE 0
newdb=> select * from employees_teams;
 team_id | employee_id
---------+-------------
       0 |           0
       0 |           1
       0 |           2
       1 |           0
       1 |           3
(5 rows)

I didn't see this behavior using where clauses with the employees or teams tables

[zachmu]

Seems like this is probably an index matching issue. Thanks for the repro!

[tbantle22]

Skipped test here: https://github.com/dolthub/doltgresql/blob/main/testing/go/getting_started_guide_test.go#L103