What is the correct order of clauses in a SELECT statement?

Help
1

I created table as below.

create table t1 (a int, b int);
insert into t1 values(1, 2);
insert into t1 values(2, 1);

I understand the query of below normal.

select a/b as c, t.* from t1 t;
select a/b as c, * from t1;

But, the query of below are OK in SingleStore DB, too.

select a/b as c, * from t1 where a/b > 1;
select a/b as c, * from t1 where c > 1;

This can cause confusion as below.

select  0 as c, c.c from 
 (
 select a/b as c, t.* from t1 t where c > 1
 ) c
where c > 1 ;

I want to know the contents of this.

2

The code is applying the last WHERE clause filter to the right-most c column.

To avoid ambiguity, don’t use duplicate column names with this filter-on-alias capability, which was new in 2021.

I’ll pass this on to the dev team. Arguably, duplicate column names in this situation could be turned into an error message, or could be considered fine and be left unchanged. Making it an error would be a breaking change so we would not do that as a bug fix. It would be a design change.

1 Like
3

Thank you from your reply.

But, it’s a little different from what I meant.
The above ‘duplicate column names’ was an example.
I’ll ask the question more accurately.

I knew ‘order of execution of a Query’ as 'FROM - WHERE -GROUP BY -HAVING - SELECT - ORDER BY’.
However, The query of below are OK. This query is not normal in other DBs like ORACLE.

select a/b as c, * from t1 where c > 1 ;

The normal query is as follows in ORACLE.

select a/b as c, t1.* from t1 where a/b > 1 ;

So, what I want to know is below.

  1. ‘order of execution of a Query’ in SingleStore
  2. Query to consider because of ‘order of execution of a Query’
  3. a document on this
4

We follow the SQL standard for SELECT-FROM-WHERE-GROUP BY-HAVING clause definitions and order.

We recently introduced support for ability to reference previously-defined aliases in the SELECT list, and ability to filter on an alias in the WHERE clause. This is similar to Sybase IQ, Redshift, and some other database products, but is not in the SQL Standard. See a discussion of these capabilities here:

1 Like