Re: [sqlite] Help with SQL syntax. Ticket #2296
Samuel R. Neff wrote: Still, I think backwards compatibility and consistency with other databases would be most important in this situation. I just checked MSSQL and it's same as current sqlite which uses the first select statement's column names. Samuel, The following is from Oracle's documentation: For compound queries containing set operators |UNION|, |INTERSECT|, |MINUS|, or |UNION| |ALL|, the |ORDER| |BY| clause must specify positions or aliases rather than explicit expressions. Also, the |ORDER| |BY| clause can appear only in the last component query. The |ORDER| |BY| clause orders all rows returned by the entire compound query. So they do require positions or aliases as I suggested earlier. Compatibility is hard to achieve when everyone does things differently. The best we can do is work towards a common standard. Even Oracle is changing to be more standard compliant as indicated by this note I ran into while reviewing their docs. Note: To comply with emerging SQL standards, a future release of Oracle will give the |INTERSECT| operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the |INTERSECT| operator with other set operators. It doesn't just affect order by too.. based on the standard SQLite should be returning a different column name entirely in the result set. Surely it would break a lot of code out there if SQLite suddenly started returning seemingly random column names from union queries when the query didn't specifically alias the columns. I agree, this seems strange to me too. I suspect I have missed some detail in the standard's convoluted description of parsing and propagating column names in queries. I'm still looking for that missing nugget. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Help with SQL syntax. Ticket #2296
Still, I think backwards compatibility and consistency with other databases would be most important in this situation. I just checked MSSQL and it's same as current sqlite which uses the first select statement's column names. It doesn't just affect order by too.. based on the standard SQLite should be returning a different column name entirely in the result set. Surely it would break a lot of code out there if SQLite suddenly started returning seemingly random column names from union queries when the query didn't specifically alias the columns. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 11:09 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > Wouldn't implementation dependent mean it's not really standardized? The > way I read it the query could still be considered legal in some dbms and > not > in others (which stinks). Samuel, That's not what the standard says. It says the name assigned to the result columns are implementation dependent, they could be sqlite_column_1 and sqlite_column_2, or perhaps cnnn where nnn is a random number, but they can not be the names of any of the columns in any of the tables in the query. The query should generate a syntax error because it is trying to sort on columns that are not, or at least should not be, present in the result. Besides, the current version of SQLite seems to match on the first tables > names which is consistent with expectations from other databases and not > prohibited by the standard (in the way I read it) and backwards > compatibility seems to be the most important thing here.. > > This behavior is prohibited by the standard. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
--- [EMAIL PROTECTED] wrote: > Consider this query: > >SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; > > Is the query above equalent to: > > (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; > > Or is it the same as: > > (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; Most databases use form (1). SQLite already leans in this direction anyway, using the first select in a union for the headings. May as well be consistant. SQLite version 3.3.15 Enter ".help" for instructions sqlite> .header on sqlite> select 3 a union select 4 b; a 3 4 8:00? 8:25? 8:40? Find a flick in no time with the Yahoo! Search movie showtime shortcut. http://tools.search.yahoo.com/shortcuts/#news - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Wouldn't implementation dependent mean it's not really standardized? The way I read it the query could still be considered legal in some dbms and not in others (which stinks). Samuel, That's not what the standard says. It says the name assigned to the result columns are implementation dependent, they could be sqlite_column_1 and sqlite_column_2, or perhaps cnnn where nnn is a random number, but they can not be the names of any of the columns in any of the tables in the query. The query should generate a syntax error because it is trying to sort on columns that are not, or at least should not be, present in the result. Besides, the current version of SQLite seems to match on the first tables names which is consistent with expectations from other databases and not prohibited by the standard (in the way I read it) and backwards compatibility seems to be the most important thing here.. This behavior is prohibited by the standard. Dennis Cote
RE: [sqlite] Help with SQL syntax. Ticket #2296
Wouldn't implementation dependent mean it's not really standardized? The
way I read it the query could still be considered legal in some dbms and not
in others (which stinks).
Besides, the current version of SQLite seems to match on the first tables
names which is consistent with expectations from other databases and not
prohibited by the standard (in the way I read it) and backwards
compatibility seems to be the most important thing here..
sqlite> create table t(a text, b text);
sqlite> insert into t values('one', 'two');
sqlite> insert into t values('three', 'four');
sqlite> select a, b from t union select b,a from t;
a b
-- --
fourthree
one two
three four
two one
sqlite> select a, b from t union select b,a from t order by a,b;
a b
-- --
fourthree
one two
three four
two one
sqlite> select a, b from t union select b,a from t order by b,a;
a b
-- --
three four
two one
fourthree
one two
sqlite>
So from this example seems like what we really need is to teach SQLite how
to count... four, one, three, two.. sheesh. :-)
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 12, 2007 6:05 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296
...
Otherwise, the of the i-th column of TR is implementation
dependent
and not equivalent to the of any column, other than
itself, of any table referenced by any contained in
the SQL statement.
...
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Help with SQL syntax. Ticket #2296
At 7:22 PM + 4/12/07, [EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. Alternately, attempting to run that could just be made to fail with an error citing ambiguity. Then users can make it work by making sure the columns they are unioning have the same names (and hence, so does the result of the union), such as by using 'AS' in the select-list. Unless you are explicitly trying to accept ambiguous syntax just because other DBMSs do (to aid portability), that is the simplest option. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
[EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. Richard, If my reading of the SQL:1999 standard is correct (its more than a little convoluted in this area), then from section 7.12 expression> Syntax Rule 16 case b)i)1 we have: Let C be the of the i-th column of T1. If the name> of the i-th column of T2 is C, then the of the i-th column of TR is C. where T1 and T2 are the tables being operated on by the UNION and TR is the result table. This case covers the normal case of matching column names. The next case 16)b)i)2 covers the case of non matching column names: Otherwise, the of the i-th column of TR is implementation dependent and not equivalent to the of any column, other than itself, of any table referenced by any contained in the SQL statement. This says that the output column names are neither a or b, but something else. This would imply that the first query above is in fact a syntax error since the output table does not have columns named a or b, and therefore it can't be ordered by those columns. Either of the second two queries should be legal, since they do not use column names for the order by clause. To generate a legal query the user would have to alias the column names in one or both of the sub queries. SELECT a, b FROM t1 UNION SELECT t1.b as a, t1.a as b from t1 ORDER by a, b SELECT t1.a as b, t1.b as a FROM t1 UNION SELECT b, a from t1 ORDER by a, b SELECT a as c, b as d FROM t1 UNION SELECT b as c, a as d from t1 ORDER by c, d HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Help with SQL syntax. Ticket #2296
Andy's answer and explanation is consistent with my experience and expectations too.. mostly from MSSQL and Access background. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 3:42 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 My understanding is: select a, b from t1 union select b, a from t1 is equivalent to select a as a, b as b from t1 union select b as a, a as b from t1 And therefore, the first sql statement controls the resulting column names, and the order by applies to the column names (transitively) from the first statement. I'll find a reference in SQL89/SQL92 to support my understanding, but this is how Oracle behaves. :) --andy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
My understanding is: select a, b from t1 union select b, a from t1 is equivalent to select a as a, b as b from t1 union select b as a, a as b from t1 And therefore, the first sql statement controls the resulting column names, and the order by applies to the column names (transitively) from the first statement. I'll find a reference in SQL89/SQL92 to support my understanding, but this is how Oracle behaves. :) --andy On 4/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
