Open 6.5 items

SELECT ... WHERE col ~ '(foo|bar)' works, but CHECK on table always fails

Does not reproduce here.

test=> create table t2 (i text check(i ~ '(foo|bar)'));
CREATE
test=> insert into t1 values ('aaa');
ERROR: ExecAppend: rejected due to CHECK constraint t1_i
test=> insert into t1 values ('foo');
INSERT 18634 1
---
Tatsuo Ishii

Allow "col AS name" to use name in WHERE clause? Is this ANSI?
Works in GROUP BY

Neighter Informix nor Oracle do it, so it is probably not ansi, but it would

be a very neat feature, especially if you do some arithmetic,
the statement gets a lot clearer.

But it probably adds some complexity:

create table a (a int, b int, c int);
select a, b as c from a where c=5;

Which c do you use alias or column ? You prbly need to use the column,
since this is how all others work, but would this be intuitive ?

Andreas

Thus spake ZEUGSWETTER Andreas IZ5

Allow "col AS name" to use name in WHERE clause? Is this ANSI?
Works in GROUP BY

But it probably adds some complexity:

create table a (a int, b int, c int);
select a, b as c from a where c=5;

Which c do you use alias or column ? You prbly need to use the column,
since this is how all others work, but would this be intuitive ?

Not to me. What if I don't know that a c exists in the table, or it is
added after creating many scripts? I think we should use the alias in
that case. Either that or it should generate an error.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

SELECT ... WHERE col ~ '(foo|bar)' works, but CHECK on table always fails

Does not reproduce here.

test=> create table t2 (i text check(i ~ '(foo|bar)'));
CREATE
test=> insert into t1 values ('aaa');
ERROR: ExecAppend: rejected due to CHECK constraint t1_i
test=> insert into t1 values ('foo');
INSERT 18634 1

Thanks. Removed.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Allow "col AS name" to use name in WHERE clause? Is this ANSI?
Works in GROUP BY

Neighter Informix nor Oracle do it, so it is probably not ansi, but it would

be a very neat feature, especially if you do some arithmetic,
the statement gets a lot clearer.

But it probably adds some complexity:

create table a (a int, b int, c int);
select a, b as c from a where c=5;

Which c do you use alias or column ? You prbly need to use the column,
since this is how all others work, but would this be intuitive ?

That is an excellent point. GROUP BY has to use a column name, and they
have to be unique, while WHERE does not require stuff to be in the
target list, so there is a change of ambiguity. I am going to remove
the item from the list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

resno's, sublevelsup corrupt when reaching rewrite system

Don't remember exactly how I produced them. Haven't seen
them again after the latest changes in the rule system. I
think it was due to incorrect handling of unrewritten TLE's
from group by clauses, which are now pulled out of the main
targetlist.

3 = sum(x) in rewrite system is a problem

Is it? I guess what is meant by this item is the problem of
the rewriter that it must create subqueries for view
aggregate columns if they appear in the WHERE clause.

That entire area is a very problematic one. And for sake it
must wait for after v6.5. Aggregates and GROUP BY in views
are unsafe and depend on the later usage of the view.
Consider the following:

CREATE TABLE t1 (a text, b text, c int4);
CREATE VIEW v1 AS SELECT a, b, sum(c) as n
FROM t1 GROUP BY a, b;
CREATE TABLE t2 (a text, b text);

SELECT t2.a, v1.n FROM t2, v1 WHERE t2.a = v1.a
GROUP BY t2.a;

Due to the new code in the rewriter, adding junk TLE's for
the view's GROUP BY columns, this doesn't crash the backend
anymore. The result (IMHO wrong) will return multiple rows
with same t2.a because the rewritten query reads as:

SELECT t2.a, sum(t1.c) FROM t2, t1
WHERE t2.a = t1.a GROUP BY t2.a, t1.a, t1.b;

The correct result would be only one row per t2.a with one of
the possible values of v1.n if a plain SELECT * FROM v1 is
done. But there's currently no way to express that in a
querytree.

What's absolutely broken is:

SELECT t2.a, sum(v1.n) FROM t2, v1 WHERE t2.a = v1.a
GROUP BY t2.a;

This gives totally unpredictable results because after
rewriting you have cascaded aggregates. And I expected the
rotten results I've seen from it :-)

I really hope to find the time after v6.5 to implement my
idea of subselecting RTE's where I can place all those views
that have these beasty DISTINCT, UNION, GROUP BY and other
f*ing stuff. The result of a subselecting RTE will be an on-
the-fly-materialization of the entire view used in a nestloop
or so (dunno exactly yet). It's expansive - yes - and I don't
know yet how to pull out restrictions from the WHERE clause
to make the views subset as small as possible - but AFAICS
the only fail-safe way to meet the view definition in a
complex join.

Future TODO items
-----------------
CREATE VIEW ignores DISTINCT

Covered above.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

resno's, sublevelsup corrupt when reaching rewrite system

Don't remember exactly how I produced them. Haven't seen
them again after the latest changes in the rule system. I
think it was due to incorrect handling of unrewritten TLE's
from group by clauses, which are now pulled out of the main
targetlist.

Removed. I suspected you had fixed it with your last GROUP patch,
because you were addressing this exact area.

3 = sum(x) in rewrite system is a problem

Is it? I guess what is meant by this item is the problem of
the rewriter that it must create subqueries for view
aggregate columns if they appear in the WHERE clause.

The issue where was that aggregates can't be on the right in some cases.
Tom Lane brought this up.

That entire area is a very problematic one. And for sake it
must wait for after v6.5. Aggregates and GROUP BY in views
are unsafe and depend on the later usage of the view.
Consider the following:

Yes, I understand.

Future TODO items
-----------------
CREATE VIEW ignores DISTINCT

Covered above.

OK.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <maillist@candle.pha.pa.us> writes:

That is an excellent point. GROUP BY has to use a column name, and they
have to be unique, while WHERE does not require stuff to be in the
target list, so there is a change of ambiguity. I am going to remove
the item from the list.

Good point --- consider this:
SELECT a, b AS a FROM tt GROUP BY a;
We do get it right: "ERROR: GROUP BY 'a' is ambiguous".
Whereas in
SELECT a, b AS a FROM tt WHERE a = 1;
the WHERE clause is taken as referring to the "real" column a.

So, unless there's some violation of spec behavior here, there is a
reason for GROUP BY to behave differently from WHERE. I think I was
the one who complained that they were different --- I withdraw the
complaint.

BTW, which behavior should ORDER BY exhibit? I find that
SELECT a, b AS a FROM tt ORDER BY a;
is accepted and 'a' is taken to be the real column a. Considering that
ORDER BY is otherwise much like GROUP BY, I wonder whether it shouldn't
complain that 'a' is ambiguous...

regards, tom lane

I did some more thinking on this. The logic is, that where and group by
affect the result set of a select, but order by (and col. alias) only affect

result presentation. This is why order by shoud look at the alias first,
while everything else should look at the real column name first.
(where, group, having ...)

Good point --- consider this:
SELECT a, b AS a FROM tt GROUP BY a;
We do get it right: "ERROR: GROUP BY 'a' is ambiguous".

This is wrong, it should use the real column (all other DBMS do this).

Whereas in
SELECT a, b AS a FROM tt WHERE a = 1;
the WHERE clause is taken as referring to the "real" column a.

good

So, unless there's some violation of spec behavior here, there is a
reason for GROUP BY to behave differently from WHERE. I think I was
the one who complained that they were different --- I withdraw the
complaint.

No, group by and where have to be the same. Your oringinal complaint was
justified.

BTW, which behavior should ORDER BY exhibit? I find that
SELECT a, b AS a FROM tt ORDER BY a;
is accepted and 'a' is taken to be the real column a. Considering that
ORDER BY is otherwise much like GROUP BY, I wonder whether it shouldn't
complain that 'a' is ambiguous...

This is wrong, order by needs to use the alias.

I therefore see the following for TODO:
use alias before column for order by -- very important
(currently wrong)
use real column name before alias for group by -- important
(currently does elog)
use alias in where iff it is unambiguous -- feature,
not important

On the other hand, anyone really using such ambiguous names
deserves unpredictable results anyway :-)

Andreas

Thus spake ZEUGSWETTER Andreas IZ5

Good point --- consider this:
SELECT a, b AS a FROM tt GROUP BY a;
We do get it right: "ERROR: GROUP BY 'a' is ambiguous".

This is wrong, it should use the real column (all other DBMS do this).

Regardless of what the others do, I prefer our behaviour better. What if
the column is not in the select list and perhaps is added to the database
table later? It seems wrong to me that the behaviour of this select
should change if a column, perhaps not relevant to the program doing
the select, is added. I would prefer that it fail so I could investigate
it to see what I have to change.

Whereas in
SELECT a, b AS a FROM tt WHERE a = 1;
the WHERE clause is taken as referring to the "real" column a.

good

Well, I don't care only because someone would be nuts to write this. :-)

BTW, which behavior should ORDER BY exhibit? I find that
SELECT a, b AS a FROM tt ORDER BY a;
is accepted and 'a' is taken to be the real column a. Considering that
ORDER BY is otherwise much like GROUP BY, I wonder whether it shouldn't
complain that 'a' is ambiguous...

This is wrong, order by needs to use the alias.

I agree but I wouldn't complain if it gave an error.

I therefore see the following for TODO:
use alias before column for order by -- very important
(currently wrong)

Yep.

use real column name before alias for group by -- important
(currently does elog)

I prefer the current behaviour.

use alias in where iff it is unambiguous -- feature,
not important

Yes.

On the other hand, anyone really using such ambiguous names
deserves unpredictable results anyway :-)

Absolutely. My feeling is that if the select is unambiguous and self
consistent, the intuitive thing should happen. This means that as
long as they don't make alias names that conflict with column names
that are selected (meaning all column names if '*' is selected) then
the alias should always be taken over the unselected column name.
I am less concerned about the behaviour when the select is ambiguous
on the face of it.

Of course, we should follow the standard wherever it has something to
say on the subject but let's not be overly concerned about what others
do in this situation. If it's a real problem then let's just elog any
ambiguity and document our reasons for doing so.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

shift/reduce conflict in grammar, SELECT ... FOR [UPDATE|CURSOR]

Fixed.

The problem was that CursorStmt tried to parse FOR UPDATE
which is already parsed by SelectStmt.

To fix it I had to add FOR READ ONLY to SelectStmt (returning
NULL for forUpdate as if empty) and let CursorStmt look at
there for the elog(). Don't know if FOR READ ONLY is O.K. for
regular SELECT queries too, but I think it's better to allow
this than to remove this syntax from CURSOR.

The same error is still in the ecpg parser. AFAICS fixing it
the same way there would make ecpg accept the DECLARE/FOR
UPDATE syntax because there is no Query where to look at a
forUpdate. Any suggestions?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

"D'Arcy" "J.M." Cain <darcy@druid.net> writes:

Thus spake ZEUGSWETTER Andreas IZ5

This is wrong, it should use the real column (all other DBMS do this).

Regardless of what the others do, I prefer our behaviour better.

Er, I think what actually counts is what the SQL92 spec says ...
but I haven't got a copy to look at.

regards, tom lane