I will try this – thanks. From: users-list mailto:users-list-bounces+lcj34=cornell.edu@monetdb.org> on behalf of Anthony Damico mailto:ajdamico@gmail.com> Reply-To: Communication channel for MonetDB users mailto:users-list@monetdb.org> Date: Monday, February 8, 2016 at 3:48 PM To: Communication channel for MonetDB users mailto:users-list@monetdb.org> Subject: Re: sql query failure question what happens if you use SELECT gt.chr instead of SELECT fg.chr ? what happens if you add more parentheses all over the place? for example (( fg.chr = gt.chr ) AND ( fg.pos >= gt.start_pos ) AND ( fg.pos <= gt.end_pos) ) instead of (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) On Mon, Feb 8, 2016 at 3:36 PM, Lynn Carol Johnson mailto:lcj34@cornell.edu> wrote: Hi all - I’m going nuts trying to determine why a monetdb SQL query does not work. I am trying to pull data from 2 tables. These tables are in the DB as shown here: sql>\d TABLE maizefullgenome.fullgenomegff TABLE maizefullgenome.fullgenomestable TABLE maizefullgenome.gene_from_gff TABLE maizefullgenome.ranges_from_gff sql> If I join only on the “pos” field, the query executes. Here is that query:

delChrPosGene_fullGenome <-dbGetQuery(conn,

"SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene_from_gff gt on (fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >=-1) OR (sift_score_g < 0.05 AND sift_score_g >=-1) OR (sift_score_t < 0.05 AND sift_score_t >=-1))") If I add the “chr” field, it fails with the message below. Can anyone tell me what is wrong with this second query?

delChrPosGene_fullGenome <-dbGetQuery(conn,

"SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene_from_gff gt on (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >=-1) OR (sift_score_g < 0.05 AND sift_score_g >=-1) OR (sift_score_t < 0.05 AND sift_score_t >=-1))") Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene...'. Server says '!Object not found'. Here are the fields in the gene_from_gff table: sql>\d maizefullgenome.gene_from_gff CREATE TABLE "maizefullgenome"."gene_from_gff" ( "chr" INTEGER, "start_pos" INTEGER, "end_pos" INTEGER, "gene_name" CHARACTER LARGE OBJECT ); sql> Here are the fields in the fullgenomestable table: sql>\d maizefullgenome.fullgenomestable CREATE TABLE "maizefullgenome"."fullgenomestable" ( "chr" INTEGER, "pos" INTEGER, "ref_allele" TINYINT, "two_bp_upstream" INTEGER, "two_bp_downstream" INTEGER, "three_bp_upstream" INTEGER, "three_bp_center" INTEGER, "three_bp_downstream" INTEGER, "four_bp_upstream" INTEGER, "four_bp_downstream" INTEGER, "five_bp_upstream" INTEGER, "five_bp_center" INTEGER, "five_bp_downstream" INTEGER, "gc_content" TINYINT, "gerp_neutral_tree_length" REAL, "gerp_score" REAL, "gerp_conserved" BOOLEAN, "cmethylation" REAL, "cpg_methylation_1bk_moving_average" REAL, "chg_methylation_1bk_moving_average" REAL, "chh_methylation_1bk_moving_average" REAL, "mnase_low_minus_high_rpm_shoots" REAL, "mnase_bayes_factor_shoots" REAL, "mnase_hotspot_shoots" BOOLEAN, "mnase_low_minus_high_rpm_roots" REAL, "mnase_bayes_factor_roots" REAL, "mnase_hotspot_roots" BOOLEAN, "allele_total_depth" INTEGER, "site_presence" INTEGER, "conservedgene_presence" BOOLEAN, "minor_allele" INTEGER, "maf" REAL, "combined_site_uniqueness_score" REAL, "site_uniqueness_score_sd" REAL, "expected_invariant_site" BOOLEAN, "codon_position" TINYINT, "within_cds" BOOLEAN, "within_gene" BOOLEAN, "within_transcript" BOOLEAN, "within_exon" BOOLEAN, "within_five_prime_utr" BOOLEAN, "within_three_prime_utr" BOOLEAN, "within_mirna" BOOLEAN, "line_family" INTEGER, "sine_family" INTEGER, "ltr_family" INTEGER, "helitron" BOOLEAN, "carot_common_support_interval" TINYINT, "toco_common_support_interval" TINYINT, "orig_amino_acid_a" TINYINT, "new_amino_acid_a" TINYINT, "amino_acid_substitute_pos_a" INTEGER, "sift_score_a" REAL, "sift_median_seq_info_a" REAL, "number_seqs_at_pos_a" INTEGER, "orig_amino_acid_c" TINYINT, "new_amino_acid_c" TINYINT, "amino_acid_substitute_pos_c" INTEGER, "sift_score_c" REAL, "sift_median_seq_info_c" REAL, "number_seqs_at_pos_c" INTEGER, "orig_amino_acid_g" TINYINT, "new_amino_acid_g" TINYINT, "amino_acid_substitute_pos_g" INTEGER, "sift_score_g" REAL, "sift_median_seq_info_g" REAL, "number_seqs_at_pos_g" INTEGER, "orig_amino_acid_t" TINYINT, "new_amino_acid_t" TINYINT, "amino_acid_substitute_pos_t" INTEGER, "sift_score_t" REAL, "sift_median_seq_info_t" REAL, "number_seqs_at_pos_t" INTEGER, "b73_uniqueness_score" REAL, "cml247_uniqueness_score" REAL, "ph207_uniqueness_score" REAL, "w22_uniqueness_score" REAL, "coelorachis_anc" TINYINT, "vossia_anc" TINYINT, "sorghum_anc" TINYINT, "oryza_anc" TINYINT, "setaria_anc" TINYINT, "brachypodium_anc" TINYINT, "hordeum_anc" TINYINT, "musa_anc" TINYINT, "populus_anc" TINYINT, "vitis_anc" TINYINT, "arabidopsis_anc" TINYINT, "panicum_anc" TINYINT ); sql> Any hints are greatly appreciated! Thanks - Lynn _______________________________________________ users-list mailing list users-list@monetdb.orgmailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

UPDATE: It looks like there was a problem with my gene_from_gff table. While I had no issue doing individual selects from it, it didn’t work in the query below. I have another table with the same (and additional) information and I switched to it for the join. The revised query below is now working when sent from R-Studio on a smaller dataset. Still having some problems with the 2Billion dataset through R, but I think it is an R issue with size. testChrPosGene_fullGenome <-dbGetQuery(conn,"SELECT fg.chr,fg.pos,rt.value FROM maizeFullGenome.fullGenomestable fg join maizeFullGenome.ranges_from_gff rt on (fg.chr = rt.chr AND (fg.pos >= rt.start_pos AND fg.pos <= rt.end_pos) AND rt.unit_type='Gene') where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >= -1) OR (sift_score_g < 0.05 AND sift_score_g >= -1) OR (sift_score_t < 0.05 AND sift_score_t >= -1))”) Thanks for your help and suggestions! Lynn On 2/8/16, 4:08 PM, "users-list on behalf of Lynn Carol Johnson" wrote:

Thanks, Stefan and Anthony:

I am running these queries through R-Studio. Other queries from R have not had a problem. The tables are big - here are the sizes ( yes, that is over 2 billion!):

sql>select count(*) from maizefullgenome.fullgenomestable; +------------+ | L1 | +============+ | 2059943587 | +------------+ 1 tuple (31.855ms) sql>select count(*) from maizefullgenome.gene_from_gff; +-------+ | L1 | +=======+ | 39149 | +-------+ 1 tuple (1.858ms) sql>

Could table size be part of the problem? I get the data I need if I DON¹T include the ³join², but I need that to get the gene name for each conserved position. Is there a more efficient way to ³join²?

I am currently running with gt.chr (instead of fg.chr) and with more parens as suggested. It takes this query some time to finish (or barf) - around 45 minutes, so I can¹t yet tell if these suggestions have worked.

If I can¹t get it working, I will see if I can create a smaller dataset.

On 2/8/16, 3:48 PM, "users-list on behalf of Stefan Manegold" wrote:

...

Hi,

appears to work fine on empty tables (see below).

We might need some data to reproduce the problem.

Best, Stefan

======== sql>SELECT fg.chr,fg.pos,gt.gene_name FROM fullgenomestable fg more>join gene_from_gff gt on (fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) more>where gerp_score> 2 AND gerp_neutral_tree_length > 2 more>AND ((sift_score_a < 0.05 AND sift_score_a >= -1) more>OR (sift_score_c < 0.05 AND sift_score_c >=-1) more>OR (sift_score_g < 0.05 AND sift_score_g >=-1) more>OR (sift_score_t < 0.05 AND sift_score_t >=-1)); +-----+-----+-----------+ | chr | pos | gene_name | +=====+=====+===========+ +-----+-----+-----------+ 0 tuples (5.759ms) sql>SELECT fg.chr,fg.pos,gt.gene_name FROM fullgenomestable fg more>join gene_from_gff gt on (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) more>where gerp_score> 2 AND gerp_neutral_tree_length > 2 more>AND ((sift_score_a < 0.05 AND sift_score_a >= -1) more>OR (sift_score_c < 0.05 AND sift_score_c >=-1) more>OR (sift_score_g < 0.05 AND sift_score_g >=-1) more>OR (sift_score_t < 0.05 AND sift_score_t >=-1)); +-----+-----+-----------+ | chr | pos | gene_name | +=====+=====+===========+ +-----+-----+-----------+ 0 tuples (13.054ms) ========

----- On Feb 8, 2016, at 9:36 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

Hi all -

I¹m going nuts trying to determine why a monetdb SQL query does not work. I am trying to pull data from 2 tables. These tables are in the DB as shown here:

sql>\d

TABLE maizefullgenome.fullgenomegff

TABLE maizefullgenome.fullgenomestable

TABLE maizefullgenome.gene_from_gff

TABLE maizefullgenome.ranges_from_gff

sql>

If I join only on the ³pos² field, the query executes. Here is that query:

...

delChrPosGene_fullGenome <-dbGetQuery(conn,

"SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg

join maizeFullGenome.gene_from_gff gt on (fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos)

where gerp_score> 2 AND gerp_neutral_tree_length > 2

AND ((sift_score_a < 0.05 AND sift_score_a >= -1)

OR (sift_score_c < 0.05 AND sift_score_c >=-1)

OR (sift_score_g < 0.05 AND sift_score_g >=-1)

OR (sift_score_t < 0.05 AND sift_score_t >=-1))")

If I add the ³chr² field, it fails with the message below. Can anyone tell me what is wrong with this second query?

...

delChrPosGene_fullGenome <-dbGetQuery(conn,

"SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg

join maizeFullGenome.gene_from_gff gt on (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos)

where gerp_score> 2 AND gerp_neutral_tree_length > 2

AND ( (sift_score_a < 0.05 AND sift_score_a >= -1)

OR (sift_score_c < 0.05 AND sift_score_c >=-1)

OR (sift_score_g < 0.05 AND sift_score_g >=-1)

OR (sift_score_t < 0.05 AND sift_score_t >=-1) ) ")

Error in .local(conn, statement, ...) :

Unable to execute statement 'SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene...'.

Server says '!Object not found'.

Here are the fields in the gene_from_gff table:

sql>\d maizefullgenome.gene_from_gff

CREATE TABLE "maizefullgenome"."gene_from_gff" (

"chr" INTEGER,

"start_pos" INTEGER,

"end_pos" INTEGER,

"gene_name" CHARACTER LARGE OBJECT

);

sql> Here are the fields in the fullgenomestable table:

sql>\d maizefullgenome.fullgenomestable

CREATE TABLE "maizefullgenome"."fullgenomestable" (

"chr" INTEGER,

"pos" INTEGER,

"ref_allele" TINYINT,

"two_bp_upstream" INTEGER,

"two_bp_downstream" INTEGER,

"three_bp_upstream" INTEGER,

"three_bp_center" INTEGER,

"three_bp_downstream" INTEGER,

"four_bp_upstream" INTEGER,

"four_bp_downstream" INTEGER,

"five_bp_upstream" INTEGER,

"five_bp_center" INTEGER,

"five_bp_downstream" INTEGER,

"gc_content" TINYINT,

"gerp_neutral_tree_length" REAL,

"gerp_score" REAL,

"gerp_conserved" BOOLEAN,

"cmethylation" REAL,

"cpg_methylation_1bk_moving_average" REAL,

"chg_methylation_1bk_moving_average" REAL,

"chh_methylation_1bk_moving_average" REAL,

"mnase_low_minus_high_rpm_shoots" REAL,

"mnase_bayes_factor_shoots" REAL,

"mnase_hotspot_shoots" BOOLEAN,

"mnase_low_minus_high_rpm_roots" REAL,

"mnase_bayes_factor_roots" REAL,

"mnase_hotspot_roots" BOOLEAN,

"allele_total_depth" INTEGER,

"site_presence" INTEGER,

"conservedgene_presence" BOOLEAN,

"minor_allele" INTEGER,

"maf" REAL,

"combined_site_uniqueness_score" REAL,

"site_uniqueness_score_sd" REAL,

"expected_invariant_site" BOOLEAN,

"codon_position" TINYINT,

"within_cds" BOOLEAN,

"within_gene" BOOLEAN,

"within_transcript" BOOLEAN,

"within_exon" BOOLEAN,

"within_five_prime_utr" BOOLEAN,

"within_three_prime_utr" BOOLEAN,

"within_mirna" BOOLEAN,

"line_family" INTEGER,

"sine_family" INTEGER,

"ltr_family" INTEGER,

"helitron" BOOLEAN,

"carot_common_support_interval" TINYINT,

"toco_common_support_interval" TINYINT,

"orig_amino_acid_a" TINYINT,

"new_amino_acid_a" TINYINT,

"amino_acid_substitute_pos_a" INTEGER,

"sift_score_a" REAL,

"sift_median_seq_info_a" REAL,

"number_seqs_at_pos_a" INTEGER,

"orig_amino_acid_c" TINYINT,

"new_amino_acid_c" TINYINT,

"amino_acid_substitute_pos_c" INTEGER,

"sift_score_c" REAL,

"sift_median_seq_info_c" REAL,

"number_seqs_at_pos_c" INTEGER,

"orig_amino_acid_g" TINYINT,

"new_amino_acid_g" TINYINT,

"amino_acid_substitute_pos_g" INTEGER,

"sift_score_g" REAL,

"sift_median_seq_info_g" REAL,

"number_seqs_at_pos_g" INTEGER,

"orig_amino_acid_t" TINYINT,

"new_amino_acid_t" TINYINT,

"amino_acid_substitute_pos_t" INTEGER,

"sift_score_t" REAL,

"sift_median_seq_info_t" REAL,

"number_seqs_at_pos_t" INTEGER,

"b73_uniqueness_score" REAL,

"cml247_uniqueness_score" REAL,

"ph207_uniqueness_score" REAL,

"w22_uniqueness_score" REAL,

"coelorachis_anc" TINYINT,

"vossia_anc" TINYINT,

"sorghum_anc" TINYINT,

"oryza_anc" TINYINT,

"setaria_anc" TINYINT,

"brachypodium_anc" TINYINT,

"hordeum_anc" TINYINT,

"musa_anc" TINYINT,

"populus_anc" TINYINT,

"vitis_anc" TINYINT,

"arabidopsis_anc" TINYINT,

"panicum_anc" TINYINT

);

sql>

Any hints are greatly appreciated!

Thanks - Lynn

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list