It appears that Postgres, DB2 and SQL Server CE have issues with
certain types of alias expresssions in GROUP BY, while MySQL does
not.
Postgres will allow column aliases in GROUP BY - even aliases overriding
table column names - as long as every table column component of that
alias'
On 12/11/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote:
> On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- [EMAIL PROTECTED] wrote:
> > > The current name resolution rules for SQLite are that it
> > > first tries to resolve names using just the tables in the
> > > join. If that fails,
On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > The current name resolution rules for SQLite are that it
> > first tries to resolve names using just the tables in the
> > join. If that fails, then it looks at result column aliases.
> > I think that approach
--- [EMAIL PROTECTED] wrote:
> The current name resolution rules for SQLite are that it
> first tries to resolve names using just the tables in the
> join. If that fails, then it looks at result column aliases.
> I think that approach continues to work on WHERE. But I need
> to reverse the
Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> So SQLite should be sorting by the result column "c" not the column "c"
> in the table T1 which it doing.
>
My understand as of yesterday is that column name aliases
are significant for ORDER BY, but not for WHERE, in standard SQL.
This is not as
Joe Wilson schrieb:
--- Trevor Talbot <[EMAIL PROTECTED]> wrote:
On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote:
IBM DB2 9.5
select a AS "foo" from t1 union select b from t1 order by foo
SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703
The problem here is
MSSQL results match your MySQL and PostgreSQL results. (I only changed the
table name to be a temporary table)
create table #t1(a INT, b INT, c INT);
insert into #t1 values(1, 2, 4);
insert into #t1 values(2, -1000, 5);
(1 row(s) affected)
(1 row(s) affected)
-- See if select alias
> Could someone please post the results of these queries on Oracle,
> DB2 or SQL Server?
On MS SQL Server 2000, your queries result in the following:
a c
--- ---
2-998
1 3
(2 row(s) affected)
foo
---
Joe Wilson wrote:
Let's add another row to table t1...
postgres=> insert into t1 values(2, -1000, 5);
INSERT 0 1
test=> select * from t1;
a | b | c
---+---+---
1 | 2 | 4
2 | -1000 | 5
postgres=> select a, a+b AS "c" from t1 order by c;
a | c
---+--
2 | -998
1 |3
--- Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote:
>
> > IBM DB2 9.5
> >
> > select a AS "foo" from t1 union select b from t1 order by foo
> > SQL0206N "FOO" is not valid in the context where it is used.
> > SQLSTATE=42703
>
> The problem
On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote:
> IBM DB2 9.5
>
> select a AS "foo" from t1 union select b from t1 order by foo
> SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703
The problem here is with the inconsistent quoting. PostgreSQL uses the
opposite
On 12/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > Dennis Cote <[EMAIL PROTECTED]> wrote:
> > > [EMAIL PROTECTED] wrote:
> > > >
> > > > Can you please tell me what
> > > > other databases do with this:
> > > >
> > > >CREATE TABLE t1(a,b,c);
> > > >INSERT
On 12/8/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- Robert Wishlaw <[EMAIL PROTECTED]> wrote:
> > > >CREATE TABLE t1(a,b,c);
>
> > IBM DB2 9.5
> >
> > INSERT INTO t1 VALUES(1,2,4);
> > SELECT a+b AS c FROM t1 WHERE c=4;
> >
> > returns
> >
> > C
> >
> >
--- [EMAIL PROTECTED] wrote:
> Dennis Cote <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > >
> > > Can you please tell me what
> > > other databases do with this:
> > >
> > >CREATE TABLE t1(a,b,c);
> > >INSERT INTO t1 VALUES(1,2,4);
> > >SELECT a+b AS c FROM t1 WHERE c==4;
Dennis Cote <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> >
> > Can you please tell me what
> > other databases do with this:
> >
> >CREATE TABLE t1(a,b,c);
> >INSERT INTO t1 VALUES(1,2,4);
> >SELECT a+b AS c FROM t1 WHERE c==4;
> >
> > In the WHERE clause, should the "c"
[EMAIL PROTECTED] wrote:
Can you please tell me what
other databases do with this:
CREATE TABLE t1(a,b,c);
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b AS c FROM t1 WHERE c==4;
In the WHERE clause, should the "c" resolve to
the column "c" or to the "a+b" expression? I'm
guessing the
> >CREATE TABLE t1(a,b,c);
> >INSERT INTO t1 VALUES(1,2,4);
> >SELECT a+b AS c FROM t1 WHERE c=4;
>
> SQL Server returns a 3.
> ORACLE 9 returns a 3.
PostgreSQL 8.1.5 also returns a 3
Never
--- Robert Wishlaw <[EMAIL PROTECTED]> wrote:
> > >CREATE TABLE t1(a,b,c);
> IBM DB2 9.5
>
> INSERT INTO t1 VALUES(1,2,4);
> SELECT a+b AS c FROM t1 WHERE c=4;
>
> returns
>
> C
>
>3
>3
>3
>
On Dec 8, 2007 11:49 PM, Trey Mack <[EMAIL PROTECTED]> wrote:
> > Can you please tell me what
> > other databases do with this:
> >
> >CREATE TABLE t1(a,b,c);
> >INSERT INTO t1 VALUES(1,2,4);
> >SELECT a+b AS c FROM t1 WHERE c==4;
>
> SQL Server returns a 3.
> ORACLE 9 returns a 3.
>
Can you please tell me what
other databases do with this:
CREATE TABLE t1(a,b,c);
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b AS c FROM t1 WHERE c==4;
SQL Server returns a 3.
ORACLE 9 returns a 3.
-
To
>CREATE TABLE t1(a,b,c);
>INSERT INTO t1 VALUES(1,2,4);
>SELECT a+b AS c FROM t1 WHERE c==4;
MySQL 5.0.45:
create table t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b AS c FROM t1 WHERE c=4;
+--+
| c|
+--+
|3 |
+--+
> In the WHERE clause,
Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> My interest is to make sqlite more compatible with
> other databases or to improve its speed.
>
I'm happy for the help. Can you please tell me what
other databases do with this:
CREATE TABLE t1(a,b,c);
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> >
> > So few lines of code are changed by the patch that one could easily
> > add a new PRAGMA to have the old compound SELECT behavior to be the
> > default. If a database is explicitly created or altered with the new
> >
Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> So few lines of code are changed by the patch that one could easily
> add a new PRAGMA to have the old compound SELECT behavior to be the
> default. If a database is explicitly created or altered with the new
> PRAGMA setting, it could have the new
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> On Dec 7, 2007, at 11:47 PM, Joe Wilson wrote:
> >
> > I believe it makes compound query behavior more compatible with other
> > popular databases. It is mostly backwards compatible with the previous
> > syntax and only 2 tests performed by "make
On Dec 7, 2007, at 11:47 PM, Joe Wilson wrote:
I believe it makes compound query behavior more compatible with other
popular databases. It is mostly backwards compatible with the previous
syntax and only 2 tests performed by "make test" had to be altered.
Compatibility with other databases
The attached sqlite 3.5.3 patch addresses several different compound
query column naming and resolving issues in ORDER BY and the SELECT
expression list mentioned in this ticket:
http://www.sqlite.org/cvstrac/tktview?tn=2822
(The exception being it does not support expressions in the ORDER
27 matches
Mail list logo
