[
https://issues.apache.org/jira/browse/DERBY-7140?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Richard N. Hillegas updated DERBY-7140:
---------------------------------------
Attachment: derby-7140.sql
> 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
> Priority: Major
> Attachments: derby-7140.sql
>
>
> 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)
