| Bug #4106 | ndb table, query with correlated subquery, wrong result | ||
|---|---|---|---|
| Submitted: | 11 Jun 2004 14:18 | Modified: | 10 Aug 2004 15:16 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 4.1 | OS: | Linux (Linux) |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
[11 Jun 2004 14:20]
Matthias Leich
test case for mysql-test-run (->mysqltest)
Attachment: ml_err6x.test (application/octet-stream, text), 1.01 KiB.
[15 Jun 2004 12:51]
Martin Skold
A quick analysis shows that storage engine is scanning table t1 twice (2 * 4 records), but query engine reports only one record the second time. Must be something implictly assumed in the semantics of handler calls. This query generates a scan of one table instance first (up to a limit) and then a scan of the other (same) table again up to a limit. If this query is rewritten as: select x1.a, x1.b from t1 x1, t1 x2 where x1.b = x2.b; the table is scanned one record from one instance, one from the other, and so forth. This give the correct result.
[15 Jun 2004 16:04]
Martin Skold
The problem is rnd_init(scan = true) means a started scan should be reset. Currently this returns an unexpected error, instead of resetting. A tested solution solution here is to define a new NdbScanOperation on the same table, but this requires remembering the columns to be returned from the scan, bounds (for ordered index scan), and scan filters (not yet used by handler). A better solution is to support reset on NdbResultSet which takes care of the above and that reuses the same NdbScanOperation. A new WL entry will be created for this.
[15 Jun 2004 16:16]
Martin Skold
WL #1873 "Add support for scan (NdbResultSet) reset" has been added.
[10 Aug 2004 15:16]
Martin Skold
Solved
Description: I have a simple correlated subquery on an ndb table, where I get a wrong result: create table t1 ( a int primary key, b varchar(30) ) ENGINE = ndb ; inserts .. select a,b from t1 ; a b 2 two 4 four 3 three 1 one select a, b FROM t1 outer_table where a = (select a from t1 where b = outer_table.b ) ; a b 2 two I would expect a b 2 two 4 four 3 three 1 one Additional observations: The table used within the outer query can be of any table type. It is essential, that the subquery table is of type ndb. My environment: Intel PC with Linux(SuSE 9.0), MySQL 4.1 source distrib.(last bk pull 2004-06-10) How to repeat: Please use my test file, copy it to mysql-test/t ./mysql-test-run --with-ndbcluster ml_err6x One hint: Try to decrease the memory consumption of the ndb engine. It will give you better chances to get the ndb cluster started (there seem to be timeouts) and it will accelerate the test execution: --> mysql-test/ndb/ndb_config_2_node.ini MaxNoOfConcurrentOperations: 10000 IndexMemory: 2000000 DataMemory: 6000000 This decreases the memory consumption of ndbd+ndb_mgmd from ~500 MB to much smaller values.