Hi,
in our test suite we have this one :
\tests\functional\dml\cte_02.fbt
that fail under 3.0
'init_script': """ CREATE TABLE product( id_product INTEGER , name
VARCHAR(20) ,id_type_product INTEGER, PRIMARY KEY(id_product));
CREATE TABLE type_product(id_type_product INTEGER, name
VARCHAR(20),id_sub_type INTEGER);
INSERT INTO type_product(id_type_product,name,id_sub_type)
values(1,'DVD',NULL);
INSERT INTO type_product(id_type_product,name,id_sub_type)
values(2,'BOOK',NULL);
INSERT INTO type_product(id_type_product,name,id_sub_type)
values(3,'FILM SF',1);
INSERT INTO type_product(id_type_product,name,id_sub_type)
values(4,'FILM ACTION',1);
INSERT INTO type_product(id_type_product,name,id_sub_type)
values(5,'FILM ROMANCE',1);
INSERT INTO product(id_product, name,id_type_product) VALUES (1,'Harry
Potter 8',3 );
INSERT INTO product(id_product, name,id_type_product) VALUES (2,'Total
Recall',3 );
INSERT INTO product(id_product, name,id_type_product) VALUES
(3,'Kingdom of Heaven',3 );
INSERT INTO product(id_product, name,id_type_product) VALUES
(4,'Desperate Housewives',5 );
INSERT INTO product(id_product, name,id_type_product) VALUES (5,'Reign
over me',5 );
INSERT INTO product(id_product, name,id_type_product) VALUES (6,'Prison
Break',4 );
""",
'test_type': 'ISQL',
'test_script': """WITH RECURSIVE
TYPE_PRODUCT_RECUR AS
(
SELECT id_type_product ,'+ ' || name as name , id_type_product as father
FROM type_product
WHERE type_product.id_sub_type is null
UNION ALL
SELECT id_type_product ,' - ' || name , TR.id_type_product as father
FROM type_product T
JOIN TYPE_PRODUCT_RECUR TR
on TR.id_type_product = T.id_sub_type
),
COUNT_BY_TYPE AS
(
SELECT P.ID_TYPE_PRODUCT,count(ID_PRODUCT) as count_p from PRODUCT P
group by P.ID_TYPE_PRODUCT
union
SELECT TP.FATHER,count(ID_PRODUCT) as count_p from
TYPE_PRODUCT_RECUR TP , PRODUCT P
where TP.ID_TYPE_PRODUCT = P.id_type_product
group by TP.FATHER
)
SELECT T.id_type_product , T.name ,C.count_p
FROM TYPE_PRODUCT_RECUR T
left join COUNT_BY_TYPE C
on C.ID_TYPE_PRODUCT = T.id_type_product;
""",
'expected_stdout': """
ID_TYPE_PRODUCT NAME COUNT_P
=============== ====================== ============
1 + DVD 6
3 - FILM SF 3
4 - FILM ACTION 1
5 - FILM ROMANCE 2
2 + BOOK <null>
"""
Under 3.0 we get a :
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-TR.ID_TYPE_PRODUCT
-At line 25, column 26
------------------------------------------------------------------------------
Sponsored by Intel(R) XDK
Develop, test and display web and hybrid apps with a single code base.
Download it for free now!
http://pubads.g.doubleclick.net/gampad/clk?id=111408631&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
