2

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

Counter Table Data structure

box table

Box atble data structure

box_type table box_type table data structure

Collection table collection table data structure

The query returns data on some data. Query returns data on previous db with same structure less 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)'

**It ssays : #2014 - Commands out of sync; you can't run this command now and 1690 BIGINT UNSIGNED value is out of range in '(<code>cdcblog_lottery_scanner</code>.<code>bt</code>.<code>end_val</code> - <code>cdcblog_lottery_scanner</code>.<code>bt</code>.<code>start_val</code>)'**

Here is my query... was working before now with new data having problem

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.

  • It's very difficult for us to provide answers with difficult to read graphics - but the real problem is that we can't copy and past data or table structures. Could you provide DDL (CREATE TABLE blah...) and DML (INSERT INTO TABLE blah VALUES(....). If there's a lot of data, you can provide it as a PasteBin or something - or an SQLFiddle if suitable. – Vérace Jan 29 '15 at 6:19
  • ok @Vérace sqlfiddle.com/#!2/528d2 – Jaimin MosLake Jan 29 '15 at 6:29
  • And the data that's required? What version of MySQL? – Vérace Jan 29 '15 at 6:31
  • 5.5.29 and data may i send you the sql file ? – Jaimin MosLake Jan 29 '15 at 6:34
1

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

NO_UNSIGNED_SUBTRACTION

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.

| improve this answer | |
  • This behavior is really not so egregious. Unsigned integer operations produce unsigned integer results. Declaring a value as unsigned is, as you point out, best reserved for surrogate keys, but it's less because MySQL is Doin' it Wrong™ and more because unsigned values are inappropriate arguments if you expect to do signed arithmetic operations. stackoverflow.com/questions/7221409/… – Michael - sqlbot Jan 30 '15 at 12:33
  • But, you may want to obtain a negative out of two positives - you can't have a stock level of less than 0, but I can imagine that you might want to set a strategic reserve to less than 0 - say if you knew a huge order was coming in and you wished to ensure that other orders couldn't "use up" your aritifical reserve. Or letters of credit and that sort of thing - it was just a bit of a "gotcha" for which MySQL is perhaps justifiably famous :-) – Vérace Jan 30 '15 at 12:38
  • Yes, I understand your point... my point is that from a low-level computer programming perspective, unsigned subtraction never gives negative numbers. 4 - 5 = 65535 for 16 bit unsigned. – Michael - sqlbot Jan 30 '15 at 12:47
  • To get even more technical, the way I see it is that unsigned ints don't form a group under subtraction!You disagree - but hey, that's fine! :-) – Vérace Jan 30 '15 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.