Skip to content

Corrupted where clause on .one() with a complete object #813

@maritaria

Description

@maritaria
function defineDatabase(db) {
	let User = db.define("user", {
		name: { type: "text", required: true, unique: true },
		last_seen: { type: 'date', time: true },
		card_url: { type: 'text' },
	});
	
	let Group = db.define("group", {
		name: { type: "text", required: true },
		chat_id: { type: "text", required: true, unique: true },
	});
	Group.hasMany("members", User, {
			rank: [ "unverified", "member", "admin" ],
		}, { key: true });
	
	let Ticket = db.define("ticket", {
		//status: [ "started", "expired", "submitted", "completed", "denied" ],
		task: { type: "text" },
		pic: { type: "text" },
		accepted: { type: "boolean", default: false },
		
		started_at: { type: "date", required: true, time: true },
		finish_before: { type: "date", required: true, time: true },
		signed_at: { type: "date", required: false, time: true },
	}, {
		methods: { /* ... */
		},
	});
	Ticket.hasOne("user", User, { required: true, autoFetch: true });
	Ticket.hasOne("group", Group, { required: true, autoFetch: true });
	Ticket.hasOne("admin", User, { required: false, autoFetch: true });
	User.hasOne("currentTicket", Ticket, { required: false, autoFetch: true });//workaround: disable autofetch
}

The following code creates an error:

db.models.group.get(1, (err, someGroup) => {
someGroup.getMembers().one(user, (err, member) => {});
});

Creates the following SQL if user.currentTicket is set to an instance of Ticket:

(orm/mysql) DROP TABLE IF EXISTS `user`
(orm/mysql) DROP TABLE IF EXISTS `group`
(orm/mysql) DROP TABLE IF EXISTS `group_members`
(orm/mysql) DROP TABLE IF EXISTS `ticket`
(orm/mysql) SHOW TABLES LIKE 'user'
(orm/mysql) CREATE TABLE `user` (`name` VARCHAR(255) NOT NULL, `last_seen` DATETIME, `card_url` VARCHAR(255), `id` INT(11) NOT NULL AUTO_INCREMENT, `currentticket_id` INTEGER, PRIMARY KEY (`id`))
(orm/mysql) SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'fetguard' AND table_name = 'user'
(orm/mysql) CREATE UNIQUE INDEX `name_unique` ON `user` (`name`)
(orm/mysql) SHOW TABLES LIKE 'group'
(orm/mysql) CREATE TABLE `group` (`name` VARCHAR(255) NOT NULL, `chat_id` VARCHAR(255) NOT NULL, `id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))
(orm/mysql) SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'fetguard' AND table_name = 'group'
(orm/mysql) CREATE UNIQUE INDEX `chat_id_unique` ON `group` (`chat_id`)
(orm/mysql) SHOW TABLES LIKE 'group_members'
(orm/mysql) CREATE TABLE `group_members` (`group_id` INTEGER NOT NULL, `members_id` INTEGER NOT NULL, `rank` ENUM ('unverified','member','admin'), PRIMARY KEY (`group_id`, `members_id`))
(orm/mysql) SELECT index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'fetguard' AND table_name = 'group_members'
(orm/mysql) CREATE INDEX `group_id_index` ON `group_members` (`group_id`)
(orm/mysql) CREATE INDEX `members_id_index` ON `group_members` (`members_id`)
(orm/mysql) SHOW TABLES LIKE 'ticket'
(orm/mysql) CREATE TABLE `ticket` (`task` VARCHAR(255), `pic` VARCHAR(255), `accepted` TINYINT(1), `started_at` DATETIME NOT NULL, `finish_before` DATETIME NOT NULL, `signed_at` DATETIME, `id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INTEGER NOT NULL, `group_id` INTEGER NOT NULL, `admin_id` INTEGER, PRIMARY KEY (`id`))
(orm/mysql) INSERT INTO `group` (`name`, `chat_id`, `id`) VALUES ('Haven', '1', NULL)
(orm/mysql) INSERT INTO `group` (`name`, `chat_id`, `id`) VALUES ('Test', '2', NULL)
(orm/mysql) INSERT INTO `user` (`name`, `last_seen`, `card_url`, `id`, `currentticket_id`) VALUES ('maritaria', NULL, NULL, NULL, NULL)
(orm/mysql) INSERT INTO `user` (`name`, `last_seen`, `card_url`, `id`, `currentticket_id`) VALUES ('clltrldmg', NULL, NULL, NULL, NULL)
(orm/mysql) SELECT `name`, `chat_id`, `id` FROM `group` WHERE `name` = 'Test' LIMIT 1
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'maritaria' LIMIT 1
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'clltrldmg' LIMIT 1
(orm/mysql) DELETE FROM `group_members` WHERE `group_id` = 2 AND `members_id` = 1
(orm/mysql) DELETE FROM `group_members` WHERE `group_id` = 2 AND `members_id` = 2
(orm/mysql) INSERT INTO `group_members` (`rank`, `group_id`, `members_id`) VALUES ('unverified', 2, 1)
(orm/mysql) INSERT INTO `group_members` (`rank`, `group_id`, `members_id`) VALUES ('member', 2, 2)
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'maritaria' LIMIT 1
(orm/mysql) SELECT `name`, `chat_id`, `id` FROM `group` WHERE `chat_id` = '2' LIMIT 1
(orm/mysql) SELECT `t1`.`name`, `t1`.`last_seen`, `t1`.`card_url`, `t1`.`id`, `t1`.`currentticket_id`, `t2`.`rank` FROM `user` `t1` JOIN `group_members` `t2` ON `t2`.`members_id` = `t1`.`id` WHERE (`t2`.`group_id` = 2) AND (`name` = 'maritaria' AND `last_seen` IS NULL AND `card_url` IS NULL AND `id` = 1 AND `currentticket_id` IS NULL)
(orm/mysql) INSERT INTO `ticket` (`user_id`, `group_id`, `started_at`, `finish_before`, `task`, `pic`, `accepted`, `signed_at`, `id`, `admin_id`) VALUES (1, 2, '2017-10-02 18:28:10.272', '2017-10-02 18:29:10.273', 'PM me a live pic with a thumb on your nose', NULL, 0, NULL, NULL, NULL)
(orm/mysql) UPDATE `user` SET `currentticket_id` = 1 WHERE `id` = 1
(orm/mysql) SELECT `name`, `last_seen`, `card_url`, `id`, `currentticket_id` FROM `user` WHERE `name` = 'maritaria' LIMIT 1
(orm/mysql) SELECT `task`, `pic`, `accepted`, `started_at`, `finish_before`, `signed_at`, `id`, `user_id`, `group_id`, `admin_id` FROM `ticket` WHERE `id` = 1 LIMIT 1
(orm/mysql) SELECT `name`, `chat_id`, `id` FROM `group` WHERE `chat_id` = '2' LIMIT 1
(orm/mysql) SELECT `t1`.`name`, `t1`.`last_seen`, `t1`.`card_url`, `t1`.`id`, `t1`.`currentticket_id`, `t2`.`rank` FROM `user` `t1` JOIN `group_members` `t2` ON `t2`.`members_id` = `t1`.`id` WHERE (`t2`.`group_id` = 2) AND (`name` = 'maritaria' AND `last_seen` IS NULL AND `card_url` IS NULL AND `id` = 1 AND `currentticket_id` = 1 AND `currentTicket` = `task` = 'PM me a live pic with a thumb on your nose', `pic` = NULL, `accepted` = false, `started_at` = '2017-10-02 18:28:10.000', `finish_before` = '2017-10-02 18:29:10.000', `signed_at` = NULL, `id` = 1, `user_id` = 1, `group_id` = 2, `admin_id` = NULL)
Error: ER_BAD_FIELD_ERROR: Unknown column 'currentTicket' in 'where clause'

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions