- Mais recentes
- Mais votos
- Mais comentários
We tried different changes and all of them provided the correct result. However, the weird thing is that, individually, the subqueries work, but the left outer join of them is wrong. This behavior does not seem correct from the DBMS side, right?
Since most queries are providing correct results this seems more of a particular query problem to me, rather than a widespread DBMS problem. Also since the subqueries are work fine, but the left outer join of them is providing incorrect results, it could imply come unexpected data type conversion implicitly happening. I think you should try explicitly casting the correct data types for the join columns to be able to get down to the crux of the issue.
Also, FYI, the current type compatibility and conversion rules are documented at https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html#r_Type_conversion
And lastly, may I request that you use the
comment
button to add comments instead of posting comments as answers.Sorry, I didn't pay attention when I responded to your comment. About the data types, they are all the same on both side of the join: date time, date time, string, and boolean. If I create a table saving one of the side's results and use it on the query it gives the right result. It's like if the substitution principle is not valid
I tried using a simplified version of the table joins and I am seeing 8 rows in the results. Can you double check if some intermediate table is breaking the joins somewhere.
Here is my setup
create schema adventureworks2012;
create table adventureworks2012.t1
( c1 TIMESTAMP,
c2 TIMESTAMP,
c3 VARCHAR(50),
c4 BOOLEAN
);
create table adventureworks2012.t531
( c1 TIMESTAMP,
c2 TIMESTAMP,
c3 VARCHAR(50),
c4 BOOLEAN,
sales_amount INTEGER
);
insert into adventureworks2012.t1 values
('1970-01-01','1970-01-01','_AS_CONSTANT_',true),
('2005-01-02','2005-01-02','Reporting Calendar 2005',false),
('2007-01-07','2007-01-07','Reporting Calendar 2007',false),
('2008-01-06','2008-01-06','Reporting Calendar 2008',false)
;
insert into adventureworks2012.t531 values
('1970-01-01','1970-01-01','_AS_CONSTANT_',true,339772),
('1970-01-01','1970-01-01','_AS_CONSTANT_',true,700759),
('2005-01-02','2005-01-02','Reporting Calendar 2005',false,3266373),
('2005-01-02','2005-01-02','Reporting Calendar 2005',false,6485784),
('2007-01-07','2007-01-07','Reporting Calendar 2007',false,142513),
('2007-01-07','2007-01-07','Reporting Calendar 2007',false,302238),
('2008-01-06','2008-01-06','Reporting Calendar 2008',false,197258),
('2008-01-06','2008-01-06','Reporting Calendar 2008',false,398522)
;
select t1.c1, t1.c2, t1.c3, t1.c4, t531.sales_amount
from adventureworks2012.t1 as t1
left outer join adventureworks2012.t531 as t531
on t1.c1 = t531.c1
and t1.c2 = t531.c2
and t1.c3 = t531.c3
and t1.c4 = t531.c4
;
I am seeing the 8 rows as expected
Conteúdo relevante
- AWS OFICIALAtualizada há 2 anos
- AWS OFICIALAtualizada há 2 anos
Thanks for sharing the scripts. I tried using the Redshift-bug-db-schema.sql and notice the first line
create schema adventureworks2012Bug;
was a minor typo. I corrected that toadventureworks2012
and I have the schema and tables created. However, when I execute the query-simplified.sql I get no rows. Will it be possible for you to include minimal insert statements for tables so the query can execute and provide results?