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.

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

dolt panics with nested WHERE...IN blocks #5700

Closed
PavelSafronov opened this issue Apr 5, 2023 · 0 comments
Closed

dolt panics with nested WHERE...IN blocks #5700

PavelSafronov opened this issue Apr 5, 2023 · 0 comments
Labels
analyzer bug Something isn't working sql Issue with SQL

Comments

@PavelSafronov
Copy link
Contributor

SQL:

use db;

CREATE TABLE dcim_powerport (
  id char(32),
  _cable_peer_id char(32),
  allocated_draw smallint unsigned,
  _cable_peer_type_id int,
  PRIMARY KEY (id),
  KEY _cable_peer_type_id (_cable_peer_type_id)
);
insert into dcim_powerport (id, _cable_peer_id, allocated_draw, _cable_peer_type_id) values
('222d80159e4540de80b5c32d2bf15d5e','5723f100f8794febb68180769c31f3bd',NULL,55),
('561747a58a73401e92f5922a979c8ed2','03e857381d254ce2b9fd92fbc9e1399d',1200,29),
('aba27ecbf7664d058124c3616e516583','67f99993a0754862b04a9f48f165cabb',2400,55);

CREATE TABLE dcim_powerfeed (
  id char(32),
  _cable_peer_id char(32),
  _cable_peer_type_id int,
  rack_id char(32),
  PRIMARY KEY (id),
  KEY _cable_peer_type_id (_cable_peer_type_id),
  KEY rack_id (rack_id)
);
insert into dcim_powerfeed (id, _cable_peer_id, _cable_peer_type_id, rack_id) values
('5723f100f8794febb68180769c31f3bd','222d80159e4540de80b5c32d2bf15d5e',28,'6ba89a61c5094df7a709b01cc681ecd3'),
('67f99993a0754862b04a9f48f165cabb','aba27ecbf7664d058124c3616e516583',28,'6ba89a61c5094df7a709b01cc681ecd3');

CREATE TABLE dcim_poweroutlet (
  id char(32),
  _cable_peer_id char(32),
  power_port_id char(32),
  PRIMARY KEY (id),
  KEY power_port_id (power_port_id)
);
INSERT INTO dcim_poweroutlet (id,_cable_peer_id,power_port_id) VALUES
('03e857381d254ce2b9fd92fbc9e1399d','561747a58a73401e92f5922a979c8ed2','222d80159e4540de80b5c32d2bf15d5e');

SELECT SUM(dcim_powerport.allocated_draw)
FROM dcim_powerport
WHERE (
    dcim_powerport._cable_peer_id IN (
        SELECT  W0.id
        FROM dcim_poweroutlet W0
        WHERE W0.power_port_id IN (
            SELECT  V0.id
            FROM dcim_powerport V0
            WHERE (V0._cable_peer_type_id = 55)
        )
    )
    AND dcim_powerport._cable_peer_type_id = 29
);

MySQL behavior:

+------------------------------------+
| SUM(dcim_powerport.allocated_draw) |
+------------------------------------+
|                               1200 |
+------------------------------------+

dolt behavior:

error on line 39 for query 

SELECT SUM(dcim_powerport.allocated_draw)
FROM dcim_powerport
WHERE (
    dcim_powerport._cable_peer_id IN (
        SELECT  W0.id
        FROM dcim_poweroutlet W0
        WHERE W0.power_port_id IN (
            SELECT  V0.id
            FROM dcim_powerport V0
            WHERE (V0._cable_peer_type_id = 55)
        )
    )
    AND dcim_powerport._cable_peer_type_id = 29
): unable to find field with index 6 in row of 3 columns
unable to find field with index 6 in row of 3 columns

Bookkeeping note: this is breaking NTC test nautobot.dcim.tests.test_views.RackTestCase.test_powerports

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
analyzer bug Something isn't working sql Issue with SQL
Projects
None yet
Development

No branches or pull requests

2 participants