Oracle 5.0, Справочное руководство

Страница: 2990 / 3232

Query-Related Issues
2970
Standard SQL disallows references to column aliases in a
WHERE
clause. This restriction is imposed
because when the
WHERE
clause is evaluated, the column value may not yet have been determined.
For example, the following query is illegal:
SELECT id, COUNT(*) AS cnt FROM tbl_name
WHERE cnt > 0 GROUP BY id;
The
WHERE
clause determines which rows should be included in the
GROUP BY
clause, but it refers to
the alias of a column value that is not known until after the rows have been selected, and grouped by
the
GROUP BY
.
In the select list of a query, a quoted column alias can be specified using identifier or string quoting
characters:
SELECT 1 AS `one`, 2 AS 'two';
Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference
is treated as a string literal. For example, this statement groups by the values in column
id
, referenced
using the alias
`a`
:
SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
GROUP BY `a`;
But this statement groups by the literal string
'a'
and will not work as expected:
SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
GROUP BY 'a';
C.5.5.5. Rollback Failure for Nontransactional Tables
If you receive the following message when trying to perform a
ROLLBACK
, it means that one or more of
the tables you used in the transaction do not support transactions:
Warning: Some non-transactional changed tables couldn't be rolled back
These nontransactional tables are not affected by the
ROLLBACK
statement.
If you were not deliberately mixing transactional and nontransactional tables within the transaction, the
most likely cause for this message is that a table you thought was transactional actually is not. This
can happen if you try to create a table using a transactional storage engine that is not supported by
your
mysqld
server (or that was disabled with a startup option). If
mysqld
does not support a storage
engine, it instead creates the table as a
MyISAM
table, which is nontransactional.
You can check the storage engine for a table by using either of these statements:
SHOW TABLE STATUS LIKE ' tbl_name ';
SHOW CREATE TABLE tbl_name ;
See Section 13.7.5.33, “
SHOW TABLE STATUS
Syntax” , and Section 13.7.5.9, “
SHOW CREATE TABLE
Syntax” .
You can check which storage engines your
mysqld
server supports by using this statement:
SHOW ENGINES;
You can also use the following statement, and check the value of the variable that is associated with
the storage engine in which you are interested:
SHOW VARIABLES LIKE 'have_%';
For example, to determine whether the
InnoDB
storage engine is available, check the value of the
have_innodb
[455] variable.
See Section 13.7.5.13, “
SHOW ENGINES
Syntax” , and Section 13.7.5.36, “
SHOW VARIABLES
Syntax” .