Closed aredridel closed 2 years ago
Hey @aredridel! Thanks a lot for the bug report. That looks weird and indeed like bad SQL generation.
There are a few things that would help me greatly debug this:
--explain 2
at the end (requires graphviz).--optimize=false
?octosql --version
)Whoops! Query was in the original post but I screwed up the markdown code fence. It's there now.
The schema:
CREATE TABLE `eav_attribute` (
`attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute Id',
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity Type Id',
`attribute_code` varchar(255) NOT NULL COMMENT 'Attribute Code',
`attribute_model` varchar(255) DEFAULT NULL COMMENT 'Attribute Model',
`backend_model` varchar(255) DEFAULT NULL COMMENT 'Backend Model',
`backend_type` varchar(8) NOT NULL DEFAULT 'static' COMMENT 'Backend Type',
`backend_table` varchar(255) DEFAULT NULL COMMENT 'Backend Table',
`frontend_model` varchar(255) DEFAULT NULL COMMENT 'Frontend Model',
`frontend_input` varchar(50) DEFAULT NULL COMMENT 'Frontend Input',
`frontend_label` varchar(255) DEFAULT NULL COMMENT 'Frontend Label',
`frontend_class` varchar(255) DEFAULT NULL COMMENT 'Frontend Class',
`source_model` varchar(255) DEFAULT NULL COMMENT 'Source Model',
`is_required` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Defines Is Required',
`is_user_defined` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Defines Is User Defined',
`default_value` text DEFAULT NULL COMMENT 'Default Value',
`is_unique` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Defines Is Unique',
`note` varchar(255) DEFAULT NULL COMMENT 'Note',
PRIMARY KEY (`attribute_id`),
UNIQUE KEY `UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE` (`entity_type_id`,`attribute_code`),
KEY `IDX_EAV_ATTRIBUTE_ENTITY_TYPE_ID` (`entity_type_id`),
CONSTRAINT `FK_EAV_ATTRIBUTE_ENTITY_TYPE_ID_EAV_ENTITY_TYPE_ENTITY_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=509 DEFAULT CHARSET=utf8mb3 COMMENT='Eav Attribute'
It does still happen with --optimize=false
:; octosql --version
octosql version 0.7.2
Additionally, showing the help when an error happens is weird and confused me a bit
Thanks for all the details! I'll try to reproduce this as soon as I have some time.
Awesome! Thank you for a really neat tool! I'm excited to use it.
I, unfortunately, wasn't able to reproduce this (the table works just fine for me).
Which version of MariaDB are you using? And which operating system?
I've also released octosql v0.7.3 and the mysql plugin 0.3.0, it includes support for smallints and verbose logging, you can install it by running octosql plugin install mysql@0.3.0
Running queries with the environment variable OCTOSQL_MYSQL_VERBOSE=1
will result in verbose logs that will be printed to ~/.octosql/logs.txt
. It'd be great if you could provide the contents of those logs for your query.
Ok, I found the bug.
If I'm not mistaken, then you have multiple copies of this table in various schemas on your database instance. Looks like that broke during schema inference and looking up fields. Same bug is in the Postgres plugin actually, so thanks for that!
I've just released version 0.4.0 of the MySQL plugin which should work.
Oh delightful! I'll test on Monday!
And indeed I do have the same table in multiple schemas.
This is now also fixed in Postgres, so closing this issue. Feel free to reopen if needed.
Any thoughts? Looks like it's generating invalid SQL there