Bryan Pendleton wrote:
> ij> select SQL_TEXT from syscs_diag.statement_cache where
CAST(SQL_TEXT AS LONG VARCHAR) LIKE '%932432%';
...
> Kathey, was it intentional to include an extra space in the above
mentioned statement (between "TEXT" and "AS")?
> I think if the second space is removed, the result will contain one
row instead of two, and we won't see this diff any longer...
Why does the extra space affect the number of rows in the result? Isn't
that space just whitespace and discarded by the SQL parser?
I don't know. I've attached the failing output in case you want to have a look.
--
John
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
--
-- this test is for miscellaneous errors
--
-- lexical error
select @#^%*&! from swearwords;
ERROR 42X02: Lexical error at line 22, column 8. Encountered: "@" (64), after
: "".
ij> --
-- try to create duplicate table
--
create table a (one int);
0 rows inserted/updated/deleted
ij> create table a (one int, two int);
ERROR X0Y32: Table/View 'A' already exists in Schema 'APP'.
ij> create table a (one int);
ERROR X0Y32: Table/View 'A' already exists in Schema 'APP'.
ij> drop table a ;
0 rows inserted/updated/deleted
ij> create table a (one int, two int, three int);
0 rows inserted/updated/deleted
ij> insert into a values (1,2,3);
1 row inserted/updated/deleted
ij> select * from a;
ONE |TWO |THREE
-----------------------------------
1 |2 |3
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- set isolation to repeatable read
set isolation serializable;
0 rows inserted/updated/deleted
ij> -- see that statements that fail at parse or bind time
-- are not put in the statment cache;
values 1;
1
-----------
1
ij> select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG
VARCHAR) LIKE '%932432%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------
select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG
VARCHAR) LIKE '%932432%'
ij> VALUES FRED932432;
ERROR 42X04: Column 'FRED932432' is either not in any table in the FROM list or
appears within a join specification and is outside the scope of the join
specification or appears in a HAVING clause and is not in the GROUP BY list. If
this is a CREATE or ALTER TABLE statement then 'FRED932432' is not a column in
the target table.
ij> SELECT * FROM BILL932432;
ERROR 42X05: Table/View 'BILL932432' does not exist.
ij> SELECT 932432;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 13.
ij> select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS
LONG VARCHAR) LIKE '%932432%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------
select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG
VARCHAR) LIKE '%932432%'
select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG
VARCHAR) LIKE '%932432%'
ij>
