Re: reserved words in table names

Hi,
I'd like to note annoying behaviour in mysql 4.0. pre 11:

How-To-Repeat:

mysql> CREATE TABLE `double` (`e_value` double default NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into double (e_value) values (1e-254);
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double (e_value) values (1e-254)' at line 1
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''double'' at line 1
mysql> show tables;
+-------------------------+
| Tables_in_test_mmokrejs |
+-------------------------+
| double |
[...]

If "double" is reserverved, then such tablename should not be allowed too.


mysql> CREATE TABLE `d-screwed` (`e_value` double(308,30) default NULL);
ERROR 1074: Too big column length for column 'e_value' (max = 255). Use BLOB instead

Why BLOB?

mysql> CREATE TABLE `d-screwed` (`e_value` double(255,30) default NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into d-screwed (e_value) values (1e-254);
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-screwed (e_value) values (1e-254)' at line 1
mysql>

If mysql allowed me to create the table, then I should be able to access it, right?

mysql> CREATE TABLE `test222` (`e_value` double(255,256) unsigned default NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> describe table test222;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'table test222' at line 1
mysql> show tables;
+-------------------------+
| Tables_in_test_mmokrejs |
+-------------------------+
| test222 |
[...]

What's wrong with tablename test222?


BTW: I've once realized I had database "-h$hostname", where $hostname is the hostname
of mysql server. I suspect the handling of order of argument by mysqladmin makes it
interpret "-h$hostname" as a database name.

--
Martin Mokrejs ,
PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
MIPS / Institute for Bioinformatics
GSF - National Research Center for Environment and Health
Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
tel.: +49-89-3187 3683 , fax: +49-89-3187 3585

---end of your message-------

MySQL Development Team

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13919@lists.mysql.com
To unsubscribe, e-mail

Re: reserved words in table names

Martin,

Martin MOKREJ© wrote:
> Hi,


> mysql> CREATE TABLE `double` (`e_value` double default NULL);


> mysql> insert into double (e_value) values (1e-254);
> ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double (e_value) values (1e-254)' at line 1


> If "double" is reserverved, then such tablename should not be allowed too.

When you use Reserved Words as table or column names you mus always enclose them
in backticks.

mysql> insert into `double` (e_value) values (1e-254);
Query OK, 1 row affected (0.15 sec)

> mysql> CREATE TABLE `d-screwed` (`e_value` double(308,30) default NULL);
> ERROR 1074: Too big column length for column 'e_value' (max = 255). Use BLOB instead
>
> Why BLOB?

Isn't above message clear enough? Max allowed lenght of double() is 255

> mysql> CREATE TABLE `d-screwed` (`e_value` double(255,30) default NULL);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into d-screwed (e_value) values (1e-254);
> ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-screwed (e_value) values (1e-254)' at line 1

Backticks again :)

- is Minus Sign. Syntax A-B is interpreted as "Column A - Column B"

mysql> insert into `d-screwed` (e_value) values (1e-254);
Query OK, 1 row affected (0.03 sec)

> If mysql allowed me to create the table, then I should be able to access it, right?

Yes with backticks.
Creating and using such tables is possible, but you can see why it is not
recommended as it leads to many subtle troubles.

> mysql> describe table test222;
^^^^^
As it says below:
> ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'table test222' at line 1


> What's wrong with tablename test222?

Syntax is DESCRIBE tablename;
No TABLE keyword.

From manual:
6.6.2 DESCRIBE Syntax (Get Information About Columns)

{DESCRIBE | DESC} tbl_name [col_name | wild]

Best regards
--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13920@lists.mysql.com
To unsubscribe, e-mail

Re: reserved words in table names

On Fri, 7 Mar 2003, Alexander Keremidarski wrote:

Hi,
thanks for reply!

> > mysql> CREATE TABLE `double` (`e_value` double default NULL);
>
>
> > mysql> insert into double (e_value) values (1e-254);
> > ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'double (e_value) values (1e-254)' at line 1
>
>
> > If "double" is reserverved, then such tablename should not be allowed too.
>
> When you use Reserved Words as table or column names you mus always enclose them
> in backticks.

OK, I got the point. I should *always* use backticks in these cases, sorry
for bugging you on this list.

> mysql> insert into `double` (e_value) values (1e-254);
> Query OK, 1 row affected (0.15 sec)
>
> > mysql> CREATE TABLE `d-screwed` (`e_value` double(308,30) default NULL);
> > ERROR 1074: Too big column length for column 'e_value' (max = 255). Use BLOB instead
> >
> > Why BLOB?
>
> Isn't above message clear enough? Max allowed lenght of double() is 255

But what has double to do with blob? Yes, the '255' suggested was
clear enough, but the "BLOB" is just fooling.

>
> > mysql> CREATE TABLE `d-screwed` (`e_value` double(255,30) default NULL);
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql> insert into d-screwed (e_value) values (1e-254);
> > ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-screwed (e_value) values (1e-254)' at line 1
>
> Backticks again :)
>
> - is Minus Sign. Syntax A-B is interpreted as "Column A - Column B"

Oh, I didn't know about such feature. Cool. Now I know why it did not
work as I expected. ;)

>
> mysql> insert into `d-screwed` (e_value) values (1e-254);
> Query OK, 1 row affected (0.03 sec)
>
> > If mysql allowed me to create the table, then I should be able to access it, right?
>
> Yes with backticks.
> Creating and using such tables is possible, but you can see why it is not
> recommended as it leads to many subtle troubles.
>
> > mysql> describe table test222;

Ah, yes, I think more bofore posting this, sorry, my typo.

--
Martin Mokrejs ,
PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
MIPS / Institute for Bioinformatics
GSF - National Research Center for Environment and Health
Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
tel.: +49-89-3187 3683 , fax: +49-89-3187 3585

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13921@lists.mysql.com
To unsubscribe, e-mail

Re: reserved words in table names

Hi,

>>> mysql> CREATE TABLE `d-screwed` (`e_value` double(308,30) default
NULL);
>>> ERROR 1074: Too big column length for column 'e_value' (max = 255).
Use BLOB instead
>>> Why BLOB?
>> Isn't above message clear enough? Max allowed lenght of double() is 255
> But what has double to do with blob? Yes, the '255' suggested was
> clear enough, but the "BLOB" is just fooling.

I guess it's because MySQL prints out the same error message that was
originally intended for too long VARCHAR definitions, in which case the BLOB
suggestion makes perfect sense.


Rgds,
Indrek

--
MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuemoisa, Haapsalu, Estonia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13925@lists.mysql.com
To unsubscribe, e-mail