[
https://issues.apache.org/jira/browse/PHOENIX-4869?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16596285#comment-16596285
]
Gardella Juan Pablo commented on PHOENIX-4869:
----------------------------------------------
[~tdsilva] I write an integration test but I was unable to reproduce it.
{code:java}
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.phoenix.tx;
import static org.junit.Assert.*;
import static org.junit.Assert.assertTrue;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.UUID;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.phoenix.end2end.BaseClientManagedTimeIT;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.google.common.base.Stopwatch;
public class Issue4869IT extends BaseClientManagedTimeIT {
private static final String DDL = "create table Issue4869IT (id VARCHAR
not null primary key, json varchar, ts timestamp)";
private static final int ROWS_TO_INSERT = 1_000_000;
private static final int LIMIT = 100_000;
private static final int OFFSET = 100_000;
@Before
public void setUp() throws SQLException {
createTestTable(getUrl(), DDL);
}
@After
public void after() throws Exception {
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.createStatement().execute("DROP INDEX
Issue4869IT_capturetime on Issue4869IT");
conn.createStatement().execute("DROP TABLE
Issue4869IT");
}
super.cleanUpAfterTest();
}
@Test
public void testReadOwnWrites() throws Exception {
String selectSql = "SELECT count(*) FROM Issue4869IT";
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.setAutoCommit(true);
try (ResultSet rs =
conn.createStatement().executeQuery(selectSql);) {
assertTrue(rs.next());
assertEquals(rs.getLong(1), 0L);
}
Stopwatch sw = new Stopwatch();
sw.start();
System.out.println("Inserting dataInserting data...");
String upsert = "UPSERT INTO Issue4869IT (id, json, ts)
VALUES(?,?,?)";
final Timestamp ts = new Timestamp(new
Date().getTime());
final String jsonData = RandomStringUtils.random(5000);
try (PreparedStatement stmt =
conn.prepareStatement(upsert);) {
for (int i = 0; i < ROWS_TO_INSERT; i++) {
stmt.setString(1,
UUID.randomUUID().toString());
stmt.setString(2, jsonData);
stmt.setTimestamp(3, ts);
stmt.addBatch();
if (i % 1000 == 0) {
stmt.executeBatch();
}
}
stmt.executeBatch();
}
conn.commit();
System.out.println(ROWS_TO_INSERT + " rows inserted in
" + sw);
conn.createStatement()
.execute(" CREATE INDEX
Issue4869IT_capturetime ON Issue4869IT (ts) INCLUDE(id,json)");
try (ResultSet rs =
conn.createStatement().executeQuery(selectSql);) {
assertTrue(rs.next());
assertEquals(ROWS_TO_INSERT, (int)
rs.getLong(1));
}
try (ResultSet rs = conn.createStatement()
.executeQuery("SELECT count(*) FROM
Issue4869IT where ID is null");) {
assertFalse(rs.next());// it should return 0
AFAIK (a bug for v4.7.0 )
// v4.8.0 works well
// assertTrue(rs.next());
// assertEquals(rs.getLong(1), 0L);
}
try (ResultSet rs = conn.createStatement()
.executeQuery(" explain select count(*)
from " + "(SELECT ID FROM Issue4869IT LIMIT " + LIMIT
+ " OFFSET " + OFFSET +
") " + "where ID is null");) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
try (ResultSet rs = conn.createStatement()
.executeQuery("select count(*) from " +
"(SELECT ID FROM Issue4869IT LIMIT " + LIMIT + " OFFSET "
+ OFFSET + ") " +
"where ID is null");) {
assertTrue(rs.next());
assertEquals(rs.getLong(1), 0L);
}
}
}
}
{code}
The test is very slow. Probably I have to export as CSV the table in the server
and load in a local Phoenix DB.
> Empty row when using OFFSET + LIMIT
> -----------------------------------
>
> Key: PHOENIX-4869
> URL: https://issues.apache.org/jira/browse/PHOENIX-4869
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.7.0
> Reporter: Gardella Juan Pablo
> Priority: Major
> Attachments: empty_row.png, no_results.png
>
>
> I'm using [Phoenix shipped
> |https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.1/bk_release-notes/content/patch_phoenix.html]at
> [HDP
> 2.6.1|https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.1/index.html]. I
> have a table defined as:
> {code:sql}
>
> create table test (
> id VARCHAR not null primary key,
> json VARCHAR,
> ts timestamp
> )
> CREATE INDEX testix ON test (ts ) INCLUDE(id,json);
> {code}
> It has 2559774 rows. If I execute the following query, it returns a row with
> a null value.
> {code:sql}
> select * from
> (
> SELECT ID
> FROM test
> LIMIT 100000 OFFSET 100000
> )
> where ID is null
> {code}
>
> I was reviewing the git logs and I didn't see any commit related to that[1].
> Notice the query for OFFSET and LIMIT lowers than 100000 does not fail. I've
> attached a capture of the query results.
> !empty_row.png!
> Notice if I execute SELECT ID FROM test WHERE ID IS NULL returns an empty
> result as expected.
> !no_results.png!
>
> Thread:
> [https://lists.apache.org/thread.html/fd54a0cf623a20ad54d1ac65656d01add8eeef74ad51fb1674afb566@%3Cuser.phoenix.apache.org%3E]
> [1] Similar but not equal is PHOENIX-3422. The results is no data instead of
> null row.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
