Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

WHERE statement has no effect when joining multiple tables #3065

Closed
Jannick-v opened this issue Mar 23, 2022 · 5 comments
Closed

WHERE statement has no effect when joining multiple tables #3065

Jannick-v opened this issue Mar 23, 2022 · 5 comments
Assignees
@max-hoffman
Labels
bug Something isn't working

Comments

@Jannick-v
Copy link

Starting from release 0.37.2 it seems that the filtering operation performed by a WHERE statement has no effect when applied on a table obtained after performing multiple (INNER) JOIN operations.
The reason why I suspect that this has something to do with Dolt is simply that I cannot reproduce this error using an SQLite backend.

I have extracted an MWE from a recording application, where we are tracking product locations in a sequence of images (frames).
The MWE database can easily be created by applying the following command in a fresh dolt repo:
$ dolt sql <doltdump.sql

-- doltdump.sql

DROP TABLE IF EXISTS `product_metadata`;
CREATE TABLE `product_metadata` (
                                    `prod_id` int NOT NULL AUTO_INCREMENT,
                                    `tech_id` int,
                                    `short_name` varchar(255),
                                    `long_name` varchar(255),
                                    PRIMARY KEY (`prod_id`),
                                    UNIQUE KEY `tech_id` (`tech_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `cameras`;
CREATE TABLE `cameras` (
                           `id` int NOT NULL AUTO_INCREMENT,
                           `serial` varchar(255) NOT NULL,
                           `width` int NOT NULL,
                           `height` int NOT NULL,
                           `channels` int NOT NULL,
                           PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `recordings`;
CREATE TABLE `recordings` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `use_case` varchar(255) NOT NULL,
                              `name` varchar(255) NOT NULL,
                              PRIMARY KEY (`id`),
                              UNIQUE KEY `use_casename` (`use_case`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `product_entries`;
CREATE TABLE `product_entries` (
                                   `id` int NOT NULL AUTO_INCREMENT,
                                   `prod_id` int,
                                   `sales_qty` int NOT NULL,
                                   PRIMARY KEY (`id`),
                                   UNIQUE KEY `prod_idsales_qty` (`prod_id`,`sales_qty`),
                                   KEY `ix_product_entries_prod_id` (`prod_id`),
                                   CONSTRAINT `r1le6pb7` FOREIGN KEY (`prod_id`) REFERENCES `product_metadata` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `tracks`;
CREATE TABLE `tracks` (
                          `id` int NOT NULL AUTO_INCREMENT,
                          `product_entry_id` int,
                          PRIMARY KEY (`id`),
                          KEY `ix_tracks_product_entry_id` (`product_entry_id`),
                          CONSTRAINT `mu77p6bh` FOREIGN KEY (`product_entry_id`) REFERENCES `product_entries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `frames`;
CREATE TABLE `frames` (
                          `id` int NOT NULL AUTO_INCREMENT,
                          `camera_id` int NOT NULL,
                          `recording_id` int NOT NULL,
                          `index` int NOT NULL,
                          `timestamp` float,
                          PRIMARY KEY (`id`),
                          KEY `camera_id` (`camera_id`),
                          KEY `ix_frames_index` (`index`),
                          KEY `recording_id` (`recording_id`),
                          CONSTRAINT `m0bcc1cr` FOREIGN KEY (`recording_id`) REFERENCES `recordings` (`id`),
                          CONSTRAINT `n4pfq0pq` FOREIGN KEY (`camera_id`) REFERENCES `cameras` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `annotations`;
CREATE TABLE `annotations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `frame_id` int NOT NULL,
  `bbox_r` float,
  `bbox_c` float,
  `bbox_w` float,
  `bbox_h` float,
  `track_id` int,
  `label_method` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_annotations_frame_id` (`frame_id`),
  KEY `ix_annotations_track_id` (`track_id`),
  CONSTRAINT `l5pnv0o2` FOREIGN KEY (`frame_id`) REFERENCES `frames` (`id`),
  CONSTRAINT `tphvig4r` FOREIGN KEY (`track_id`) REFERENCES `tracks` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `product_metadata` (`prod_id`,`tech_id`,`short_name`,`long_name`) VALUES (1,1,'test prod 1',NULL);
INSERT INTO `product_metadata` (`prod_id`,`tech_id`,`short_name`,`long_name`) VALUES (2,2,'test prod 2',NULL);

INSERT INTO `cameras` (`id`,`serial`,`width`,`height`,`channels`) VALUES (1,'1',600,400,3);
INSERT INTO `cameras` (`id`,`serial`,`width`,`height`,`channels`) VALUES (2,'2',600,400,3);

INSERT INTO `recordings` (`id`,`use_case`,`name`) VALUES (1,'test','test');

INSERT INTO `product_entries` (`id`,`prod_id`,`sales_qty`) VALUES (1,1,1);
INSERT INTO `product_entries` (`id`,`prod_id`,`sales_qty`) VALUES (2,2,1);

INSERT INTO `tracks` (`id`,`product_entry_id`) VALUES (1,1);

INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (1,1,1,0,0);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (9,1,1,4,0.2);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (8,2,1,3,0.15);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (10,2,1,4,0.2);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (5,1,1,2,0.1);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (2,2,1,0,0);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (12,2,1,5,0.25);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (7,1,1,3,0.15);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (15,1,1,7,0.35);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (11,1,1,5,0.25);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (6,2,1,2,0.1);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (13,1,1,6,0.3);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (17,1,1,8,0.4);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (3,1,1,1,0.05);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (16,2,1,7,0.35);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (18,2,1,8,0.4);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (4,2,1,1,0.05);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (14,2,1,6,0.3);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (21,1,1,10,0.5);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (25,1,1,12,0.6);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (24,2,1,11,0.55);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (22,2,1,10,0.5);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (27,1,1,13,0.65);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (19,1,1,9,0.45);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (20,2,1,9,0.45);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (26,2,1,12,0.6);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (23,1,1,11,0.55);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (29,1,1,14,0.7);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (30,2,1,14,0.7);
INSERT INTO `frames` (`id`,`camera_id`,`recording_id`,`index`,`timestamp`) VALUES (28,2,1,13,0.65);

INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (8,8,0.37,0.37,0.47,0.47,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (6,6,0.38,0.38,0.48,0.48,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (1,1,0.1,0.1,0.2,0.2,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (4,4,0.39,0.39,0.49,0.49,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (3,3,0.11,0.11,0.21,0.21,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (7,7,0.13,0.13,0.23,0.23,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (2,2,0.4,0.4,0.5,0.5,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (9,9,0.14,0.14,0.24,0.24,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (5,5,0.12,0.12,0.22,0.22,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (16,16,0.33,0.33,0.43,0.43,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (12,12,0.35,0.35,0.45,0.45,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (14,14,0.34,0.34,0.44,0.44,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (15,15,0.17,0.17,0.27,0.27,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (11,11,0.15,0.15,0.25,0.25,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (17,17,0.18,0.18,0.28,0.28,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (10,10,0.36,0.36,0.46,0.46,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (13,13,0.16,0.16,0.26,0.26,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (19,19,0.19,0.19,0.29,0.29,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (18,18,0.32,0.32,0.42,0.42,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (21,21,0.2,0.2,0.3,0.3,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (22,22,0.3,0.3,0.4,0.4,1,'auto');
INSERT INTO `annotations` (`id`,`frame_id`,`bbox_r`,`bbox_c`,`bbox_w`,`bbox_h`,`track_id`,`label_method`) VALUES (20,20,0.31,0.31,0.41,0.41,1,'auto');

Once created, run the following query:

SELECT frames.id, frames.camera_id, frames.recording_id, frames.index, frames.timestamp FROM frames JOIN cameras ON cameras.id = frames.camera_id JOIN annotations ON frames.id = annotations.frame_id WHERE frames.camera_id=1 order by id

The result of this query in dolt 0.37.2 and higher is unexpected:
dolt 0.37.6 results

+----+-----------+--------------+-------+-----------+
| id | camera_id | recording_id | index | timestamp |
+----+-----------+--------------+-------+-----------+
| 1  | 1         | 1            | 0     | 0         |
| 2  | 2         | 1            | 0     | 0         |
| 3  | 1         | 1            | 1     | 0.05      |
| 4  | 2         | 1            | 1     | 0.05      |
| 5  | 1         | 1            | 2     | 0.1       |
| 6  | 2         | 1            | 2     | 0.1       |
| 7  | 1         | 1            | 3     | 0.15      |
| 8  | 2         | 1            | 3     | 0.15      |
| 9  | 1         | 1            | 4     | 0.2       |
| 10 | 2         | 1            | 4     | 0.2       |
| 11 | 1         | 1            | 5     | 0.25      |
| 12 | 2         | 1            | 5     | 0.25      |
| 13 | 1         | 1            | 6     | 0.3       |
| 14 | 2         | 1            | 6     | 0.3       |
| 15 | 1         | 1            | 7     | 0.35      |
| 16 | 2         | 1            | 7     | 0.35      |
| 17 | 1         | 1            | 8     | 0.4       |
| 18 | 2         | 1            | 8     | 0.4       |
| 19 | 1         | 1            | 9     | 0.45      |
| 20 | 2         | 1            | 9     | 0.45      |
| 21 | 1         | 1            | 10    | 0.5       |
| 22 | 2         | 1            | 10    | 0.5       |
+----+-----------+--------------+-------+-----------+

dolt 0.37.2 results:

+----+-----------+--------------+-------+-----------+
| id | camera_id | recording_id | index | timestamp |
+----+-----------+--------------+-------+-----------+
| 1  | 1         | 1            | 0     | 0         |
| 2  | 2         | 1            | 0     | 0         |
| 3  | 1         | 1            | 1     | 0.05      |
| 4  | 2         | 1            | 1     | 0.05      |
| 5  | 1         | 1            | 2     | 0.1       |
| 6  | 2         | 1            | 2     | 0.1       |
| 7  | 1         | 1            | 3     | 0.15      |
| 8  | 2         | 1            | 3     | 0.15      |
| 9  | 1         | 1            | 4     | 0.2       |
| 10 | 2         | 1            | 4     | 0.2       |
| 11 | 1         | 1            | 5     | 0.25      |
| 12 | 2         | 1            | 5     | 0.25      |
| 13 | 1         | 1            | 6     | 0.3       |
| 14 | 2         | 1            | 6     | 0.3       |
| 15 | 1         | 1            | 7     | 0.35      |
| 16 | 2         | 1            | 7     | 0.35      |
| 17 | 1         | 1            | 8     | 0.4       |
| 18 | 2         | 1            | 8     | 0.4       |
| 19 | 1         | 1            | 9     | 0.45      |
| 20 | 2         | 1            | 9     | 0.45      |
| 21 | 1         | 1            | 10    | 0.5       |
| 22 | 2         | 1            | 10    | 0.5       |
+----+-----------+--------------+-------+-----------+

The result in older versions of dolt (here 0.37.1) is identical to the result obtained with an SQLite backend:

+----+-----------+--------------+-------+-----------+
| id | camera_id | recording_id | index | timestamp |
+----+-----------+--------------+-------+-----------+
| 1  | 1         | 1            | 0     | 0         |
| 3  | 1         | 1            | 1     | 0.05      |
| 5  | 1         | 1            | 2     | 0.1       |
| 7  | 1         | 1            | 3     | 0.15      |
| 9  | 1         | 1            | 4     | 0.2       |
| 11 | 1         | 1            | 5     | 0.25      |
| 13 | 1         | 1            | 6     | 0.3       |
| 15 | 1         | 1            | 7     | 0.35      |
| 17 | 1         | 1            | 8     | 0.4       |
| 19 | 1         | 1            | 9     | 0.45      |
| 21 | 1         | 1            | 10    | 0.5       |
+----+-----------+--------------+-------+-----------+

How do I fix this issue?
@timsehn
Copy link
Sponsor Contributor

timsehn commented Mar 23, 2022

Thanks for the great report!

We will dig into this today.

@bpf120
Copy link

bpf120 commented Mar 23, 2022

Hi @Jannick-v, thanks for filing this and using Dolt. We'd love to hear about your use case. You can email me (brianf@dolthub.com) or swing by our Discord if you'd like to share.

https://discord.com/invite/RFwfYpu

@zachmu
Copy link
Member

zachmu commented Mar 23, 2022

This is almost certainly a bad interaction between our index lookups and filter analysis. We're removing a filter when it's not appropriate to do so:

dolthub/go-mysql-server#806

@max-hoffman please dig in, get help from @Hydrocharged on the index parts.

@zachmu zachmu added the bug Something isn't working label Mar 23, 2022
@max-hoffman
Copy link
Contributor

@Jannick-v thanks for the helpful sql-dump! This PR should fix the correctness issue, we will try to get a release out later today.

dolthub/go-mysql-server#904

@Jannick-v
Copy link
Author

Hi all,
Thanks for the quick fix included in the latest release!
This fully resolves the issue I was facing last week, hence I am closing this issue.
Great work!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@max-hoffman max-hoffman

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@zachmu @timsehn @Jannick-v @max-hoffman @bpf120