Tips & Tricks: Redshift: Some Interesting behaviour of Redshift Query

I have found some interesting behaviour of redshift query. It is interesting in a sense that the query runs perfectly in redshift but not in other database (SQL Server or PostgreSQL). Let’s see the behaviour. First one describes in http://www.sqlhaven.com/interesting-aggregate-query-result-using-clause-redshift/ this blog post by brother Khorshed Alam.

For demonstration purpose I have created a table and insert some dummy data to the table. Below is the script.

create table aggregatecheck
(
 col1 varchar(50),
 col2 varchar(100)
);

insert into aggregatecheck(col1,col2)
values('10','100'),('20','90')
 ,('30','80')
 ,('40','70')
 ,('50','60')
 ,('60','50')
 ,('70','40')
 ,('','1')
 ,(' ','2')
 ,(null,'3')
 ,('abc','string value')
 ,('120.66','numeric value');

Eliminating blank, null and empty space

If we apply not equal blank (<> ‘’ or !=’’) condition in the where clause it will eliminate blank, null and empty as well. See the below result set.

Figure: Eliminate blank, null and empty space in redshift

The query is also work in SQL server. But can’t work in PostgreSQL, it will only eliminate blank and null but not empty space. See the below image.

Figure: Result set of the same query in PostgreSQL

Filtering varchar field without quotes

We can apply filter in where clause for varchar type column without quotes and it will return the correct result set as long as the column contains integer value. See the below image, for query 1 we get 1 row which is correct, for query 2 we get 0 row but there is one row exists for that value in the table (wrong result) and query 3 it gives an error message .

Figure: Filtering varchar type column without quotes in redshift

But we get error message when we try to execute the query both in SQL Server and in PostgreSQL. See the below images.

Figure: Filtering varchar without quotes in SQL Server

Figure: Filtering varchar without quotes in PostgreSQL

Apply aggregate function to varchar field without casting

We can apply aggregate function (sum etc) to varchar type column without casting it to appropriate type as long as the column contains numeric value. The query will return the result with automatic casting to integer. The query will not throw any error. See the below image, the actual result will be 400.66 but it return 400 (cast to integer).

Figure: Aggregate (sum) result in redshift for varchar type column

But if the column contains any value without integer it will throws an error message. See the below image, in this case the column (col1) contains a string value (abc).