{Denodo}月の初日 - HTN20190109の日記

調査パターン:
ソースDB: MySQL,Oracle,PostgreSQL,SQL Server,Redshift
キャシュDB: MySQL,Oracle,PostgreSQL,SQL Server,Redshift

結論: DBにRedshiftを使用すると月の初日を条件とするクエリがエラーになる場合がある

-- テストテーブル準備

drop table tab1;
create table tab1(col1 varchar(100) );

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
insert into tab1 values(
DATE_FORMAT(date_add(now(), interval -1 * floor(365*24*3600 * rand()) second),'%Y%m%d' )
);
end while;
commit;
end
//
delimiter ;

call proc1(1000);
select * from tab1 order by 1;

-- 2. Oracle

drop table tab1 purge;
create table tab1(col1 varchar2(100) );

declare
begin

for i in 1..1000 loop
insert into tab1 select to_char(sysdate - floor(dbms_random.value(0, 365) ) ,'yyyymmdd') from dual;
end loop;
commit;
end;
/

select * from tab1 order by 1;

-- 3. PostgreSQL

drop table tab1;
create table tab1(col1 varchar(100) );

select to_char(current_timestamp - CAST( floor(365 * random()) || 'days' AS interval),'YYYYMMDD') ;

insert into tab1 select to_char(current_timestamp - CAST( floor(365 * random()) || 'days' AS interval),'YYYYMMDD')
from generate_series(1,1000);

select * from tab1 order by 1;

-- 4. SQL Server

drop table tab1;
create table tab1(col1 varchar(100) );

set nocount on;
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000
begin
insert into tab1
select format(dateadd(day,-1 * floor(365 * rand()),GETDATE() ),'yyyyMMdd')
set @i = @i + 1;
end
commit;

select * from tab1 order by 1;

-- 5. Redshift

drop table tab1;
create table tab1(col1 varchar(100) );

insert into tab1 select to_char(CURRENT_DATE - CAST( floor(365 * random()) || 'days' AS interval),'YYYYMMDD') from SVL_STATEMENTTEXT;

select * from tab1 order by 1;

-- 構文エラー調査

(1-1) ソース: MySQL8, キャッシュ: MySQL8
SELECT * FROM db01.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK
※ データソースでallowLoadLocalInfileをtrueに設定する必要あり

SELECT * FROM db01.b_tab1
where col1 >= formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-4) ) )
and col1 < formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-3) ) )
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(1-2) ソース: Oracle19c, キャッシュ: MySQL8

SELECT * FROM db02."b_TAB1"
where "COL1" >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and "COL1" < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

SELECT * FROM db02."b_TAB1"
where "COL1" >= formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-4) ) )
and "COL1" < formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-3) ) )
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(1-3) ソース: postgreSQL15, キャッシュ: MySQL8

SELECT * FROM db03.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

SELECT * FROM db03.b_tab1
where col1 >= formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-4) ) )
and col1 < formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-3) ) )
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(1-4) ソース: SQL Server2022, キャッシュ: MySQL8

SELECT * FROM db04.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

SELECT * FROM db04.b_tab1
where col1 >= formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-4) ) )
and col1 < formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-3) ) )
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(1-5) ソース: Redshift, キャッシュ: MySQL8

SELECT * FROM db05.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

SELECT * FROM db05.b_tab1
where col1 >= formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-4) ) )
and col1 < formatdate('yMMdd',FIRSTDAYOFMONTH(addmonth(current_date,-3) ) )
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(2-1) ソース: MySQL8, キャッシュ: Oracle19c
SELECT * FROM db01.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(2-2) ソース: Oracle19c, キャッシュ: Oracle19c

→ OK

(2-3) ソース: postgreSQL15, キャッシュ: Oracle19c

→ OK

(2-4) ソース: SQL Server2022, キャッシュ: Oracle19c

→ OK

(2-5) ソース: Redshift, キャッシュ: Oracle19c

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

→ OK

(3-1) ソース: MySQL8, キャッシュ: PostgreSQL15
SELECT * FROM db01.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(3-2) ソース: Oracle19c, キャッシュ: PostgreSQL15

→ OK

(3-3) ソース: postgreSQL15, キャッシュ: PostgreSQL15

→ OK

(3-4) ソース: SQL Server2022, キャッシュ: PostgreSQL15

→ OK

(3-5) ソース: Redshift, キャッシュ: PostgreSQL15

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

→ OK

(4-1) ソース: MySQL8, キャッシュ: SQL Server2022
SELECT * FROM db01.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ OK

(4-2) ソース: Oracle19c, キャッシュ: SQL Server2022

→ OK

(4-3) ソース: postgreSQL15, キャッシュ: SQL Server2022

→ OK

(4-4) ソース: SQL Server2022, キャッシュ: SQL Server2022

→ OK

(4-5) ソース: Redshift, キャッシュ: SQL Server2022

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

→ OK

(5-1) ソース: MySQL8, キャッシュ: Redshift
SELECT * FROM db01.b_tab1
where col1 >= formatdate('yMM',addmonth(current_date,-4) )||'01'
and col1 < formatdate('yMM',addmonth(current_date,-3) )||'01'
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'true')
;

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

(5-2) ソース: Oracle19c, キャッシュ: Redshift

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

→ OK

(5-3) ソース: postgreSQL15, キャッシュ: Redshift

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

→ OK

(5-4) ソース: SQL Server2022, キャッシュ: Redshift

→ OK
しかしCONTEXT句を指定しないと★エラー発生
Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.

→ OK

(5-5) ソース: Redshift, キャッシュ: Redshift

→ Finished with error: Error loading cache: ERROR: array value must start with "{" or dimension information.
★エラー発生

→ OK