SQL Fun
Not a support question but a puzzle: the poster built a table called "select" whose columns are all SQL keywords (create, from, where, and, or, set, etc.) and challenged readers to spot which of his statements would fail, making the point that SQL technically has no reserved words and that Informix only rejects keyword identifiers some of the time. Replies: Oracle raised an exception on essentially all of them; DB2 ran them all (the "select ... from from from select" case only worked once an alias named FROM was created), and the readers guessing picked that statement too. The author admitted two failed on Informix. Takeaway offered: don't use keywords as column names, since failures are unpredictable rather than consistent.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I was thinking about this the other day and I created some "amusing"
examples. Technically SQL has no reserved words so you can create some
abdominal SQL. Of course in reality some of them don't work or don't
work all of the time so here are some examples of some really bad
things to do. All of these statements work except one. Can you tell
which one without actually running them? I can't. (all rights reserved
;-) )
create table select(
serial serial,
create int,
table int,
order int,
select int,
from int,
where int,
and int,
or int,
insert int,
update int,
in int,
int int,
delete int,
drop int,
into int,
set int
) ;
insert into select (
serial, create, table, order, select,
from, where, and, or, insert,
update, in, int, delete, drop,
into, set
)
values (
0, 1, 2, 3, 4,
5, 6, 7, 8, 9,
10, 11, 12, 13, 14,
15, 16
) ;
select select from select;
select from from select where and = 5 and or = 7 ;
delete from select where where = 10 and and = 20 ;
delete from select where into = 5;
update select set set = 20 where where = 5 and order = 7 and drop = 8
and and = 2 ;
select or, and, update from select where and = 6 and or = and ;
select or, and, from from from select ;
select or and, and or, from from select ;
select or select, and where, from from select;
bozon wrote:
> I was thinking about this the other day and I created some "amusing"
> examples. Technically SQL has no reserved words so you can create some
> abdominal SQL. Of course in reality some of them don't work or don't
> work all of the time so here are some examples of some really bad
> things to do. All of these statements work except one. Can you tell
> which one without actually running them? I can't. (all rights reserved
> ;-) )
>
> create table select(
> serial serial,
> create int,
> table int,
> order int,
> select int,
> from int,
> where int,
> and int,
> or int,
> insert int,
> update int,
> in int,
> int int,
> delete int,
> drop int,
> into int,
> set int
> ) ;
> insert into select (
> serial, create, table, order, select,
> from, where, and, or, insert,
> update, in, int, delete, drop,
> into, set
> )
> values (
> 0, 1, 2, 3, 4,
> 5, 6, 7, 8, 9,
> 10, 11, 12, 13, 14,
> 15, 16
> ) ;>
> select select from select;>
> select from from select where and = 5 and or = 7 ;>
> delete from select where where = 10 and and = 20 ;>
> delete from select where into = 5;>
> update select set set = 20 where where = 5 and order = 7 and drop = 8
> and and = 2 ;>
> select or, and, update from select where and = 6 and or = and ;>
> select or, and, from from from select ;>
> select or and, and or, from from select ;
>
> select or select, and where, from from select;
Interesting ... I tried this in Oracle ... couldn't find a single
one that didn't raise an exception.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
bozon wrote:
> I was thinking about this the other day and I created some "amusing"
> examples. Technically SQL has no reserved words so you can create some
> abdominal SQL. Of course in reality some of them don't work or don't
> work all of the time so here are some examples of some really bad
> things to do. All of these statements work except one. Can you tell
> which one without actually running them? I can't. (all rights reserved
> ;-) )
>
> create table select(
> serial serial,
> create int,
> table int,
> order int,
> select int,
> from int,
> where int,
> and int,
> or int,
> insert int,
> update int,
> in int,
> int int,
> delete int,
> drop int,
> into int,
> set int
> ) ;
> insert into select (
> serial, create, table, order, select,
> from, where, and, or, insert,
> update, in, int, delete, drop,
> into, set
> )
> values (
> 0, 1, 2, 3, 4,
> 5, 6, 7, 8, 9,
> 10, 11, 12, 13, 14,
> 15, 16
> ) ;>
> select select from select;>
> select from from select where and = 5 and or = 7 ;>
> delete from select where where = 10 and and = 20 ;>
> delete from select where into = 5;>
> update select set set = 20 where where = 5 and order = 7 and drop = 8
> and and = 2 ;>
> select or, and, update from select where and = 6 and or = and ;>
> select or, and, from from from select ;>
> select or and, and or, from from select ;
>
> select or select, and where, from from select;
I'd guess this one:
select or, and, from from from select
select or, and, from AS from from select
?
I believe I am correct that SQL is not supposed to have any reserved words. Of course that is from my memory and I don't remember where or when I learned this. (I think college which was a long time ago.) This fact may have even changed since then and different vendors short circuited the complicated parsing that no reserved words implies. If you use "key words" as my "SQL IN A NUTSHELL" book calls them in the appendices then you should expect issues. I would hate to write any of the parser. And actually I was mistaken there are two that don't work on Informix. DA Morgan wrote: > bozon wrote: ... > Interesting ... I tried this in Oracle ... couldn't find a single > one that didn't raise an exception. > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace x with u to respond)
yes
richard.harnden@googlemail.com wrote:
> bozon wrote:
>
> > I was thinking about this the other day and I created some "amusing"
> > examples. Technically SQL has no reserved words so you can create some
> > abdominal SQL. Of course in reality some of them don't work or don't
> > work all of the time so here are some examples of some really bad
> > things to do. All of these statements work except one. Can you tell
> > which one without actually running them? I can't. (all rights reserved
> > ;-) )
> >
> > create table select(
> > serial serial,
> > create int,
> > table int,
> > order int,
> > select int,
> > from int,
> > where int,
> > and int,
> > or int,
> > insert int,
> > update int,
> > in int,
> > int int,
> > delete int,
> > drop int,
> > into int,
> > set int
> > ) ;
> > insert into select (
> > serial, create, table, order, select,
> > from, where, and, or, insert,
> > update, in, int, delete, drop,
> > into, set
> > )
> > values (
> > 0, 1, 2, 3, 4,
> > 5, 6, 7, 8, 9,
> > 10, 11, 12, 13, 14,
> > 15, 16
> > ) ;> >
> > select select from select;> >
> > select from from select where and = 5 and or = 7 ;> >
> > delete from select where where = 10 and and = 20 ;> >
> > delete from select where into = 5;> >
> > update select set set = 20 where where = 5 and order = 7 and drop = 8
> > and and = 2 ;> >
> > select or, and, update from select where and = 6 and or = and ;> >
> > select or, and, from from from select ;> >
> > select or and, and or, from from select ;
> >
> > select or select, and where, from from select;
>
> I'd guess this one:
> select or, and, from from from select>
> select or, and, from AS from from select>
> ?
bozon wrote:
> I was thinking about this the other day and I created some "amusing"
> examples. Technically SQL has no reserved words so you can create some
> abdominal SQL. Of course in reality some of them don't work or don't
> work all of the time so here are some examples of some really bad
> things to do. All of these statements work except one. Can you tell
> which one without actually running them? I can't. (all rights reserved
> ;-) )
>
> create table select(
> serial serial,
> create int,
> table int,
> order int,
> select int,
> from int,
> where int,
> and int,
> or int,
> insert int,
> update int,
> in int,
> int int,
> delete int,
> drop int,
> into int,
> set int
> ) ;
> insert into select (
> serial, create, table, order, select,
> from, where, and, or, insert,
> update, in, int, delete, drop,
> into, set
> )
> values (
> 0, 1, 2, 3, 4,
> 5, 6, 7, 8, 9,
> 10, 11, 12, 13, 14,
> 15, 16
> ) ;>
> select select from select;>
> select from from select where and = 5 and or = 7 ;>
> delete from select where where = 10 and and = 20 ;>
> delete from select where into = 5;>
> update select set set = 20 where where = 5 and order = 7 and drop = 8
> and and = 2 ;>
> select or, and, update from select where and = 6 and or = and ;>
> select or, and, from from from select ;
I'll vote for these last 2 not working:
> select or and, and or, from from select ;
>
> select or select, and where, from from select;
Now I have to try it...
Art S. Kagel
You will burn in the pit of hell if you put any of that code live :)
Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com
Failure is not as frightening as regret.
Attend IDUG 2007 San Jose, North America
May 6-10, 2007
Visit http://www.iiug.org/conf for more information.
> -----Original Message-----
> From: bozon [mailto:curtis@crowson1.com]
> Posted At: 17 January 2007 07:35
> Posted To: comp.databases.informix
> Conversation: SQL Fun
> Subject: SQL Fun
>
>
> I was thinking about this the other day and I created some "amusing"
> examples. Technically SQL has no reserved words so you can
> create some abdominal SQL. Of course in reality some of them
> don't work or don't work all of the time so here are some
> examples of some really bad things to do. All of these
> statements work except one. Can you tell which one without
> actually running them? I can't. (all rights reserved
> ;-) )
>
> create table select(
> serial serial,
> create int,
> table int,
> order int,
> select int,
> from int,
> where int,
> and int,
> or int,
> insert int,
> update int,
> in int,
> int int,
> delete int,
> drop int,
> into int,
> set int
> ) ;
> insert into select (
> serial, create, table, order, select,
> from, where, and, or, insert,
> update, in, int, delete, drop,
> into, set
> )
> values (
> 0, 1, 2, 3, 4,
> 5, 6, 7, 8, 9,
> 10, 11, 12, 13, 14,
> 15, 16
> ) ;>
> select select from select;>
> select from from select where and = 5 and or = 7 ;>
> delete from select where where = 10 and and = 20 ;>
> delete from select where into = 5;>
> update select set set = 20 where where = 5 and order = 7 and> drop = 8 and and = 2 ;
>
> select or, and, update from select where and = 6 and or = and ;>
> select or, and, from from from select ;>
> select or and, and or, from from select ;
>
> select or select, and where, from from select;
>
bozon said:
> I was thinking about this the other day and I created some "amusing"
> examples. Technically SQL has no reserved words so you can create some
> abdominal SQL. Of course in reality some of them don't work or don't
> work all of the time so here are some examples of some really bad
> things to do. All of these statements work except one. Can you tell
> which one without actually running them? I can't. (all rights reserved
> ;-) )
>
> create table select(
> serial serial,
> create int,
> table int,
> order int,
> select int,
> from int,
> where int,
> and int,
> or int,
> insert int,
> update int,
> in int,
> int int,
> delete int,
> drop int,
> into int,
> set int
> ) ;
> insert into select (
> serial, create, table, order, select,
> from, where, and, or, insert,
> update, in, int, delete, drop,
> into, set
> )
> values (
> 0, 1, 2, 3, 4,
> 5, 6, 7, 8, 9,
> 10, 11, 12, 13, 14,
> 15, 16
> ) ;>
> select select from select;>
> select from from select where and = 5 and or = 7 ;>
> delete from select where where = 10 and and = 20 ;>
> delete from select where into = 5;>
> update select set set = 20 where where = 5 and order = 7 and drop = 8
> and and = 2 ;>
> select or, and, update from select where and = 6 and or = and ;>
> select or, and, from from from select ;>
> select or and, and or, from from select ;
>
> select or select, and where, from from select;
Sweet Jesus! Don't you have any work or something? :o)
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
I have to shepherd young developers (Large cast iron frying pan
involved usually) This was something I worked up to show them why we
should be careful to not use "key words" as column names. We have
several columns called "date" that existed before me. I think that they
just assumed that the SQL would fail if they tried to use a name that
they shouldn't. Instead it just fails sometimes, which is eminently
better. ;-)
Obnoxio The Clown wrote:
> bozon said:
> > I was thinking about this the other day and I created some "amusing"
> > examples. Technically SQL has no reserved words so you can create some
> > abdominal SQL. Of course in reality some of them don't work or don't
> > work all of the time so here are some examples of some really bad
> > things to do. All of these statements work except one. Can you tell
> > which one without actually running them? I can't. (all rights reserved
> > ;-) )
> >
> > create table select(
> > serial serial,
> > create int,
> > table int,
> > order int,
> > select int,
> > from int,
> > where int,
> > and int,
> > or int,
> > insert int,
> > update int,
> > in int,
> > int int,
> > delete int,
> > drop int,
> > into int,
> > set int
> > ) ;
> > insert into select (
> > serial, create, table, order, select,
> > from, where, and, or, insert,
> > update, in, int, delete, drop,
> > into, set
> > )
> > values (
> > 0, 1, 2, 3, 4,
> > 5, 6, 7, 8, 9,
> > 10, 11, 12, 13, 14,
> > 15, 16
> > ) ;> >
> > select select from select;> >
> > select from from select where and = 5 and or = 7 ;> >
> > delete from select where where = 10 and and = 20 ;> >
> > delete from select where into = 5;> >
> > update select set set = 20 where where = 5 and order = 7 and drop = 8
> > and and = 2 ;> >
> > select or, and, update from select where and = 6 and or = and ;> >
> > select or, and, from from from select ;> >
> > select or and, and or, from from select ;
> >
> > select or select, and where, from from select;
>
> Sweet Jesus! Don't you have any work or something? :o)
>
> --
> Bye now,
> Obnoxio
>
> "I don't read newspapers anymore except the local rag which I do weekly to
> cheer myself trying to see if anyone I hate has been stabbed."
> -- Horribilis XVI
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
Obnoxio The Clown wrote: > > Sweet Jesus! Don't you have any work or something? :o) > > -- > Bye now, > Obnoxio > > "I don't read newspapers anymore except the local rag which I do weekly to > cheer myself trying to see if anyone I hate has been stabbed." > -- Horribilis XVI > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. Right back at you, OTC. ;-)
And this is a change for me, how? ;-)
Paul Watson wrote:
> You will burn in the pit of hell if you put any of that code live :)
>
> Paul Watson
> Tel: +44 1414161772
> Mob: +44 7818003457
> Web: www.oninit.com
>
> Failure is not as frightening as regret.
>
> Attend IDUG 2007 San Jose, North America
> May 6-10, 2007
> Visit http://www.iiug.org/conf for more information.
>
>
>
>
> > -----Original Message-----
> > From: bozon [mailto:curtis@crowson1.com]
> > Posted At: 17 January 2007 07:35
> > Posted To: comp.databases.informix
> > Conversation: SQL Fun
> > Subject: SQL Fun
> >
> >
> > I was thinking about this the other day and I created some "amusing"
> > examples. Technically SQL has no reserved words so you can
> > create some abdominal SQL. Of course in reality some of them
> > don't work or don't work all of the time so here are some
> > examples of some really bad things to do. All of these
> > statements work except one. Can you tell which one without
> > actually running them? I can't. (all rights reserved
> > ;-) )
> >
> > create table select(
> > serial serial,
> > create int,
> > table int,
> > order int,
> > select int,
> > from int,
> > where int,
> > and int,
> > or int,
> > insert int,
> > update int,
> > in int,
> > int int,
> > delete int,
> > drop int,
> > into int,
> > set int
> > ) ;
> > insert into select (
> > serial, create, table, order, select,
> > from, where, and, or, insert,
> > update, in, int, delete, drop,
> > into, set
> > )
> > values (
> > 0, 1, 2, 3, 4,
> > 5, 6, 7, 8, 9,
> > 10, 11, 12, 13, 14,
> > 15, 16
> > ) ;> >
> > select select from select;> >
> > select from from select where and = 5 and or = 7 ;> >
> > delete from select where where = 10 and and = 20 ;> >
> > delete from select where into = 5;> >
> > update select set set = 20 where where = 5 and order = 7 and> > drop = 8 and and = 2 ;
> >
> > select or, and, update from select where and = 6 and or = and ;> >
> > select or, and, from from from select ;> >
> > select or and, and or, from from select ;
> >
> > select or select, and where, from from select;
> >
DA Morgan wrote:
> Interesting ... I tried this in Oracle ... couldn't find a single
> one that didn't raise an exception.
.. which is why I found the recent "TIMESTAMP" thread in c.d.o.server so
amusing.
Here is my submission:
Database Connection Information
Database server = DB2/NT 9.1.0
SQL authorization ID = SRIELAU
Local database alias = REGEXP
-- IDENTITY is only a property in DB2. Use a distinct type instead.
db2 => create distinct type serial as int with comparisons;
DB20000I The SQL command completed successfully.
db2 => create table select(
db2 (cont.) => serial serial,
db2 (cont.) => create int,
db2 (cont.) => table int,
db2 (cont.) => order int,
db2 (cont.) => select int,
db2 (cont.) => from int,
db2 (cont.) => where int,
db2 (cont.) => and int,
db2 (cont.) => or int,
db2 (cont.) => insert int,
db2 (cont.) => update int,
db2 (cont.) => in int,
db2 (cont.) => int int,
db2 (cont.) => delete int,
db2 (cont.) => drop int,
db2 (cont.) => into int,
db2 (cont.) => set int
db2 (cont.) => ) ;
DB20000I The SQL command completed successfully.
db2 => insert into select (
db2 (cont.) => serial, create, table, order, select,
db2 (cont.) => from, where, and, or, insert,
db2 (cont.) => update, in, int, delete, drop,
db2 (cont.) => into, set
db2 (cont.) => )
db2 (cont.) => values (
db2 (cont.) => 0, 1, 2, 3, 4,
db2 (cont.) => 5, 6, 7, 8, 9,
db2 (cont.) => 10, 11, 12, 13, 14,
db2 (cont.) => 15, 16
db2 (cont.) => ) ;
DB20000I The SQL command completed successfully.
db2 => select select from select;
SELECT
-----------
4
1 record(s) selected.
db2 => select from from select where and = 5 and or = 7 ;
FROM
-----------
0 record(s) selected.
db2 => delete from select where where = 10 and and = 20 ;
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
db2 => delete from select where into = 5;
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
db2 => update select set set = 20 where where = 5 and order = 7 and drop = 8
db2 (cont.) => and and = 2 ;
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
db2 => select or, and, update from select where and = 6 and or = and ;
OR AND UPDATE
----------- ----------- -----------
0 record(s) selected.
-- There is no table "FROM"... try again below..
db2 => select or, and, from from from select ;
SQL0204N "SRIELAU.FROM" is an undefined name. SQLSTATE=42704
db2 => select or and, and or, from from select ;
AND OR FROM
----------- ----------- -----------
8 7 5
1 record(s) selected.
db2 => select or select, and where, from from select;
SELECT WHERE FROM
----------- ----------- -----------
8 7 5
1 record(s) selected.
db2 => create alias from for select;
DB20000I The SQL command completed successfully.
-- OK that one I wasn't sure about..
db2 => select or, and, from from from select ;
OR AND FROM
----------- ----------- -----------
8 7 5
1 record(s) selected.
db2 =>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
So DB2 runs them all. This is tremendous. This is actually what I
expected and I was surprised that 2 of them didn't work.
Serge Rielau wrote:
> DA Morgan wrote:
> > Interesting ... I tried this in Oracle ... couldn't find a single
> > one that didn't raise an exception.
> .. which is why I found the recent "TIMESTAMP" thread in c.d.o.server so
> amusing.
>
> Here is my submission:
>
> Database Connection Information
>
> Database server = DB2/NT 9.1.0
> SQL authorization ID = SRIELAU
> Local database alias = REGEXP
>
> -- IDENTITY is only a property in DB2. Use a distinct type instead.
> db2 => create distinct type serial as int with comparisons;
> DB20000I The SQL command completed successfully.
> db2 => create table select(
> db2 (cont.) => serial serial,
> db2 (cont.) => create int,
> db2 (cont.) => table int,
> db2 (cont.) => order int,
> db2 (cont.) => select int,
> db2 (cont.) => from int,
> db2 (cont.) => where int,
> db2 (cont.) => and int,
> db2 (cont.) => or int,
> db2 (cont.) => insert int,
> db2 (cont.) => update int,
> db2 (cont.) => in int,
> db2 (cont.) => int int,
> db2 (cont.) => delete int,
> db2 (cont.) => drop int,
> db2 (cont.) => into int,
> db2 (cont.) => set int
> db2 (cont.) => ) ;
> DB20000I The SQL command completed successfully.
> db2 => insert into select (
> db2 (cont.) => serial, create, table, order, select,
> db2 (cont.) => from, where, and, or, insert,
> db2 (cont.) => update, in, int, delete, drop,
> db2 (cont.) => into, set
> db2 (cont.) => )
> db2 (cont.) => values (
> db2 (cont.) => 0, 1, 2, 3, 4,
> db2 (cont.) => 5, 6, 7, 8, 9,
> db2 (cont.) => 10, 11, 12, 13, 14,
> db2 (cont.) => 15, 16
> db2 (cont.) => ) ;
> DB20000I The SQL command completed successfully.
> db2 => select select from select;
>
> SELECT
> -----------
> 4
>
> 1 record(s) selected.
>
> db2 => select from from select where and = 5 and or = 7 ;
>
> FROM
> -----------
>
> 0 record(s) selected.
>
> db2 => delete from select where where = 10 and and = 20 ;
> SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
> query is an empty table. SQLSTATE=02000
> db2 => delete from select where into = 5;
> SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
> query is an empty table. SQLSTATE=02000
> db2 => update select set set = 20 where where = 5 and order = 7 and drop = 8
> db2 (cont.) => and and = 2 ;
> SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
> query is an empty table. SQLSTATE=02000
> db2 => select or, and, update from select where and = 6 and or = and ;
>
> OR AND UPDATE
> ----------- ----------- -----------
>
> 0 record(s) selected.
>
> -- There is no table "FROM"... try again below..
> db2 => select or, and, from from from select ;
> SQL0204N "SRIELAU.FROM" is an undefined name. SQLSTATE=42704
> db2 => select or and, and or, from from select ;
>
> AND OR FROM
> ----------- ----------- -----------
> 8 7 5
>
> 1 record(s) selected.
>
> db2 => select or select, and where, from from select;
>
> SELECT WHERE FROM
> ----------- ----------- -----------
> 8 7 5>
> 1 record(s) selected.
>
> db2 => create alias from for select;
> DB20000I The SQL command completed successfully.
>
> -- OK that one I wasn't sure about..
> db2 => select or, and, from from from select ;
>
> OR AND FROM
> ----------- ----------- -----------
> 8 7 5
>
> 1 record(s) selected.
>
> db2 =>
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> WAIUG Conference
> http://www.iiug.org/waiug/present/Forum2006/Forum2006.html