Question :
My table structure is … There is Counter which has many Box in it. Box has a one BoxType. and Collection stores Box datewise revenue and data. I need data of particular 1 COUNTER (Data of All the box belongs to it.) .
counter table
box table
box_type table
Collection table
The query returns data on some data.
Query not working on new inserted data. It ssays : #2014 – Commands out of sync; you can’t run this command now and 1690 BIGINT UNSIGNED value is out of range in ‘(bt.end_val–bt.start_val)’
SELECT `b`.`box_id`,
`bt`.`box_type_id`,
`bt`.`price`,
`bt`.`start_val`,
`bt`.`end_val`,
((`bt`.`end_val` - `bt`.`start_val`) + 1) AS totalTickets,
(CASE
WHEN (SELECT SUM(`co1`.`sold_tickets`)
FROM `collections` `co1`
INNER JOIN `boxes` `b1`
ON `b1`.`box_id` = `co1`.`box_id`
AND `b1`.`status` = "1"
INNER JOIN `counters` `c1`
ON `c1`.`counter_id` = `b1`.`counter_id`
AND `c1`.`status` = "1"
INNER JOIN `box_types` `bt1`
ON `bt1`.`box_type_id` = `b1`.`box_type_id`
AND `bt1`.`status` = "1"
WHERE `co1`.`status` = "1" AND `b1`.`box_id` = `b`.`box_id`)
IS NULL
THEN
0
ELSE
(SELECT SUM(`co1`.`sold_tickets`)
FROM `collections` `co1`
INNER JOIN `boxes` `b1`
ON `b1`.`box_id` = `co1`.`box_id`
AND `b1`.`status` = "1"
INNER JOIN `counters` `c1`
ON `c1`.`counter_id` = `b1`.`counter_id`
AND `c1`.`status` = "1"
INNER JOIN `box_types` `bt1`
ON `bt1`.`box_type_id` = `b1`.`box_type_id`
AND `bt1`.`status` = "1"
WHERE `co1`.`status` = "1" AND `b1`.`box_id` = `b`.`box_id`)
END)
AS soldTickets,
( ((`bt`.`end_val` - `bt`.`start_val`) + 1)
- (CASE
WHEN (SELECT SUM(`co1`.`sold_tickets`)
FROM `collections` `co1`
INNER JOIN `boxes` `b1`
ON `b1`.`box_id` = `co1`.`box_id`
AND `b1`.`status` = "1"
INNER JOIN `counters` `c1`
ON `c1`.`counter_id` = `b1`.`counter_id`
AND `c1`.`status` = "1"
INNER JOIN `box_types` `bt1`
ON `bt1`.`box_type_id` = `b1`.`box_type_id`
AND `bt1`.`status` = "1"
WHERE `co1`.`status` = "1"
AND `b1`.`box_id` = `b`.`box_id`)
IS NULL
THEN
0
ELSE
(SELECT SUM(`co1`.`sold_tickets`)
FROM `collections` `co1`
INNER JOIN `boxes` `b1`
ON `b1`.`box_id` = `co1`.`box_id`
AND `b1`.`status` = "1"
INNER JOIN `counters` `c1`
ON `c1`.`counter_id` = `b1`.`counter_id`
AND `c1`.`status` = "1"
INNER JOIN `box_types` `bt1`
ON `bt1`.`box_type_id` = `b1`.`box_type_id`
AND `bt1`.`status` = "1"
WHERE `co1`.`status` = "1"
AND `b1`.`box_id` = `b`.`box_id`)
END))
AS remainingTickets
FROM `collections` `co`
INNER JOIN `boxes` `b`
ON `b`.`box_id` = `co`.`box_id` AND `b`.`status` = "1"
INNER JOIN `counters` `c`
ON `c`.`counter_id` = `b`.`counter_id` AND `c`.`status` = "1"
INNER JOIN `box_types` `bt`
ON `bt`.`box_type_id` = `b`.`box_type_id` AND `bt`.`status` = "1"
WHERE `co`.`status` = "1" AND `b`.`counter_id` = "1"
GROUP BY `b`.`box_id`
ORDER BY `b`.`box_id` ASC
How do i improve this query for consistent performance and is there a better way of doing this thing.
Answer :
I don’t think the problem is with your query, but rather with your data. Check out the error message
It ssays : #2014 – Commands out of sync; you can’t run this command
now and 1690 BIGINT UNSIGNED value is out of range in
‘(bt.end_val-bt.start_val)’
I think for some reason, now (bad data entry?), your query is trying to insert a negative value into an UNSIGNED INT which is not allowed.
[EDIT]
I found it strange that you couldn’t subract one integer from another so I tested with a sample data set.
mysql> CREATE TABLE `test1` (
-> `start_val` int(10) unsigned NOT NULL,
-> `end_val` int(10) unsigned NOT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.50 sec)
mysql> insert into test1 values(3, 4);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test1 values(4, 3);
Query OK, 1 row affected (0.05 sec)
So, two simple records in a simple table.
Then I ran this very simple query:
mysql> select end_val - start_val from test1;
and INCREDIBLY I got the result:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test1`.`end_val` - `test`.`test1`.`start_val`)
I investigated and found that there is a setting
By default, subtraction between integer operands produces an UNSIGNED
result if any operand isUNSIGNED.
This has to be switched off if you with to subtract UNSIGNED INT values. I consider this to be a bug in MySQL – all the more egregious since there are no CHECK constraints in MySQL – but one could have plenty of reasons to wish to subtract two positve integers and obtain a negative result (debts, whatnot), but you can’t do that in MySQL. Absolutely incredible! Unsigned INTs should not be used for anything but PRIMARY KEYs.