8000 Query failing silently, returning empty set. · Issue #3 · infinidb/infinidb · GitHub
[go: up one dir, main page]
Skip to content

Query failing silently, returning empty set. #3

New issue
New issue
Closed
Closed
Query failing silently, returning empty set.#3

Description

@fgeller
fgeller
opened on Oct 13, 2014

Add tables and add data:

root@idb[localhost-InfiniDB]> show create table table1;
+--------+-------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                    |
+--------+-------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `t1_id` int(11) DEFAULT NULL
) ENGINE=InfiniDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@idb[localhost-InfiniDB]> show create table table2;
+--------+------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
  `id` int(11) DEFAULT NULL,
  `t1_id` int(11) DEFAULT NULL
) ENGINE=InfiniDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@idb[localhost-InfiniDB]> select * from table1;
+-------+
| t1_id |
+-------+
|     1 |
+-------+
1 row in set, 1 warning (0.01 sec)
root@idb[localhost-InfiniDB]> select * from table2;
+------+-------+
| id   | t1_id |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set, 1 warning (0.00 sec)

The following query is a simplified version of the query we actually use. We are filtering on the flag value which is always 1 in this simplified version. Also, the subqueries that are combined via UNION ALL are usually different but in this case are all the same and contain the same row.

SELECT COUNT(DISTINCT t.t1_id)
FROM (
SELECT t.t1_id,
MAX(CASE WHEN t.flag =  0 THEN 1 ELSE 1 END) AS f0,
MAX(CASE WHEN t.flag =  1 THEN 1 ELSE 1 END) AS f1,
MAX(CASE WHEN t.flag =  2 THEN 1 ELSE 1 END) AS f2,
MAX(CASE WHEN t.flag =  3 THEN 1 ELSE 1 END) AS f3,
MAX(CASE WHEN t.flag =  4 THEN 1 ELSE 1 END) AS f4,
MAX(CASE WHEN t.flag =  5 THEN 1 ELSE 1 END) AS f5,
MAX(CASE WHEN t.flag =  6 THEN 1 ELSE 1 END) AS f6,
MAX(CASE WHEN t.flag =  7 THEN 1 ELSE 1 END) AS f7,
MAX(CASE WHEN t.flag =  8 THEN 1 ELSE 1 END) AS f8,
MAX(CASE WHEN t.flag =  9 THEN 1 ELSE 1 END) AS f9,
MAX(CASE WHEN t.flag = 10 THEN 1 ELSE 1 END) AS f10,
MAX(CASE WHEN t.flag = 11 THEN 1 ELSE 1 END) AS f11,
MAX(CASE WHEN t.flag = 12 THEN 1 ELSE 1 END) AS f12,
MAX(CASE WHEN t.flag = 13 THEN 1 ELSE 1 END) AS f13,
MAX(CASE WHEN t.flag = 14 THEN 1 ELSE 1 END) AS f14,
MAX(CASE WHEN t.flag = 15 THEN 1 ELSE 1 END) AS f15,
MAX(CASE WHEN t.flag = 16 THEN 1 ELSE 1 END) AS f16,
MAX(CASE WHEN t.flag = 17 THEN 1 ELSE 1 END) AS f17,
MAX(CASE WHEN t.flag = 18 THEN 1 ELSE 1 END) AS f18,
MAX(CASE WHEN t.flag = 19 THEN 1 ELSE 1 END) AS f19,
MAX(CASE WHEN t.flag = 20 THEN 1 ELSE 1 END) AS f20,
MAX(CASE WHEN t.flag = 21 THEN 1 ELSE 1 END) AS f21,
MAX(CASE WHEN t.flag = 22 THEN 1 ELSE 1 END) AS f22,
MAX(CASE WHEN t.flag = 23 THEN 1 ELSE 1 END) AS f23,
MAX(CASE WHEN t.flag = 24 THEN 1 ELSE 1 END) AS f24,
MAX(CASE WHEN t.flag = 25 THEN 1 ELSE 1 END) AS f25,
MAX(CASE WHEN t.flag = 26 THEN 1 ELSE 1 END) AS f26,
MAX(CASE WHEN t.flag = 27 THEN 1 ELSE 1 END) AS f27,
MAX(CASE WHEN t.flag = 28 THEN 1 ELSE 1 END) AS f28,
MAX(CASE WHEN t.flag = 29 THEN 1 ELSE 1 END) AS f29,
MAX(CASE WHEN t.flag = 30 THEN 1 ELSE 1 END) AS f30,
MAX(CASE WHEN t.flag = 31 THEN 1 ELSE 1 END) AS f31
FROM ((
           SELECT t1.t1_id AS t1_id,  1 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  1 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  2 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  3 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  4 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  5 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  6 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  7 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  8 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id,  9 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 10 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 11 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 12 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 13 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 14 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 15 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 16 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 17 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 18 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 19 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 20 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 21 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 22 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 23 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 24 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 25 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 26 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 27 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 28 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 29 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 30 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1))
UNION ALL (SELECT t1.t1_id AS t1_id, 31 AS flag FROM table1 t1, table2 t2 WHERE t2.t1_id = t1.t1_id AND (t2.id = 1)))
AS t GROUP BY t.t1_id HAVING f0 = 1 AND f1 = 1 AND f2 = 1 AND f3 = 1 AND f4 = 1 AND f5 = 1 AND f6 = 1 AND f7 = 1 AND f8 = 1 AND f9 = 1 AND f10 = 1 AND f11 = 1 AND f12 = 1 AND f13 = 1 AND f14 = 1 AND f15 = 1 AND f16 = 1 AND f17 = 1 AND f18 = 1
) AS t;

IDB fails to return the expected row:

+-------------------------+
| COUNT(DISTINCT t.t1_id) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.24 sec)

If you drop the last AND f18 = 1, the query will return the 1 expected row.

This leads us to believe it is related to the query's complexity, but unfortunately we can't easily change the (generated) query structure. We ran calgettrace() but are not sure why the row count goes down to 0 in the last TNS stage:

HJS  PM   t1-t2 3342     -                 -   -   -   -----   -    
TNS  UM   -     -        -                 -   -   -   0.000   1    
BPS  PM   t2    3344     (id,t1_id)        0   2   0   0.002   1    
BPS  PM   t1    3342     (t1_id)           0   1   0   0.000   1    
HJS  PM   t1-t2 3342     -                 -   -   -   -----   -    
TNS  UM   -     -        -                 -   -   -   0.000   1    
TUS  UM   -     -        -                 -   -   -   0.023   32   
TAS  UM   -     -        -                 -   -   -   0.002   1    
THS  UM   -     -        -                 -   -   -   0.000   -    
TNS  UM   -     -        -                 -   -   -   0.000   0    
TAS  UM   -     -        -                 -   -   -   0.000   1

Any pointers on where to continue debugging this or which configuration setting might solve this would be greatly appreciated :)

This was tested on an AWS i2.4xlarge instance (16 cores, 128gb).

Activity

  1. rdempseyidb commented on Oct 13, 2014

    @rdempseyidb
    rdempseyidb
    on Oct 13, 2014

    Your results indicate a warning was returned. What is the output of 'show warnings;' ?

  2. fgeller commented on Oct 13, 2014

    @fgeller
    fgeller
    on Oct 13, 2014
    Author

    This is our output for show warnings after running the failing query:

    root@idb[localhost-InfiniDB]> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                    |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 9999 | Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-96; BlocksTouched-96; PartitionBlocksEliminated-0; MsgBytesIn-8KB; MsgBytesOut-29KB; Mode-Distributed |
+-------+------+-------------------------------------------------
8000
-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@idb[localhost-InfiniDB]>

    Can you help us in interpreting this?

  3. rdempseyidb commented on Oct 14, 2014

    @rdempseyidb
    rdempseyidb
    on Oct 14, 2014

    If you execute 'select calsettrace(256);' and then run the query do you get the right answer?

  4. fgeller commented on Oct 14, 2014

    @fgeller
    fgeller
    on Oct 14, 2014
    Author

    Hi,

    the results of the SELECT COUNT(... query does not change if we run select calsettrace(256); before running the query. The result for the calsettrace call is this:

    root@idb[localhost-InfiniDB]> select calsettrace(256);
+------------------+
| calsettrace(256) |
+------------------+
|              256 |
+------------------+
1 row in set (0.00 sec)

  5. fgeller commented on Oct 14, 2014

    @fgeller
    fgeller
    on Oct 14, 2014
    Author

    Can you point us to documentation what that call does?
    Do you have any other pointers on how do debug it? Thank you very much for your help!

  6. fgeller commented on Oct 15, 2014

    @fgeller
    fgeller
    on Oct 15, 2014
    Author

    Doing a show warnings after a select calsettrace(256); returns:

    root@idb[localhost-InfiniDB]> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1051 | Unknown table '$vtable_10' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

    Does this maybe help?

  7. rdempseyidb commented on Oct 17, 2014

    @rdempseyidb
    rdempseyidb
    on Oct 17, 2014

    caltrace 256 turns off partition metadata processing. Sometimes the metadata gets out of sync with the actual column contents. This flag can help determine if that's the case. If it is, 'editem -C' will reset all the metadata and cause it to be recalculated on the next scan.

    The only useful flags are 1 and 256.

    Unfortunately, in your case, this does not seem to be the problem. Somewhere in the tuple processing something's going wrong.
    TAS = TupleAggregateStep
    TNS = TupleAnnexStep
    THS = TupleHavingStep

    You can look in dbcon/joblist at these classes and step thru the RowGroup processing, but it's going to be messy.

    I understand that the query is generated, but changing the generator is likely an easier solution.

  8. rdempseyidb
    closed this as completedon Oct 17, 2014

  9. awakmu commented on Nov 24, 2014

    @awakmu
    awakmu
    on Nov 24, 2014

    What the status of this issue?
    It is really a bug, isn't it?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions
      0