RE: group by select queries
Jira created: CASSANDRA-14209. From: kurt greaves [mailto:k...@instaclustr.com] Sent: Thursday, February 01, 2018 12:38 AM To: User Subject: Re: group by select queries Seems problematic. Would you be able to create a JIRA ticket with the above information/examples? On 30 January 2018 at 22:41, Modha, Digant <digant.mo...@tdsecurities.com<mailto:digant.mo...@tdsecurities.com>> wrote: It was local quorum. There’s no difference with CONSISTENCY ALL. Consistency level set to LOCAL_QUORUM. cassandra@cqlsh> select * from wp.position where account_id = 'user_1'; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +-+-++--+--++- user_1 |AMZN | 2 | 1239.2 |0 | 1011 | null | 2018-01-25 17:18:07.158000+ user_1 |AMZN | 1 | 1239.2 |0 | 1010 | null | 2018-01-25 17:18:07.158000+ (2 rows) cassandra@cqlsh> select * from wp.position where account_id = 'user_1' group by security_id; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +-+-++--+--++- user_1 |AMZN | 1 | 1239.2 |0 | 1010 | null | 2018-01-25 17:18:07.158000+ (1 rows) cassandra@cqlsh> select account_id,security_id, counter, avg_exec_price,quantity, update_time from wp.position where account_id = 'user_1' group by security_id ; account_id | security_id | counter | avg_exec_price | quantity | update_time +-+-++--+- user_1 |AMZN | 2 | 1239.2 | 1011 | 2018-01-25 17:18:07.158000+ (1 rows) cassandra@cqlsh> consistency all; Consistency level set to ALL. cassandra@cqlsh> select * from wp.position where account_id = 'user_1' group by security_id; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +-+-++--+--++- user_1 |AMZN | 1 | 1239.2 |0 | 1010 | null | 2018-01-25 17:18:07.158000+ (1 rows) cassandra@cqlsh> select account_id,security_id, counter, avg_exec_price,quantity, update_time from wp.position where account_id = 'user_1' group by security_id ; account_id | security_id | counter | avg_exec_price | quantity | update_time +-+-++--+- user_1 |AMZN | 2 | 1239.2 | 1011 | 2018-01-25 17:18:07.158000+ From: kurt greaves [mailto:k...@instaclustr.com<mailto:k...@instaclustr.com>] Sent: Monday, January 29, 2018 11:03 PM To: User Subject: Re: group by select queries What consistency were you querying at? Can you retry with CONSISTENCY ALL? TD Securities disclaims any liability or losses either direct or consequential caused by the use of this information. This communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. TD Securities is neither making any investment recommendation nor providing any professional or advisory services relating to the activities described herein. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice Any products described herein are (i) not insured by the FDIC, (ii) not a deposit or other obligation of, or guaranteed by, an insured depository institution and (iii) subject to investment risks, including possible loss of the principal amount invested. The information shall not be further distributed or duplicated in whole or in part by any means without the prior written consent of TD Securities. TD Securities is a trademark of The Toronto-Dominion Bank and represents TD Securities (USA) LLC and certain investment banking activities of The Toronto-Dominion Bank and its subsidiaries. TD Securities disclaims any liability or losses either direct or consequential caused by the use of this information. This communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. TD Securities is neither making any investment recommendation nor providing any professional or advisory services r
Re: group by select queries
Worth digging into the source code of GROUP BY but as far as I remember, using GROUP BY without any aggregation function will lead to C* picking just the first row (or maybe last, not sure on this point) row at hand. About ordering, since the grouping is on a component of partition key, do not expect any sensible order since only token order matters On Thu, Feb 1, 2018 at 6:38 AM, kurt greaves <k...@instaclustr.com> wrote: > Seems problematic. Would you be able to create a JIRA ticket with the > above information/examples? > > On 30 January 2018 at 22:41, Modha, Digant <digant.mo...@tdsecurities.com> > wrote: > >> It was local quorum. There’s no difference with CONSISTENCY ALL. >> >> >> >> Consistency level set to LOCAL_QUORUM. >> >> cassandra@cqlsh> select * from wp.position where account_id = 'user_1'; >> >> >> >> account_id | security_id | counter | avg_exec_price | pending_quantity | >> quantity | transaction_id | update_time >> >> +-+-++-- >> +--++--- >> -- >> >> user_1 |AMZN | 2 | 1239.2 |0 >> | 1011 | null | 2018-01-25 17:18:07.158000+ >> >> user_1 |AMZN | 1 | 1239.2 |0 >> | 1010 | null | 2018-01-25 17:18:07.158000+ >> >> >> >> (2 rows) >> >> cassandra@cqlsh> select * from wp.position where account_id = 'user_1' >> group by security_id; >> >> >> >> account_id | security_id | counter | avg_exec_price | pending_quantity | >> quantity | transaction_id | update_time >> >> +-+-++-- >> +--++--- >> -- >> >> user_1 |AMZN | 1 | 1239.2 |0 >> | 1010 | null | 2018-01-25 17:18:07.158000+ >> >> >> >> (1 rows) >> >> cassandra@cqlsh> select account_id,security_id, counter, >> avg_exec_price,quantity, update_time from wp.position where account_id = >> 'user_1' group by security_id ; >> >> >> >> account_id | security_id | counter | avg_exec_price | quantity | >> update_time >> >> +-+-++-- >> +- >> >> user_1 |AMZN | 2 | 1239.2 | 1011 | >> 2018-01-25 17:18:07.158000+ >> >> >> >> (1 rows) >> >> cassandra@cqlsh> consistency all; >> >> Consistency level set to ALL. >> >> cassandra@cqlsh> select * from wp.position where account_id = 'user_1' >> group by security_id; >> >> >> >> account_id | security_id | counter | avg_exec_price | pending_quantity | >> quantity | transaction_id | update_time >> >> +-+-++-- >> +--++--- >> -- >> >> user_1 |AMZN | 1 | 1239.2 |0 >> | 1010 | null | 2018-01-25 17:18:07.158000+ >> >> >> >> (1 rows) >> >> cassandra@cqlsh> select account_id,security_id, counter, >> avg_exec_price,quantity, update_time from wp.position where account_id = >> 'user_1' group by security_id ; >> >> >> >> account_id | security_id | counter | avg_exec_price | quantity | >> update_time >> >> +-+-++-- >> +- >> >> user_1 |AMZN | 2 | 1239.2 | 1011 | >> 2018-01-25 17:18:07.158000+ >> >> >> >> >> >> *From:* kurt greaves [mailto:k...@instaclustr.com] >> *Sent:* Monday, January 29, 2018 11:03 PM >> *To:* User >> *Subject:* Re: group by select queries >> >> >> >> What consistency were you querying at? Can you retry with CONSISTENCY ALL? >> >> >> >> >> >> >> TD Securities disclaims any liability or losses either direct or >> consequential caused by the use of this information. This communication is >> for informational purposes only and is not intended as an offer or >> solicitation for the purchase or sale of any financial instrument or as an >> official confirmation of any transaction. TD Securities is n
Re: group by select queries
Seems problematic. Would you be able to create a JIRA ticket with the above information/examples? On 30 January 2018 at 22:41, Modha, Digant <digant.mo...@tdsecurities.com> wrote: > It was local quorum. There’s no difference with CONSISTENCY ALL. > > > > Consistency level set to LOCAL_QUORUM. > > cassandra@cqlsh> select * from wp.position where account_id = 'user_1'; > > > > account_id | security_id | counter | avg_exec_price | pending_quantity | > quantity | transaction_id | update_time > > +-+-++-- > +--++- > > user_1 |AMZN | 2 | 1239.2 |0 > | 1011 | null | 2018-01-25 17:18:07.158000+ > > user_1 |AMZN | 1 | 1239.2 |0 > | 1010 | null | 2018-01-25 17:18:07.158000+ > > > > (2 rows) > > cassandra@cqlsh> select * from wp.position where account_id = 'user_1' > group by security_id; > > > > account_id | security_id | counter | avg_exec_price | pending_quantity | > quantity | transaction_id | update_time > > +-+-++-- > +--++- > > user_1 |AMZN | 1 | 1239.2 |0 > | 1010 | null | 2018-01-25 17:18:07.158000+ > > > > (1 rows) > > cassandra@cqlsh> select account_id,security_id, counter, > avg_exec_price,quantity, update_time from wp.position where account_id = > 'user_1' group by security_id ; > > > > account_id | security_id | counter | avg_exec_price | quantity | > update_time > > +-+-++-- > +- > > user_1 |AMZN | 2 | 1239.2 | 1011 | > 2018-01-25 17:18:07.158000+ > > > > (1 rows) > > cassandra@cqlsh> consistency all; > > Consistency level set to ALL. > > cassandra@cqlsh> select * from wp.position where account_id = 'user_1' > group by security_id; > > > > account_id | security_id | counter | avg_exec_price | pending_quantity | > quantity | transaction_id | update_time > > +-+-++-- > +--++- > > user_1 |AMZN | 1 | 1239.2 |0 > | 1010 | null | 2018-01-25 17:18:07.158000+ > > > > (1 rows) > > cassandra@cqlsh> select account_id,security_id, counter, > avg_exec_price,quantity, update_time from wp.position where account_id = > 'user_1' group by security_id ; > > > > account_id | security_id | counter | avg_exec_price | quantity | > update_time > > +-+-----+--------+------ > +- > > user_1 |AMZN | 2 | 1239.2 | 1011 | > 2018-01-25 17:18:07.158000+ > > > > > > *From:* kurt greaves [mailto:k...@instaclustr.com] > *Sent:* Monday, January 29, 2018 11:03 PM > *To:* User > *Subject:* Re: group by select queries > > > > What consistency were you querying at? Can you retry with CONSISTENCY ALL? > > > > > > > TD Securities disclaims any liability or losses either direct or > consequential caused by the use of this information. This communication is > for informational purposes only and is not intended as an offer or > solicitation for the purchase or sale of any financial instrument or as an > official confirmation of any transaction. TD Securities is neither making > any investment recommendation nor providing any professional or advisory > services relating to the activities described herein. All market prices, > data and other information are not warranted as to completeness or accuracy > and are subject to change without notice Any products described herein are > (i) not insured by the FDIC, (ii) not a deposit or other obligation of, or > guaranteed by, an insured depository institution and (iii) subject to > investment risks, including possible loss of the principal amount invested. > The information shall not be further distributed or duplicated in whole or > in part by any means without the prior written consent of TD Securities. TD > Securities is a trademark of The Toronto-Dominion Bank and represents TD > Securities (USA) LLC and certain investment banking activities of The > Toronto-Dominion Bank and its subsidiaries. >
RE: group by select queries
It was local quorum. There’s no difference with CONSISTENCY ALL. Consistency level set to LOCAL_QUORUM. cassandra@cqlsh> select * from wp.position where account_id = 'user_1'; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +-+-++--+--++- user_1 |AMZN | 2 | 1239.2 |0 | 1011 | null | 2018-01-25 17:18:07.158000+ user_1 |AMZN | 1 | 1239.2 |0 | 1010 | null | 2018-01-25 17:18:07.158000+ (2 rows) cassandra@cqlsh> select * from wp.position where account_id = 'user_1' group by security_id; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +-+-++--+--++- user_1 |AMZN | 1 | 1239.2 |0 | 1010 | null | 2018-01-25 17:18:07.158000+ (1 rows) cassandra@cqlsh> select account_id,security_id, counter, avg_exec_price,quantity, update_time from wp.position where account_id = 'user_1' group by security_id ; account_id | security_id | counter | avg_exec_price | quantity | update_time +-+-++--+- user_1 |AMZN | 2 | 1239.2 | 1011 | 2018-01-25 17:18:07.158000+ (1 rows) cassandra@cqlsh> consistency all; Consistency level set to ALL. cassandra@cqlsh> select * from wp.position where account_id = 'user_1' group by security_id; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +-+-++--+--++- user_1 |AMZN | 1 | 1239.2 |0 | 1010 | null | 2018-01-25 17:18:07.158000+ (1 rows) cassandra@cqlsh> select account_id,security_id, counter, avg_exec_price,quantity, update_time from wp.position where account_id = 'user_1' group by security_id ; account_id | security_id | counter | avg_exec_price | quantity | update_time +-+-++--+- user_1 |AMZN | 2 | 1239.2 | 1011 | 2018-01-25 17:18:07.158000+ From: kurt greaves [mailto:k...@instaclustr.com] Sent: Monday, January 29, 2018 11:03 PM To: User Subject: Re: group by select queries What consistency were you querying at? Can you retry with CONSISTENCY ALL? TD Securities disclaims any liability or losses either direct or consequential caused by the use of this information. This communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. TD Securities is neither making any investment recommendation nor providing any professional or advisory services relating to the activities described herein. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice Any products described herein are (i) not insured by the FDIC, (ii) not a deposit or other obligation of, or guaranteed by, an insured depository institution and (iii) subject to investment risks, including possible loss of the principal amount invested. The information shall not be further distributed or duplicated in whole or in part by any means without the prior written consent of TD Securities. TD Securities is a trademark of The Toronto-Dominion Bank and represents TD Securities (USA) LLC and certain investment banking activities of The Toronto-Dominion Bank and its subsidiaries.
Re: group by select queries
What consistency were you querying at? Can you retry with CONSISTENCY ALL?
group by select queries
HI, I get two different out with these 2 queries. The only difference between the 2 queries is that one does 'select *' and other does 'select specific fields' without any aggregate functions. Does group by pick any record? It seems to pick either the first or the last record. I expected the result #2 not #1 because that's the way data is ordered. I am using Apache Cassandra 3.10. #1 cassandra@cqlsh> select * from wp.position where account_id = 'user_1' group by security_id; account_id | security_id | counter | avg_exec_price | pending_quantity | quantity | transaction_id | update_time +---+-++--+--++- user_1 | MSFT | 0 | 91 |0 | 1000 | null | 2018-01-25 17:17:47.158000+ user_1 | T | 0 | 39.3 |0 | 1000 | null | 2018-01-25 17:17:47.158000+ user_1 | cash_position | 0 | 1234 |0 | 1234 | null | 2018-01-25 16:45:51.18+ (3 rows) #2 cassandra@cqlsh> select account_id,security_id, counter, avg_exec_price,quantity, update_time from wp.position where account_id = 'user_1' group by security_id; account_id | security_id | counter | avg_exec_price | quantity | update_time +---+-++--+- user_1 | MSFT | 0 | 91 | 1000 | 2018-01-25 17:17:47.158000+ user_1 | T | 1 | 39.2 | 1010 | 2018-01-25 17:18:07.158000+ user_1 | cash_position | 70 | 1 |1 | 2018-01-25 17:17:47.158000+ (3 rows) Table Description: CREATE TABLE wp.position ( account_id text, security_id text, counter bigint, avg_exec_price double, pending_quantity double, quantity double, transaction_id uuid, update_time timestamp, PRIMARY KEY (account_id, security_id, counter) ) WITH CLUSTERING ORDER BY (security_id ASC, counter DESC) --Digant TD Securities disclaims any liability or losses either direct or consequential caused by the use of this information. This communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. TD Securities is neither making any investment recommendation nor providing any professional or advisory services relating to the activities described herein. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice Any products described herein are (i) not insured by the FDIC, (ii) not a deposit or other obligation of, or guaranteed by, an insured depository institution and (iii) subject to investment risks, including possible loss of the principal amount invested. The information shall not be further distributed or duplicated in whole or in part by any means without the prior written consent of TD Securities. TD Securities is a trademark of The Toronto-Dominion Bank and represents TD Securities (USA) LLC and certain investment banking activities of The Toronto-Dominion Bank and its subsidiaries.
