QuerySyntaxError for quoted comment when comment pulled from foreign key

[iamamutt]

trafficstars

Bug Report

Description

A key/attribute comment in a table definition can be quoted if not used as a foreign key but once used as a foreign key the child table will fail when trying to be created on the database.

Reproducibility

Include:

• OS MACOS
• Python Version Python 3.9.7
• MySQL Version Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper
• MySQL Deployment Strategy local-docker
• DataJoint Version 0.13.2

Minimum number of steps

This will work:

import datajoint as dj

dj.config["database.user"] = "root"
dj.config["database.password"] = "simple"

schema = dj.Schema("test")


@schema
class TableA(dj.Lookup):
    definition = """
    # Parent table
    some_key : int # A "quoted" comment will work here if no children.
    """

TableA.heading
TableA()

# Parent table
some_key             : int                          # A "quoted" comment will work here if no children.

*some_key
+----------+

 (Total: 0)

This will fail:

import datajoint as dj

dj.config["database.user"] = "root"
dj.config["database.password"] = "simple"

schema = dj.Schema("test")


@schema
class TableA(dj.Lookup):
    definition = """
    # Parent table
    some_key : int # A "quoted" comment will work here if no children.
    """


@schema
class TableB(dj.Lookup):
    definition = """
    -> TableA
    """

QuerySyntaxError:

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 'quoted" comment will work here if no
children.", PRIMARY KEY (`some_key`), FOREI' at line 2

CREATE TABLE IF NOT EXISTS `test`.`#table_b` (
`some_key` int NOT NULL COMMENT "A "quoted" comment will work here if no children.",
PRIMARY KEY (`some_key`),
FOREIGN KEY (`some_key`) REFERENCES `test`.`table_a` (`some_key`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB, COMMENT ""

Complete error stack

---------------------------------------------------------------------------
QuerySyntaxError                          Traceback (most recent call last)
~/Dropbox/datajoint/U24/pkg-templates/element-template/element-template/scratch6.py in <module>
----> 40 from scratch6 import *

~/Dropbox/datajoint/U24/pkg-templates/element-template/element-template/scratch6.py in <module>
     16 
     17 @schema
---> 18 class TableB(dj.Lookup):
     19     definition = """
     20     -> TableA

~/opt/conda/envs/element-template/lib/python3.9/site-packages/datajoint/schemas.py in __call__(self, cls, context)
    145             raise DataJointError('The schema decorator should not be applied to Part relations')
    146         if self.is_activated():
--> 147             self._decorate_master(cls, context)
    148         else:
    149             self.declare_list.append((cls, context))

~/opt/conda/envs/element-template/lib/python3.9/site-packages/datajoint/schemas.py in _decorate_master(self, cls, context)
    155         :param context: the class' declaration context
    156         """
--> 157         self._decorate_table(cls, context=dict(context, self=cls, **{cls.__name__: cls}))
    158         # Process part tables
    159         for part in ordered_dir(cls):

~/opt/conda/envs/element-template/lib/python3.9/site-packages/datajoint/schemas.py in _decorate_table(self, table_class, context, assert_declared)
    186             if not self.create_tables or assert_declared:
    187                 raise DataJointError('Table `%s` not declared' % instance.table_name)
--> 188             instance.declare(context)
    189         is_declared = is_declared or instance.is_declared
    190 

~/opt/conda/envs/element-template/lib/python3.9/site-packages/datajoint/table.py in declare(self, context)
     83             for store in external_stores:
     84                 self.connection.schemas[self.database].external[store]
---> 85             self.connection.query(sql)
     86         except AccessError:
     87             # skip if no create privilege

~/opt/conda/envs/element-template/lib/python3.9/site-packages/datajoint/connection.py in query(self, query, args, as_dict, suppress_warnings, reconnect)
    298         cursor = self._conn.cursor(cursor=cursor_class)
    299         try:
--> 300             self._execute_query(cursor, query, args, suppress_warnings)
    301         except errors.LostConnectionError:
    302             if not reconnect:

~/opt/conda/envs/element-template/lib/python3.9/site-packages/datajoint/connection.py in _execute_query(cursor, query, args, suppress_warnings)
    264                 cursor.execute(query, args)
    265         except client.err.Error as err:
--> 266             raise translate_query_error(err, query)
    267 
    268     def query(self, query, args=(), *, as_dict=False, suppress_warnings=True, reconnect=None):

QuerySyntaxError: ('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 \'quoted" comment will work here if no children.",\nPRIMARY KEY (`some_key`),\nFOREI\' at line 2', 'CREATE TABLE IF NOT EXISTS `test`.`#table_b` (\n`some_key` int NOT NULL COMMENT "A "quoted" comment will work here if no children.",\nPRIMARY KEY (`some_key`),\nFOREIGN KEY (`some_key`) REFERENCES `test`.`table_a` (`some_key`) ON UPDATE CASCADE ON DELETE RESTRICT\n) ENGINE=InnoDB, COMMENT ""')