You can translate the question and the replies:

Denodo order by issue when partial cache enabled.

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
15-11-2017 04:17:46 -0500

1 Answer

Hi, The error is due to a known issue and it will be fixed in the next official update of the Denodo Platform. Meanwhile as a workaround, you could execute the following query which works fine without any issue: > select "ColumnId", "ColumnData", "OrderByColumn" as tst1, "OrderByColumn" as tst2 from ( select "ColumnId", "ColumnData", "OrderByColumn" from "VW_Denodo_Cache_Bug" order by "OrderByColumn" desc); Hope this helps!
Denodo Team
21-11-2017 06:33:14 -0500
You must sign in to add an answer. If you do not have an account, you can register here