MySQL Bugs: #47771: return a warning or ER_NOT_SUPPORTED

Bug #47771	return a warning or ER_NOT_SUPPORTED_YET for unsupported syntax
Submitted:	1 Oct 2009 18:33	Modified:	2 Dec 2010 16:48
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	5.0, 5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql accepts certain syntax but silently ignores it, leading to unexpected results.  here are some examples:

1) descending indexes
2) column prefixes for fulltext indexes
3) foreign keys with check constraints
4) foreign keys on tables that don't support it

we should be giving a warning at least.  if the dba wants to prevent
such syntax and raise an error, we need an option or sql_mode to implement it.

How to repeat:
#1)
drop table if exists d1;
create table d1(a int)engine=myisam;
create index a on d1(a desc);
show create table d1;

#2)
drop table if exists d1;
create table d1(a varchar(10),fulltext key(a(5)))engine=myisam;
show create table d1;

#3)
drop table if exists d2,d1;
create table d1(a int primary key)engine=innodb;
create table d2(a int primary key,
constraint a foreign key fk(a) references d1(a),
constraint b check(a>0))engine=innodb;
show create table d2;

#4)
drop table if exists d2,d1;
create table d1(a int primary key)engine=myisam;
create table d2(a int primary key,
constraint a foreign key fk(a) references d1(a))engine=myisam;
show create table d2;

Suggested fix:
return a warning or error for unsupported functionality (if the dba sets an option to enable it).

another ignored syntax is the PASSWORD option in CREATE TABLE.

I also suggest the max_tmp_tables variable is either implemented or disabled.

Also ignored is DROP CASCADE

Anyway, it is more worse. Here is a test case:

default engine=MyISAM

DROP TABLE if exists t1,t;
CREATE TABLE t(i INTEGER, PRIMARY KEY(i));
CREATE TABLE t1(i INTEGER, j INTEGER, PRIMARY KEY(i), FOREIGN KEY(j) REFERENCES t(i));

-- *args* used wrong engine ... wanted innodb

ALTER TABLE t engine=innodb;
ALTER TABLE t1 engine=innodb;

SHOW CREATE TABLE t1;

You will see that the foreign key constraint got lost.

another example is FORCE INDEX being silently ignored update statements.

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
"Index hints are accepted but ignored for UPDATE statements."

Another example is the OWNER clause of the CREATE SERVER option:

http://dev.mysql.com/doc/refman/5.1/en/create-server.html
"Note that the OWNER option is currently not applied, and has no effect on the ownership or operation of the server connection that is created."

As a change of behaviour it should be an R3.

another one...
according to bug #26367 , MIN_ROWS is not implemented for MyISAM

Another one:

http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html
"MEMORY is not currently implemented"

Gives uncomplete output, instead of MEMORY information:

mysql> show profile memory for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.018393 |

The behaviour is deliberate, and is documented.

For example
"These [ASC or DESC] keywords are ... parsed but ignored"
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

"The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB
storage engine ... For other storage engines, the clauses are parsed
but ignored. The CHECK clause is parsed but ignored by all storage engines."
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

is it okay to leave as a feature request to change the current implementation?

Certainly as a feature request this is valid,
and in my view -- which perhaps many MySQL
developers share -- "parse but ignore" behaviour
is something we should slowly move away from.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html
"The CHECK clause is parsed but ignored by all storage engines"

another user wondering why accepted syntax doesn't work.. bug #60398

And the number one search result for "parsed but ignored" on Google:
http://www.google.com/search?gcx=c&ix=c2&sourceid=chrome&ie=UTF-8&q=parsed+but+ignored

is dedicated to this bug ...
http://mysqlha.blogspot.com/2009/01/parsed-but-ignored.html

Shocked by this. From my comment on http://bugs.mysql.com/bug.php?id=13375:

People would expect that as the syntax is supported at the time of index creation, the index will be properly created.

What happens if you create an index with DESC? The index gets created as ASC? This is not creating what I have requested, so is a bug.

Were the DDL to fail so that you cannot create DESC indexes, adding in support for DESC indexes would be a feature request, don't you think?

So the bug is: DESC indexes created as ASC
So the enhancement is: DESC index support

Further, the MySQL workbench allows you to edit indexes so they are ASC or DESC - should this feature be removed from the workbench?

Having it accept it silently makes it seem that you have this functionality :)

Found another one!

http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_references
"The REFERENCES privilege currently is unused."

See also bug #66987

similar bug, you can name the primary key, but it is ignored:
http://bugs.mysql.com/bug.php?id=1956

In 5.5 in-line foreign-key references worked, but in 5.6 no longer: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html . If only one key is involved, that is more convenient and more transparent than a separate constraint-declaration.

Since this was a matter of the same old code in a newer version of MySQL, it is an especial disservice to the programmer to ignore hitherto accepted code--much better to issue a warning for all parsed but ignored code.

"The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored. MySQL only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification."

https://bugs.mysql.com/bug.php?id=38169 is a part of this

Also, Bug #92242

related: https://bugs.mysql.com/bug.php?id=96001