Closed jsantana3c closed 1 day ago
jsantana3c
commented
1 month ago when creating a model with native SQL, casting it as timestamp:
SELECT cast(column as timestamp) as column_a from table;
it's working properly, but using that column (and adding the metadata etc) the filter on the dashboard doesn't work either.
paoliniluis
commented
1 month ago can you give us the DDL of the table?
jsantana3c
commented
1 month ago create table example_table
(
ID NUMBER not null
constraint "pk_id_table"
primary key,
property1 NUMBER,
event_date TIMESTAMP(6) WITH LOCAL TIME ZONE not null,
property2 CHAR not null
constraint CHK_property2
check (property2 IN ('T', 'F')),
property3 NUMBER,
property4 NUMBER,
property5 NUMBER,
property6 NUMBER,
property7 NUMBER,
property8 CHAR not null
constraint CHK_property8
check (property8 IN ('T', 'F')),
property9 CHAR not null
constraint CHK_property9
check (property9 IN ('T', 'F')),
property10 NUMBER,
property11 CHAR default 'F' not null
constraint CHK_property11
check (property11 IN ('T', 'F')),
property12 CHAR,
property13 VARCHAR2(20),
property14 CLOB,
property15 CHAR
)@paoliniluis sorry to ping you, wanted to know if there's anyway I can help fix this, can you give me the starting points to check what can I do?
paoliniluis
commented
1 week ago @jsantana3c another customer just hit this one as well so I'll be prioritizing this today
paoliniluis
commented
1 week ago I wasn't able to hit the issue on the timestamp, but rather on timestamp diff's
Repro: 1) run this in your oracle DB:
create table table1 (
name_col VARCHAR(255),
number_col NUMBER(19,0),
timestamp1_col TIMESTAMP,
timestamp2_col TIMESTAMP
);
insert into table1 values ('name1' , 1, TO_TIMESTAMP('01-01-2024 08:00 PM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('01-01-2024 07:00 PM', 'MM-DD-YYYY HH:MI AM'));
insert into table1 values ('name2' , 2, TO_TIMESTAMP('02-01-2024 09:00 PM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('02-01-2024 08:30 PM', 'MM-DD-YYYY HH:MI AM'));
insert into table1 values ('name3' , 3, TO_TIMESTAMP('03-01-2024 10:00 PM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('03-01-2024 09:20 PM', 'MM-DD-YYYY HH:MI AM'));
insert into table1 values ('name11', 11, TO_TIMESTAMP('01-02-2024 08:00 AM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('01-02-2024 06:52 AM', 'MM-DD-YYYY HH:MI AM'));
insert into table1 values ('name12', 12, TO_TIMESTAMP('02-02-2024 08:30 AM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('02-02-2024 08:22 AM', 'MM-DD-YYYY HH:MI AM'));
insert into table1 values ('name13', 13, TO_TIMESTAMP('03-02-2024 08:45 AM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('03-02-2024 08:33 AM', 'MM-DD-YYYY HH:MI AM'));
insert into table1 values ('name14', 14, TO_TIMESTAMP('04-02-2024 08:55 AM', 'MM-DD-YYYY HH:MI AM'), TO_TIMESTAMP('04-02-2024 06:13 AM', 'MM-DD-YYYY HH:MI AM'));
create or replace view view1 as
select name_col
, number_col
, (timestamp1_col - timestamp2_col) as timestamp_diff
, TO_TIMESTAMP('14.01.2023 8:00 PM', 'DD.MM.YYYY HH:MI AM') - TO_TIMESTAMP('16.02.2023 10:15 PM', 'DD.MM.YYYY HH:MI AM') AS timestamp_diff2
from table1
;2) add the db and then go to the view, hit the error
joaquinjsb
commented
1 week ago you did just add a "timezone" type, the issue is reproducible with TIMESTAMP(6) WITH LOCAL TIME ZONE, for example this ddl:
create table example_table
(
property1 NUMBER,
property2 timestamp(6),
event_date TIMESTAMP(6) WITH LOCAL TIME ZONE not null
);i successfully managed to reproduce it, the result I get is this:
darksciencebase
commented
1 week ago if it's easy to smash https://github.com/metabase/metabase/issues/44109 in the same go, we should
joaquinjsb
commented
1 week ago if it's easy to smash #44109 in the same go, we should
I do also have the clob issue, so probably it's related to the whole oracle implementation
joaquinjsb
commented
1 week ago and if you guys are squashing oracle bugs, do also check this one https://github.com/metabase/metabase/issues/42338
github-actions[bot]
commented
20 hours ago 🚀 This should also be released by v0.50.8
Describe the bug timestamps in my Oracle instance aren't fingerprinted properly.
Logs
To Reproduce Steps to reproduce the behavior:
Expected behavior the timestamps should be properly fingerprinted and rendered.
Screenshots
Severity it's preventing my from using the product at all, when creating a model, based on a native query, the filters on the dashboard are not working.
Additional context DBMS: Oracle (ver. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Metabase Diagnostic Info