IRC was unusually busy this morning. There was no talk about code but
there was some nice feedback from users on
on the protocol fixes contributed by Bryan (fixed X-gGen's problem on
the spot) and the optimizer work that Army has contributed. Also some
interesting chat about paid jobs and open source and great news that we
have a patch for DERBY-1374.
Kathey
<cloder> morning
<kmarsden> hello
-->| X-Gen ([EMAIL PROTECTED]) has joined
#derby
<X-Gen> hey freaks
<X-Gen> this the apache derby place ?
* X-Gen thinks kmarsden = Kathey Marsden maybe. so this must be
the correct place
<X-Gen> Whoot
* X-Gen shakes the # about
<cloder> er
<cloder> do you folks use outer joins much in derby?
<cloder> the optimizer is doing nasty things
<cloder> foo left outer join a.z ON a.x = b.x
<kmarsden> can you describe nasty?
<cloder> um, hm
<cloder> ok, doing a repeated table scan of a huge table
<cloder> to the point where i had to rewrite the left outer join
as a UNION of two selects (one of which uses a NOT EXISTS subquery)
<cloder> in other words, rewriting the left outer join as
UNION(inner-joined-rows, missing-rows-with-null-constant)
<X-Gen> org.apache.derby.impl.drda.DRDAProtocolException is nasty
right ? how do i go about getting more informatio as to why my query is
failing ?
<cloder> i have the output of the query analyzer
<cloder> trying to think of ways to rewrite this
<kmarsden> Yes. org.apache.derby.impl.drda.DRDAProtocolException
is nasty and usually has nothing to do with the query being executed.
<cloder> other than what i've done, which is quite ugly (even
though it performs about 1000x faster)
<kmarsden> You are saying that your query gives a protocol
exception and you rewrite it and it does not?
<cloder> me?
<X-Gen> kmarsden, yup
<cloder> two separate people i think
<kmarsden> oops
<X-Gen> kmarsden, if i have one order by col. the query works, i
add another col to the order by statement and boom. im using 10.1.1
going to update quick and see if its fixed
<kmarsden> Oh well trying to do this while cooking my son's
breakfast. Anyway, for the query issue try with the10.1.2.4 snapshot
with DERBY-805/649 fixes.
<cloder> i will give it a shot
<kmarsden> yes. both update to the snashot and see. There have
been even more protocol and optimizer fixes since too that will make it
into 10.1.3
<cloder> i think maybe i will experiment with rewriting this as a
right outer join
<cloder> seems the choice of join order is basically fixed
<kmarsden> X-Gen, I was bothered by your initial greeting. Please
be nice.
<X-Gen> kmarsden, sorry to have upset u so early in the AM. (hey
freaks is my usual greeting when joining a channel. but ill refrain from
this in #derby)
<kmarsden> X-Gen: Thanks!
<X-Gen> kmarsden, do you have a link handy where i can get the
snapshot from without builing it myself ?
<cloder> x-gen: google for 'derby download'
<X-Gen> aah thanks
<cloder> i'm impresed with army, whoever s/he is
* X-Gen gets the same error on 10.1.2.1, going to download
10.1.2.4 snapshot quick
<daghw> Kathey, I think Fernanda has uploaded a new version of
DERBY-1374!
<cloder> from my reading of the predicate pushdown patch notes,
this looks promising
<cloder> hmm. any way to force soft upgrade from production to alpha?
<daghw> yes, but it is only intended for developers, since it wont
upgrade to the next snapshot. It is intended for developers to test
upgrade paths,,, but if you are sure you can drop the db later you coudl
try it out: Set this property:derby.database.allow PreReleaseUpgrade
<cloder> ok
<cloder> this is a fairly large, problematic database of which i
have many copies
<cloder> our schema is over-normalized, to be frank
<cloder> SELECT d.dev_id, d.dev_addr, na.addr_ipv4, nn.name,
d.dev_tag, s.site_id, s.riskfactor, d.riskfactor, ds.os_id, ds.hw_id,
ds.riskscore, ds.vcnt_crit, ds.vcnt_sev, ds.vcnt_mod, sc.end_time FROM
sites s, site_devices d, device_synopsis ds, scans sc, node_addr_ip4 na,
nodes n LEFT OUTER JOIN node_name nn ON n.node_id = nn.node_id WHERE
s.status IS NULL AND s.site_id = 1 AND s.site_id = d.site_id AND
d.dev_id = ds.dev_id AND ds.node_id = n.node_id AND
<cloder> n.scan_id = sc.scan_id AND na.node_id = n.node_id ORDER
BY 15 DESC, 11 DESC, 3 ASC, 1 ASC
<cloder> you get the idea
<cloder> the nodes (n) table has about 50,000 rows, the node_name
table (nn) has about 75,000
<cloder> and it's doing: hash-left-outer-join(table-scan(nodes),
hash-scan(node_name))
<X-Gen> Whooha it werks it werks. Thanks kmarsden ;)
-->| FernandaTP ([EMAIL PROTECTED]) has joined #derby
<daghw> cloder: Did you put this question to the derby-user or
derby-developer list? I think you will have a better change of a good
answer if you do, coz none of the compiler experts are here right now.
<kmarsden> kmarsden: Thanks goes to Bryan not me.
<cloder> daghw: no, i'll do that
<cloder> who are the compiler experts, if you know
<cloder> i'd sooner hire one of them than ask the list :)
<daghw> haha! we need'em!
<cloder> oh, i'd hire them to work on optimizing our use of derby
and of course, optimizing derby itself
<cloder> so the benefits would still be committed to derby
<daghw> i think they already have an employer :)
<cloder> yes, i get the impression that many of the experts would
rather not say who their employers are, either :)
-->| pmcmahan ([EMAIL PROTECTED]) has joined #derby
<pmcmahan> I have versions of an sql script for db2, firebird,
hypersonic, interbase, jdatastore, mysql, oracle, postgres, sap, and sql
server
<pmcmahan> Can someone advise which version might be the best
starting point for a derby version of the script?
<cloder> db2 is somewhat similar
<cloder> at least in my experience
<pmcmahan> thanks cloder
<cloder> oracle is vastly different
<cloder> of course :)
<pmcmahan> yeah that's my experience too :-)
<kmarsden> cloder: I am curious why you think that about folks
hiding their employers.
<cloder> kmarsden: i'm involved in other open source projects
where that is the case
<cloder> it is very common to have people who use something at
work, and spend employer time hacking on it and improving it, but not
wanting their employer to know
<kmarsden> I use a non-IBM email address. I hope it is not
interpreted that want to hide that I work for IBM as I stated that
clearly with my initial mail to derby-dev. I do it because The Apache
Way is for us all to act as individual volunteers, expressing our own
opinions and calls for diversity. I think using my own email promotes
equality and community and makes it clear that my opinions are my own
regardless of who pays my paycheck.
<cloder> yeah, but obviously you can only speak for yourself
<cloder> btw, what do you do at ibm if you don't mind me asking
<cloder> i used to work there :)
<cloder> well, for lotus
<kmarsden> I work on Derby
<cloder> oh, nice :)
<daghw> kathey, did you see my reply?
|<-- daghw has left freenode (Remote closed the connection)
<--| pmcmahan has left #derby
-->| daghw ([EMAIL PROTECTED]) has joined #derby
<kmarsden> dagw: Yes you said there is a patch to fix 1374. That
is good. Not good to have those compat tests broken for long. Off to
help with homework.
<kmarsden> cloder: Actually what I do for IBM is I work in
advanced support. I help users like you and X-Gen get past your problems
and try to make sure they are fixed before you hit them (or at least we
have a fix waiting). It is great to get early feedback from users like
you as we put snapshots out to get feedback on regressions and potential
compatibility issues.
|
