You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
/* Value 0 represents -no value- */
INSERT INTO Colors (ColorID, ColorName) VALUES (0, 'Not defined');
INSERT INTO Colors (ColorID, ColorName) VALUES (1, 'Red');
INSERT INTO Colors (ColorID, ColorName) VALUES (2, 'White');
INSERT INTO Colors (ColorID, ColorName) VALUES (3, 'Blue');
INSERT INTO Colors (ColorID, ColorName) VALUES (4, 'Yellow');
INSERT INTO Colors (ColorID, ColorName) VALUES (5, 'Black');
INSERT INTO Colors (ColorID, ColorName) VALUES (6, 'Purple');
/* insert some data with references */
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (1, 'Red Rose', 1);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (2, 'White Rose', 2);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (3, 'Blue Rose', 3);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (4, 'Yellow Rose', 4);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (5, 'Black Rose', 5);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (6, 'Red Tulip', 1);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (7, 'White Tulip', 2);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (8, 'Yellow Tulip', 4);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (9, 'Blue Gerbera', 3);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (10, 'Purple Gerbera', 6);
COMMIT;
/* Normally these indexes are created by the primary/foreign keys,
but we don't want to rely on them for this test */
CREATE UNIQUE ASC INDEX PK_Colors ON Colors (ColorID);
CREATE UNIQUE ASC INDEX PK_Flowers ON Flowers (FlowerID);
CREATE ASC INDEX FK_Flowers_Colors ON Flowers (ColorID);
CREATE ASC INDEX I_Colors_ColorName ON Colors (ColorName);
COMMIT;
and the test :
SET PLAN ON;
SELECT
f.ColorID,
c.ColorName,
Count(*)
FROM
Colors c
LEFT JOIN Flowers f ON (f.ColorID = c.ColorID)
GROUP BY
f.ColorID, c.ColorName
HAVING
c.ColorName LIKE 'B%';
Before 2.5Beta1 we get :
PLAN SORT (JOIN (C INDEX (I_COLORS_COLORNAME), F INDEX (FK_FLOWERS_COLORS)))
COLORID COLORNAME COUNT
============ ==================== ============
3 Blue 2
5 Black 1
with 2.5.0Beta1 :
PLAN SORT (JOIN (C NATURAL, F INDEX (FK_FLOWERS_COLORS)))
COLORID COLORNAME COUNT
============ ==================== ============
3 Blue 2
5 Black 1
Submitted by: @asfernandes
Philippe wrote in fb-devel:
we get a fail in one test (functional.arno.optimizer.opt_aggregate_distribution_14)
here the initialization :
CREATE TABLE Colors (
ColorID INTEGER NOT NULL,
ColorName VARCHAR(20)
);
CREATE TABLE Flowers (
FlowerID INTEGER NOT NULL,
FlowerName VARCHAR(30),
ColorID INTEGER
);
COMMIT;
/* Value 0 represents -no value- */
INSERT INTO Colors (ColorID, ColorName) VALUES (0, 'Not defined');
INSERT INTO Colors (ColorID, ColorName) VALUES (1, 'Red');
INSERT INTO Colors (ColorID, ColorName) VALUES (2, 'White');
INSERT INTO Colors (ColorID, ColorName) VALUES (3, 'Blue');
INSERT INTO Colors (ColorID, ColorName) VALUES (4, 'Yellow');
INSERT INTO Colors (ColorID, ColorName) VALUES (5, 'Black');
INSERT INTO Colors (ColorID, ColorName) VALUES (6, 'Purple');
/* insert some data with references */
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (1, 'Red Rose', 1);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (2, 'White Rose', 2);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (3, 'Blue Rose', 3);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (4, 'Yellow Rose', 4);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (5, 'Black Rose', 5);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (6, 'Red Tulip', 1);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (7, 'White Tulip', 2);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (8, 'Yellow Tulip', 4);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (9, 'Blue Gerbera', 3);
INSERT INTO Flowers (FlowerID, FlowerName, ColorID) VALUES (10, 'Purple Gerbera', 6);
COMMIT;
/* Normally these indexes are created by the primary/foreign keys,
but we don't want to rely on them for this test */
CREATE UNIQUE ASC INDEX PK_Colors ON Colors (ColorID);
CREATE UNIQUE ASC INDEX PK_Flowers ON Flowers (FlowerID);
CREATE ASC INDEX FK_Flowers_Colors ON Flowers (ColorID);
CREATE ASC INDEX I_Colors_ColorName ON Colors (ColorName);
COMMIT;
and the test :
SET PLAN ON;
SELECT
f.ColorID,
c.ColorName,
Count(*)
FROM
Colors c
LEFT JOIN Flowers f ON (f.ColorID = c.ColorID)
GROUP BY
f.ColorID, c.ColorName
HAVING
c.ColorName LIKE 'B%';
Before 2.5Beta1 we get :
PLAN SORT (JOIN (C INDEX (I_COLORS_COLORNAME), F INDEX (FK_FLOWERS_COLORS)))
COLORID COLORNAME COUNT
============ ==================== ============
3 Blue 2
5 Black 1
with 2.5.0Beta1 :
PLAN SORT (JOIN (C NATURAL, F INDEX (FK_FLOWERS_COLORS)))
COLORID COLORNAME COUNT
============ ==================== ============
3 Blue 2
5 Black 1
Commits: 4a4fd4c
====== Test Details ======
See: fbt-repo\tests\functional\arno\optimizer\opt_aggregate_distribution_14.fbt
The text was updated successfully, but these errors were encountered: