Gideon Zhao created DERBY-7140:
----------------------------------
Summary: Wrong result when joining a table multiple times and
using count()
Key: DERBY-7140
URL: https://issues.apache.org/jira/browse/DERBY-7140
Project: Derby
Issue Type: Bug
Components: SQL
Environment: ------------------ Java Information ------------------
Java Version: 11
Java Vendor: Oracle Corporation
Java home: C:\Libs\jdk-11
Java classpath: .\Console2.jar
OS name: Windows 10
OS architecture: amd64
OS version: 10.0
Java user name: gideo
Java user home: C:\Users\gideo
Java user dir: C:\Users\gideo\IdeaProjects\Console2\out\artifacts\Console2_jar
java.specification.name: Java Platform API Specification
java.specification.version: 11
java.runtime.version: 11+28
--------- Derby Information --------
[C:\Users\gideo\IdeaProjects\Console2\out\artifacts\Console2_jar\Console2.jar]
10.12.1.1 - (1704137)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------
------------------------------------------------------
Reporter: Gideon Zhao
If the SQL has 3 tables and uses the count(), and has a WHERE clause, then the
data of the third table will be wrong. Here is the sample code.
{code:java}
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection connection =
DriverManager.getConnection("jdbc:derby:test.db;create=true");
Statement statement = connection.createStatement();
try {
statement.execute("DROP TABLE TEST1");
} catch (Throwable ex){}
try {
statement.execute("DROP TABLE TEST2");
} catch (Throwable ex){}
statement.execute("CREATE TABLE TEST1 (\n" +
"ID INTEGER,\n" +
"COL1 VARCHAR(250),\n" +
"COL2 VARCHAR(30)\n" +
")");
statement.execute("INSERT INTO TEST1(ID, COL1, COL2) VALUES(1, 'join_key',
'test2_col2_value2')");
statement.execute("CREATE TABLE TEST2 (\n" +
"ID INTEGER,\n" +
"COL1 VARCHAR(250),\n" +
"COL2 VARCHAR(250),\n" +
"COL3 VARCHAR(250)\n" +
")");
statement.execute("INSERT INTO TEST2(ID, COL1, COL2, COL3) VALUES(1,
'join_key', 'test2_col2_value1', 'test2_col3_value1')");
statement.execute("INSERT INTO TEST2(ID, COL1, COL2, COL3) VALUES(2,
'join_key', 'test2_col2_value2', 'test2_col3_value2')");
statement.execute("INSERT INTO TEST2(ID, COL1, COL2, COL3) VALUES(3,
'join_key', 'test2_col2_value3', 'test2_col3_value3')");
String sql = "" +
"SELECT \n" +
" TEST1.COL2 AS TEST1_COL2, \n" +
" COUNT(*) AS COUNT,\n" +
" TEST2_1.COL3 AS TEST2_1_COL3, \n" +
" TEST2_2.COL3 AS TEST2_2_COL3, \n" +
" TEST2_3.COL3 AS TEST2_3_COL3\n" +
"FROM \n" +
" TEST1 \n" +
" LEFT JOIN TEST2 AS TEST2_3 ON (TEST2_3.COL1 = TEST1.COL1) AND
(TEST2_3.COL2 = 'test2_col2_value3')\n" +
" LEFT JOIN TEST2 AS TEST2_2 ON (TEST2_2.COL1 = TEST1.COL1) AND
(TEST2_2.COL2 = 'test2_col2_value2') \n" +
" LEFT JOIN TEST2 AS TEST2_1 ON (TEST2_1.COL1 = TEST1.COL1) AND
(TEST2_1.COL2 = 'test2_col2_value1') \n" +
"WHERE TEST2_1.COL3 = 'test2_col3_value1'\n" +
"GROUP BY \n" +
" TEST1.COL2, \n" +
" TEST2_1.COL3, \n" +
" TEST2_2.COL3, \n" +
" TEST2_3.COL3";
ResultSet resultSet = statement.executeQuery(sql);
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(metaData.getColumnLabel(i + 1) + " ");
}
System.out.println();
while (resultSet.next()) {
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(resultSet.getString(i + 1) + " ");
}
System.out.println();
} {code}
The output will be this:
||TEST1_COL2||TEST2_1_COL3||TEST2_2_COL3||TEST2_3_COL3||
|test2_col2_value2|test2_col3_value1|test2_col3_value2|test2_col3_value2|
You can see the 4th value is wrong, it should be "test2_col3_value3".
Note that if I remove the "count(*)" or the WHERE clause, the result will be
right.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
