[jira] [Updated] (DERBY-7096) performance in `fetch first n rows only `

2021-01-07 Thread JackLi0812 (Jira) 


 [ 
https://issues.apache.org/jira/browse/DERBY-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

JackLi0812 updated DERBY-7096:
--
Issue & fix info:   (was: High Value Fix)

> performance in `fetch first n rows only `
> -
>
> Key: DERBY-7096
> URL: https://issues.apache.org/jira/browse/DERBY-7096
> Project: Derby
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 10.11.1.1, 10.14.2.0
> Environment: windows 10.
> org.apache.derby:derby:10.11.1.1
> java
>Reporter: JackLi0812
>Priority: Major
>
> > When I use 'fetch first n rows only' in A cascading query, performance 
> >drops dramatically
> > Without FETCH FIRST, it takes only a dozen milliseconds, but using this 
> >syntax will result in a query that takes 7min+.
> ```sql
> SELECT *
>  from (
>  select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
>  (
>  select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
>  (
>  select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
>  (
>  select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
>  (
>  select * from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",
>  (
>  select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
>  (
>  select * from SA.ORDER_DETAILS fetch first 5 rows only) 
> "SA.ORDER_DETAILS",
>  (
>  select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
> "SA.SALES_EMPLOYEES",
>  (
>  select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
>  (
>  select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region"
>  where "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
>  and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
>  and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
>  and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
>  and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
>  and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
>  and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
>  and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
>  and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson Region".REGION_ID          
>                                                                    
> ```



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (DERBY-7096) performance in `fetch first n rows only `

2021-01-07 Thread JackLi0812 (Jira) 


 [ 
https://issues.apache.org/jira/browse/DERBY-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

JackLi0812 updated DERBY-7096:
--
Description: 
> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

> Without FETCH FIRST, it takes only a dozen milliseconds, but using this 
>syntax will result in a query that takes 7min+.

```sql

SELECT *
 from (
 select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
 (
 select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
 (
 select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
 (
 select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
 (
 select * from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",
 (
 select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
 (
 select * from SA.ORDER_DETAILS fetch first 5 rows only) "SA.ORDER_DETAILS",
 (
 select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
"SA.SALES_EMPLOYEES",
 (
 select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
 (
 select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region"
 where "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
 and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
 and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
 and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
 and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
 and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
 and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
 and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
 and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson Region".REGION_ID            
                                                                 

```

  was:
> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

```sql

SELECT *
 from (
 select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
 (
 select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
 (
 select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
 (
 select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
 (
 select * from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",
 (
 select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
 (
 select * from SA.ORDER_DETAILS fetch first 5 rows only) "SA.ORDER_DETAILS",
 (
 select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
"SA.SALES_EMPLOYEES",
 (
 select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
 (
 select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region"
 where "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
 and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
 and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
 and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
 and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
 and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
 and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
 and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
 and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson Region".REGION_ID            
                                                                  

```


> performance in `fetch first n rows only `
> -
>
> Key: DERBY-7096
> URL: https://issues.apache.org/jira/browse/DERBY-7096
> Project: Derby
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 10.11.1.1, 10.14.2.0
> Environment: windows 10.
> org.apache.derby:derby:10.11.1.1
> java
>Reporter: JackLi0812
>Priority: Major
>
> > When I use 'fetch first n rows only' in A cascading query, performance 
> >drops dramatically
> > Without FETCH FIRST, it takes only a dozen milliseconds, but using this 
> >syntax will result in a query that takes 7min+.
> ```sql
> SELECT *
>  from (
>  select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
>  (
>  select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
>  (
>  select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
>  (
>  select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
>  (
>  select * from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",
>  (
>  select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
>  (
>  select * from SA.ORDER_DETAILS fetch first 5 rows only) 
> "SA.ORDER_DETAILS",
>  (
>  select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
> "SA.SALES_EMPLOYEES",
>  (
>  select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
>  (
>  select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region"
>  where "SA.CONTACTS".CUSTOMER_ID =

[jira] [Updated] (DERBY-7096) performance in `fetch first n rows only `

2021-01-07 Thread JackLi0812 (Jira) 


 [ 
https://issues.apache.org/jira/browse/DERBY-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

JackLi0812 updated DERBY-7096:
--
Description: 
> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

```sql

SELECT *
 from (
 select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
 (
 select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
 (
 select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
 (
 select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
 (
 select * from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",
 (
 select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
 (
 select * from SA.ORDER_DETAILS fetch first 5 rows only) "SA.ORDER_DETAILS",
 (
 select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
"SA.SALES_EMPLOYEES",
 (
 select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
 (
 select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region"
 where "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
 and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
 and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
 and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
 and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
 and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
 and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
 and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
 and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson Region".REGION_ID            
                                                                  

```

  was:
> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

``` sql

SELECT *
from   (
select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
(
select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
(
select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
(
select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
(
select * from SA.CATEGORIES fetch first 5 rows only) 
"SA.CATEGORIES",
(
select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
(
select * from SA.ORDER_DETAILS fetch first 5 rows only) 
"SA.ORDER_DETAILS",
(
select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
"SA.SALES_EMPLOYEES",
(
select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
(
select * from SA.REGIONS fetch first 5 rows only) "Salesperson 
Region"
 where  "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson 
Region".REGION_ID                                                               
               

```


> performance in `fetch first n rows only `
> -
>
> Key: DERBY-7096
> URL: https://issues.apache.org/jira/browse/DERBY-7096
> Project: Derby
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 10.11.1.1, 10.14.2.0
> Environment: windows 10.
> org.apache.derby:derby:10.11.1.1
> java
>Reporter: JackLi0812
>Priority: Major
>
> > When I use 'fetch first n rows only' in A cascading query, performance 
> >drops dramatically
> ```sql
> SELECT *
>  from (
>  select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
>  (
>  select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
>  (
>  select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
>  (
>  select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
>  (
>  select * from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",
>  (
>  select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
>  (
>  select * from SA.ORDER_DETAILS fetch first 5 rows only) 
> "SA.ORDER_DETAILS",
>  (
>  select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
> "SA.SALES_EMPLOYEES",
>  (
>  select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
>  (
>  select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region"
>  where "SA.CONTACTS".CUSTOMER_ID =

[jira] [Updated] (DERBY-7096) performance in `fetch first n rows only `

2021-01-07 Thread JackLi0812 (Jira) 


 [ 
https://issues.apache.org/jira/browse/DERBY-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

JackLi0812 updated DERBY-7096:
--
Description: 
> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

``` sql

SELECT *
from   (
select * from SA.CUSTOMERS fetch first 5 rows only) "SA.CUSTOMERS",
(
select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
(
select * from SA.REGIONS fetch first 5 rows only) "Customer Region",
(
select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
(
select * from SA.CATEGORIES fetch first 5 rows only) 
"SA.CATEGORIES",
(
select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
(
select * from SA.ORDER_DETAILS fetch first 5 rows only) 
"SA.ORDER_DETAILS",
(
select * from SA.SALES_EMPLOYEES fetch first 5 rows only) 
"SA.SALES_EMPLOYEES",
(
select * from SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",
(
select * from SA.REGIONS fetch first 5 rows only) "Salesperson 
Region"
 where  "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson 
Region".REGION_ID                                                               
               

```

  was:
> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

``` sql

SELECT *SELECT *from   (        select * from SA.CUSTOMERS fetch first 5 
rows only) "SA.CUSTOMERS",        (        select * from SA.CONTACTS fetch 
first 5 rows only) "SA.CONTACTS",        (        select * from SA.REGIONS 
fetch first 5 rows only) "Customer Region",        (        select * from 
SA.ORDERS fetch first 5 rows only) "SA.ORDERS",        (        select * 
from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",        (       
 select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",        ( 
       select * from SA.ORDER_DETAILS fetch first 5 rows only) 
"SA.ORDER_DETAILS",        (        select * from SA.SALES_EMPLOYEES fetch 
first 5 rows only) "SA.SALES_EMPLOYEES",        (        select * from 
SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",        (        
select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region" 
where  "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID  and 
"SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID  and 
"SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID  and "SA.ORDERS".ORDER_ID 
= "SA.ORDER_DETAILS".ORDER_ID  and "SA.PRODUCTS".CATEGORY_ID = 
"SA.CATEGORIES".CATEGORY_ID  and "SA.ORDER_DETAILS".PRODUCT_ID = 
"SA.PRODUCTS".PRODUCT_ID  and "SA.ORDERS".EMPLOYEE_ID = 
"SA.SALES_EMPLOYEES".EMPLOYEE_ID  and "SA.PRODUCTS".SUPPLIER_ID = 
"SA.SUPPLIERS".SUPPLIER_ID  and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson 
Region".REGION_ID                                                               
                                                       

```


> performance in `fetch first n rows only `
> -
>
> Key: DERBY-7096
> URL: https://issues.apache.org/jira/browse/DERBY-7096
> Project: Derby
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 10.11.1.1, 10.14.2.0
> Environment: windows 10.
> org.apache.derby:derby:10.11.1.1
> java
>Reporter: JackLi0812
>Priority: Major
>
> > When I use 'fetch first n rows only' in A cascading query, performance 
> >drops dramatically
> ``` sql
> SELECT *
> from   (
> select * from SA.CUSTOMERS fetch first 5 rows only) 
> "SA.CUSTOMERS",
> (
> select * from SA.CONTACTS fetch first 5 rows only) "SA.CONTACTS",
> (
> select * from SA.REGIONS fetch first 5 rows only) "Customer 
> Region",
> (
> select * from SA.ORDERS fetch first 5 rows only) "SA.ORDERS",
> (
> select * from SA.CATEGORIES fetch first 5 rows only) 
> "SA.CATEGORIES",
> (
> select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",
> (
> select * from SA.ORDER_DETAILS fetch first 5 rows only) 
> "SA.ORDER_DETAILS",
> (
>

[jira] [Created] (DERBY-7096) performance in `fetch first n rows only `

2021-01-07 Thread JackLi0812 (Jira) 
JackLi0812 created DERBY-7096:
-

 Summary: performance in `fetch first n rows only `
 Key: DERBY-7096
 URL: https://issues.apache.org/jira/browse/DERBY-7096
 Project: Derby
  Issue Type: Improvement
  Components: SQL
Affects Versions: 10.14.2.0, 10.11.1.1
 Environment: windows 10.
org.apache.derby:derby:10.11.1.1
java
Reporter: JackLi0812


> When I use 'fetch first n rows only' in A cascading query, performance drops 
>dramatically

``` sql

SELECT *SELECT *from   (        select * from SA.CUSTOMERS fetch first 5 
rows only) "SA.CUSTOMERS",        (        select * from SA.CONTACTS fetch 
first 5 rows only) "SA.CONTACTS",        (        select * from SA.REGIONS 
fetch first 5 rows only) "Customer Region",        (        select * from 
SA.ORDERS fetch first 5 rows only) "SA.ORDERS",        (        select * 
from SA.CATEGORIES fetch first 5 rows only) "SA.CATEGORIES",        (       
 select * from SA.PRODUCTS fetch first 5 rows only) "SA.PRODUCTS",        ( 
       select * from SA.ORDER_DETAILS fetch first 5 rows only) 
"SA.ORDER_DETAILS",        (        select * from SA.SALES_EMPLOYEES fetch 
first 5 rows only) "SA.SALES_EMPLOYEES",        (        select * from 
SA.SUPPLIERS fetch first 5 rows only) "SA.SUPPLIERS",        (        
select * from SA.REGIONS fetch first 5 rows only) "Salesperson Region" 
where  "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID  and 
"SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID  and 
"SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID  and "SA.ORDERS".ORDER_ID 
= "SA.ORDER_DETAILS".ORDER_ID  and "SA.PRODUCTS".CATEGORY_ID = 
"SA.CATEGORIES".CATEGORY_ID  and "SA.ORDER_DETAILS".PRODUCT_ID = 
"SA.PRODUCTS".PRODUCT_ID  and "SA.ORDERS".EMPLOYEE_ID = 
"SA.SALES_EMPLOYEES".EMPLOYEE_ID  and "SA.PRODUCTS".SUPPLIER_ID = 
"SA.SUPPLIERS".SUPPLIER_ID  and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson 
Region".REGION_ID                                                               
                                                       

```



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (DERBY-7051) Multiple DISTINCT columns are not supported

2019-08-14 Thread JackLi0812 (JIRA) 


[ 
https://issues.apache.org/jira/browse/DERBY-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16907714#comment-16907714
 ] 

JackLi0812 commented on DERBY-7051:
---

{code:java}
SELECT DISTINCT
(SELECT COUNT(ORDERS.CUSTOMER_ID) FROM ORDERS),
(SELECT COUNT(DISTINCT ORDERS.CUSTOMER_ID) FROM ORDERS),
(SELECT COUNT(ORDERS.ORDER_ID) FROM ORDERS),
(SELECT COUNT(DISTINCT ORDERS.ORDER_ID) FROM ORDERS)
FROM ORDERS
;
{code}
It's working for me.

> Multiple DISTINCT columns are not supported
> ---
>
> Key: DERBY-7051
> URL: https://issues.apache.org/jira/browse/DERBY-7051
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
> Environment: derby-10.11.1.1;
>Reporter: JackLi0812
>Priority: Major
>
>  
> {code:java}
> SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
> FROM ORDERS
> ORDER BY ORDERS.ORDER_ID
> ;{code}
> throw error: 
> {code:java}
> SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, column 39.
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

[jira] [Commented] (DERBY-7051) Multiple DISTINCT columns are not supported

2019-08-13 Thread JackLi0812 (JIRA) 


[ 
https://issues.apache.org/jira/browse/DERBY-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16906883#comment-16906883
 ] 

JackLi0812 commented on DERBY-7051:
---

oh, no, i need a query sql.

> Multiple DISTINCT columns are not supported
> ---
>
> Key: DERBY-7051
> URL: https://issues.apache.org/jira/browse/DERBY-7051
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
> Environment: derby-10.11.1.1;
>Reporter: JackLi0812
>Priority: Major
>
>  
> {code:java}
> SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
> FROM ORDERS
> ORDER BY ORDERS.ORDER_ID
> ;{code}
> throw error: 
> {code:java}
> SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, column 39.
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

[jira] [Commented] (DERBY-7051) Multiple DISTINCT columns are not supported

2019-08-13 Thread JackLi0812 (JIRA) 


[ 
https://issues.apache.org/jira/browse/DERBY-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16906776#comment-16906776
 ] 

JackLi0812 commented on DERBY-7051:
---

Hi, Rick:

      Let's say I have a table with a purchase history that contains the users 
column and the items column, and I want to know the total number of users and 
the total number of items that I bought, so I need 
{code:java}
SELECT count(DISTINCT user) as userCount, count(DISTINCT product) as 
productCount
FROM tableName;{code}
Such a query.

> Multiple DISTINCT columns are not supported
> ---
>
> Key: DERBY-7051
> URL: https://issues.apache.org/jira/browse/DERBY-7051
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
> Environment: derby-10.11.1.1;
>Reporter: JackLi0812
>Priority: Major
>
>  
> {code:java}
> SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
> FROM ORDERS
> ORDER BY ORDERS.ORDER_ID
> ;{code}
> throw error: 
> {code:java}
> SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, column 39.
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

[jira] [Updated] (DERBY-7051) Multiple DISTINCT columns are not supported

2019-08-13 Thread JackLi0812 (JIRA) 


 [ 
https://issues.apache.org/jira/browse/DERBY-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

JackLi0812 updated DERBY-7051:
--
Description: 
 
{code:java}
SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
FROM ORDERS
ORDER BY ORDERS.ORDER_ID
;{code}
throw error: 
{code:java}
SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, column 39.
{code}

  was:
SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
FROM ORDERS
ORDER BY ORDERS.ORDER_ID
;

 

throw error: SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, 
column 39.


> Multiple DISTINCT columns are not supported
> ---
>
> Key: DERBY-7051
> URL: https://issues.apache.org/jira/browse/DERBY-7051
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
> Environment: derby-10.11.1.1;
>Reporter: JackLi0812
>Priority: Major
>
>  
> {code:java}
> SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
> FROM ORDERS
> ORDER BY ORDERS.ORDER_ID
> ;{code}
> throw error: 
> {code:java}
> SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, column 39.
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

[jira] [Created] (DERBY-7051) Multiple DISTINCT columns are not supported

2019-08-13 Thread JackLi0812 (JIRA) 
JackLi0812 created DERBY-7051:
-

 Summary: Multiple DISTINCT columns are not supported
 Key: DERBY-7051
 URL: https://issues.apache.org/jira/browse/DERBY-7051
 Project: Derby
  Issue Type: Bug
  Components: JDBC
 Environment: derby-10.11.1.1;
Reporter: JackLi0812


SELECT DISTINCT (ORDERS.CUSTOMER_ID), DISTINCT (ORDERS.ORDER_ID)
FROM ORDERS
ORDER BY ORDERS.ORDER_ID
;

 

throw error: SQL Error [42X01]: Syntax error: Encountered "DISTINCT" at line 1, 
column 39.



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)