MySQL Bugs: #33667: Falcon: CREATE ... SELECT + ANSI

Bug #33667	Falcon: CREATE ... SELECT + ANSI_QUOTES = error 156
Submitted:	3 Jan 2008 18:14	Modified:	5 May 2008 18:07
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4-p2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE ... SELECT on a Falcon table with ANSI_QUOTES SQL mode results in an error.

Innodb is not affected.

How to repeat:
SET SQL_MODE = 'ANSI_QUOTES';
CREATE TABLE t1 ( "blah" INT ) engine = falcon;
CREATE TABLE t2 engine = falcon SELECT "blah" - 1 FROM t1;

results in

ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 156)

Thank you for the bug report.

c:\dbs>6.0\bin\mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.5-alpha-main-tree-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET SQL_MODE = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( "blah" INT ) engine = falcon;
Query OK, 0 rows affected (0.42 sec)

mysql> CREATE TABLE t2 engine = falcon SELECT "blah" - 1 FROM t1;
ERROR 1005 (HY000): Can't create table 'db1.t2' (errno: 156)
mysql>

I think this is more general. On WinXP, with 6.0.4 (the first build), I get:
mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 6.0.4-alpha-community        |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | ia32                         |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql> create table t1 (c1 int) engine=falcon;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2 engine=falcon select c1 from t1;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 156)
mysql> create table t2 engine=falcon select * from t1;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 156)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.02 sec)

mysql> create table t2 engine=falcon select c1 from t1;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 156)

Setting back to Open to be re-verified on latest build.

The initial issue reported in this bug:

CREATE TABLE t2 engine = falcon SELECT "blah" - 1 FROM t1;

is a duplicate of bug #33657 . The Windows issue must be something separate since it does not involve any string literals.

Jeffrey, I do not have any problems with the generic case on windows.
mysql> create table t1 (c1 int) engine=falcon;
Query OK, 0 rows affected (1.14 sec)

mysql> create table t2 engine=falcon select c1 from t1;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.09 sec)

mysql> create table t3 engine=falcon select c1 from t1;
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table t4 engine=falcon select * from t1;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

Still repeatable on latest source main and team tree:

c:\dbs>6.0\bin\mysqladmin.exe -uroot create db7

c:\dbs>6.0\bin\mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.5-alpha-main-tree-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET SQL_MODE = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( "blah" INT ) engine = falcon;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 engine = falcon SELECT "blah" - 1 FROM t1;
ERROR 1005 (HY000): Can't create table 'db7.t2' (errno: 156)
mysql>

c:\dbs>6.0t\bin\mysql -uroot db5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.5-alpha-team-tree-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET SQL_MODE = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( "blah" INT ) engine = falcon;
Query OK, 0 rows affected (0.38 sec)

mysql> CREATE TABLE t2 engine = falcon SELECT "blah" - 1 FROM t1;
ERROR 1005 (HY000): Can't create table 'db5.t2' (errno: 156)
mysql>

Falcon gets tripped up on the field called ""BLAH" -1".  The create statement fails.  You can create t1 as either falcon, innodb or myisam, but 
create t2 engine = falcon select "blah" - 1 from t1; will fail.
create t2 engine = falcon select * from t1; will succeed.

InnoDB is doing a more sophisticated job of converting the field name;
mysql> show create table i2;
+-------+----------------------------------
| Table | Create Table
+-------+----------------------------------
| i2    | CREATE TABLE "i2" (
  """blah"" - 1" bigint(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------
mysql> select * from i2;
+------------+
| "blah" - 1 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

Falcon, on the other hand, tries to create this file internally;
sql =  "create table "TEST"."F7" (""BLAH" - 1" bigint) tablespace "FALCON_USER""

If Falcon could do a better job of handling embedded quotes in the function;
StorageTableShare::cleanupFieldName() this could be made to work.

Fix field name cleanup to handling identifiers with embedded double quotes.

Fixed in 6.0.4-alpha

Noted in 6.0.4 changelog.

For table creation, Falcon did not handle identifiers with embedded
double quotes.