Hello, guys.
I faced with the weird issue when enabling denodo cache (partial mode) in the following case:
1. Created two tables in MS SQL Server and imported it to Denodo:
create table dbo.Denodo_Bug_View1
(
ColumnId bigint not null identity(1,1),
Column1 nvarchar(50) null,
Column2 nvarchar(100) null,
OrderByColumn bigint null
)
go
insert into dbo.Denodo_Bug_View1 (Column1, Column2, OrderByColumn) values
('1A', '2K', 222),
('1B', '2J', 444),
('1C', '2H', 666),
('1D', '2G', 888),
('1E', '2F', 2222),
('1F', '2E', 4444),
('1G', '2D', 6666),
('1H', '2C', 8888),
('1J', '2B', 22222),
('1K', '2A', 44444)
go
create table dbo.Denodo_Bug_ViewA
(
ColumnId bigint not null identity(1,1),
ColumnA nvarchar(50) null,
ColumnB nvarchar(100) null,
OrderByColumn bigint null
)
go
insert into dbo.Denodo_Bug_ViewA (ColumnA, ColumnB, OrderByColumn) values
('A1', 'B10', 111),
('A2', 'B9', 333),
('A3', 'B8', 555),
('A4', 'B7', 777),
('A5', 'B6', 999),
('A6', 'B5', 1111),
('A7', 'B4', 3333),
('A8', 'B3', 5555),
('A9', 'B2', 7777),
('A10', 'B1', 9999)
go
2. Created a view in Denodo and enabled partial cache:
create or replace view "VW_Denodo_Cache_Bug" as
with view1_suquery as
(
select "ColumnId",
"ColumnA" as "ColumnData",
"OrderByColumn"
from "Denodo_Bug_ViewA"
), view2_suquery as
(
select "ColumnId",
"Column1" as "ColumnData",
"OrderByColumn"
from "Denodo_Bug_View1"
), union_all_with_order_by as
(
select *
from view1_suquery
union all
select *
from view2_suquery
order by "OrderByColumn" desc
)
select *
from union_all_with_order_by
CONTEXT ('formatted' = 'yes');
ALTER VIEW "VW_Denodo_Cache_Bug"
CACHE PARTIAL EXACT
BATCHSIZEINCACHE DEFAULT TIMETOLIVEINCACHE DEFAULT;
3. Run the VQL query twice:
select "ColumnId",
"ColumnData",
"OrderByColumn" as tst1,
"OrderByColumn" as tst2
from "VW_Denodo_Cache_Bug"
order by tst1 desc;
The following error occurs when running second time:
** QUERY [PROJECTION] [ERROR] VW_DENODO_CACHE_BUG [CACHE] [ERROR] A column has been specified more than once in the order by list. Columns in the order by list must be unique.**
When I investigated SQL script from query plan:
** SELECT ColumnId, ColumnData, OrderByColumn FROM
C_VW_DENODO_CACHE_BUG36824890402848268788158285636160943723597 WHERE (
queryPatternId = ? ) ORDER BY OrderByColumn DESC, OrderByColumn DESC**
The reason is that denodo generates two equal columns in ORDER BY statement: **ORDER BY OrderByColumn DESC, OrderByColumn DESC.**
Nececcary conditions to reproduce this bug:
1. Nested ORDER BY in denodo view VW_Denodo_Cache_Bug with the same order type as in VQL query (DESC)
2. Two columns with different aliaces in VQL query ("OrderByColumn" as tst1, "OrderByColumn" as tst2)
3. Partial cache mode enabled on view VW_Denodo_Cache
