An Alternative to SQL?

Golygydd Max writes "Dave Voorhis from the University of Derbyshire has developed a program incorporating Tutorial D, a language designed to overcome of the shortcomings of SQL, and developed some years ago by Hugh Darwen and Chris Date. Until now, no-one had done anything with it but Voorhis is hoping for wider adoption; although we think it would be like pushing water uphill though." Update: 10/13 12:43 GMT by T : An anonymous reader writes "It's being picky I know, but the university in question is in fact called The University Of Derby, not Derbyshire."

Comments

Who remembers Knowledgeman?

[Pig Hogger]

Who remembers "Knowledgeman", that database language of 20 years ago which got eclipsed by dBase???

Re:Who remembers Knowledgeman?

[Anonymous Coward]

Whoa! That's taking me back! I spent a couple of years during my undergrad ('83-'87) using KMan... it seemed far superior to dBase. No one ever wrote a compiler for it (ala Clipper or QuickSilver for dBase) so it pretty much died a slow death. Too bad.

Re:Who remembers Knowledgeman?

[joebok]

Good old K-Man! A fellow programmer once said to me, "K-Man is like a retarded daughter - she can't do much, but you love her anyway."

Re:Who remembers Knowledgeman?

[jmelchio]

I'm surprised nobody has brought up Progress yet. It's a database with 4GL and its own query language. I always found it a lot easier to work in than SQL although as anything else, it's not perfect. Because of the productivity of the language it has a steady following and there are quite a few people who sell applications (financial, manufacturing) written in Progress.

Re:Who remembers Knowledgeman?

[TrentL]

A fellow programmer once said to me, "K-Man is like a retarded daughter - she can't do much, but you love her anyway."

And that same programmer probably stays up at night wondering why he's still a virgin.

Re:Who remembers Knowledgeman?

[Anonymous Coward]

Was this retarded daughter, then, an immaculate, or neighborly conception?

Re:Who remembers Knowledgeman?

[stephanruby]

"And that same programmer probably stays up at night wondering why he's still a virgin."

I take it logic puzzles were never your strong point.

Re:Who remembers Knowledgeman?

[stephanruby]

but don't worry, here at Slashdot we'll love you anyway.

Re:Who remembers Knowledgeman?

[Anonymous Coward]

With "Trent" in there, I'm not sure about the "daughter" part, but like you said, anyway.

Re:Who remembers Knowledgeman?

[FictionPimp]

Wow, your the first person I've ever found that likes progress. Wow.

Re:Who remembers Knowledgeman?

[orasio]

"And that same programmer probably stays up at night wondering why he's still a virgin."

I take it logic puzzles were never your strong point.


Come to think of it, if I were a virgin with a daughter, and she weren't adopted, I would have things to wonder other than the cause of my virginity!

Re:Who remembers Knowledgeman?

[cayenne8]

What is so hard about SQL? I find it very easy to use...complex queries are complex, but, not difficult if you've got a properly designed and normalized database. Not sure why some many dislike SQL.

shortcomings to sql?

[Suppafly]

What are the shortcomings to sql? it seems to be able to handle anything you'd need it to do.

Re:shortcomings to sql?

[gl4ss]

well.. assembly "seems to handle anything you'd need it to do" so why the need for higher level programming languages?

probably something along those lines, that it would be easier to do some things and easier to avoid problems.

Re:shortcomings to sql?

[lottameez]

SQL sucks. If you need to do anything much beyond a simple query you find yourself buried in the details of that particular db's implementation. (think date handling for example). I don't want, or feel I should need, to be a DBA to get some data out of a data store.

Joins are a big pain in the butt, and the case statements get so convoluted I feel like I'm writing LISP.

Re:shortcomings to sql?

[lub]

RTFA:
-"you ask a database for "all entries where field X is not equal to 47" it won't return any of those where field X is null because instead of saying "Null doesn't equal 47", the value "null" is deemed not to be comparable with any non-null field."
-"rather arbitrary habit of allowing nested queries in some places but not others, for instance"

Re:shortcomings to sql?

[pizza_milkshake]

postgresql, for one, offers array datatypes

Re:shortcomings to sql?

[Unordained]

Sort of. Some SQL dialects allow for arrays as a datatype, though often without much in the way of good operators. Relational theory allows an attribute (column) to have any domain (type), including complex types like containers (sets, arrays, maps, etc.) ... in theory, yes, you should be able to do this. SQL, however, is another story.

Re:shortcomings to sql?

[l810c]

One thing I always wondered was if there was a way to make a table that had a different number of columns for each row. For instance, if you wanted to insert ids from another table, but the number of ids each row would need is a variable ranging from zero to god knows what... Can SQL do that in a way that doesn't involve making a huge table with tons of columns or one big structure holding the ids separated by colons?

Yuck, you would never Want to do that. That's what relationships and foreign keys are for. You wouldn't populate your id's across columns, you put them in a new table, one row each, with an foreign key linking them back to main table.

Re:shortcomings to sql?

[Fred IV]

You only need to learn about null comparisons once, and nulls are extremely valuable when you get into eliminating rows from a result sets based on matches from data in other tables.

Combining a left outer join with a search condition where a primary key is null from the joined table is a quick and dirty way to scrub records where there's a match in the joined table, and would be impossible without the concept of nulls.

Re:shortcomings to sql?

[Gilk180]

If you think joins suck (I don't personally agree, but that's all just opinion), you should be using some other database model. After "data is stored in a table", joins are THE feature of relational databases.

Maybe try an object-oriented database (I hear ObjectStore is good), or an associative database (BerkeleyDB), or an XML database (Sleepycat has one).

Relational databases aren't the only game in town, they are just the most popular and therefore:

• have had the most development time thrown at the DBMSs
  
• have the most developers who are proficient.
  

Re:shortcomings to sql?

[lucabrasi999]

What are the shortcomings to sql? it seems to be able to handle anything you'd need it to do.

Agreed. But, then again, the only command I EVER need is TRUNCATE. All other SQL commands are useless.

Re:shortcomings to sql?

[Anonymous Coward]

Joins are a big pain in the butt, and the case statements get so convoluted I feel like I'm writing LISP.

Pussy.

Go back to building queries in Access.

Re:shortcomings to sql?

[Gilk180]

Sounds like a one to many relationship to me. Or possible a many to many, it just changes what the keys are.

Each dynamic column becomes a row in a second table.

table a
id name syllables
1 foobar foo, bar

translates to:

table a
id name
0 foobar

table syllables
a_id syllable
0 foo
0 bar

If you want ordering, you can add another column to syllables to represent order.

Re:shortcomings to sql?

[Deorus]

Usually when you need to write queries for big databases, speed is a concern, so the lower the level, the better. I've never seen a GUI which could write SQL queries as well as I do.

Additionally, a high level interface is unable to undestand where a query can or cannot be optimized, but I can. For example: there are cases where queries have to be run on a regular schedule to update special optimization tables. These optimization tables are then used when user generated query (e.g.: from web input) comes, so that the user doesn't have to wait for the database to complete that subquery which could have ben run sooner. Only low level can give you such a control.

Small databases can well use high level interfaces, but those aren't the ones driving the standards anyway since the work is already easy for them. User-friendly interfaces such as Access, Query Builder, and crap like that already provide the required high level for the non-techies.

If anything comes to replace SQL, I think it won't stand standard long enough as vendors will start adding more and more odd extensions, so the story will probably repeat. Personally I don't care much about the language databases use as long as I feel in control and the general concepts of relational databases remain the same.

Re:shortcomings to sql?

[MrLint]

Well as for the first part, this is really due to poor design. A null is intended to be used as a special thing. In fact null != null. Its meant to behave like that. This is the reason default values exist. if you want to have a column with empty rows to be 0 and not null then you specify that when you are planning. and when you are counting objects you can have 0. 0 is a value. null isnt a value. There are reasons why nulls are this way. One of which is to look for rows that have no data. If you think dealing with nulls in this sense is hard, imagine trying to programatically deal with what is 'empty' or '0' in an arbitrary column? Is this 0 a numeric? its is a 0x00? is it 0x30?

Null was meant to take the place of all the hack jobs that were used in older databases to signify non-value, NaN and so on.

Part of using SQL in the manner that it is, is that you get what you ask for. You dont get what some programmer *thinks* you want. Because at some point you may want something else. I really dont want a computer to guess at what I really want. I want it to tell me what it knows.

Re:shortcomings to sql?

[gfody]

comparing assembly language to sql?!
sql doesn't cut it because it's too high level. trying to do anything somewhat complicated in sql leads to headache and turmoil. the big problem is that there is no lower level interface to communicate to your database server with.. all you can do is hand it sql statements and get your results.

why bother writing a better sql? how about a low level object based api. use whatever language your client is already written in (so long as its oo). fuck learning another busted ass super high level language.. give me objects, methods, exceptions, etc.

Re:shortcomings to sql?

[Anonymous Coward]

Not really, AFAICT. This sort of stuff can get very messy, very quickly. For instance, you might do something like:

SELECT * FROM CUSTOMER, CONTACT, PRODUCTS
WHERE Customer.CustomerID = Contact.CustomerID
AND Customer.CustomerID = Products.CustomerID

but then you have to deal with the fun of sorting through the redundant customer information, contact information, and product information. If your DBA is on the job, you shouldn't suffer from performance degradation to a significant degree just from doing something like:

SELECT * FROM CUSTOMER
WHERE CustomerName = 'Joe Bloggs';
SELECT * FROM CONTACT
WHERE CustomerID = (Joe Bloggs' Customer ID);

-- if you do, and if this stuff is important to you, you'll invest in more RAM and suchlike for your database server. Before you start worrying about performance impact, model it: do things the way you feel is best, and benchmark the results. Find the bottlenecks and optimise the wazoo out of it. Odds are it won't be as bad as you think it will be.

The other thing is, this sort of technique matches how code generally will populate structures, anyway: create the main structure, and then fill in the arrays of associated data. I don't know how the new system proposes to deal with these problems, but I do know that battling the DB in the way you're suggesting is going to be a pain in the butt for the coders, and likely to bite you in new and interesting (for Chinese values of "interesting") ways.

Re:shortcomings to sql?

[runningduck]

You could do this with a bridge table--an intermediary table that stores foreign key pairs from two data tables.

Re:shortcomings to sql?

[subStance]

This sounds more like a job for an XML database than a relational one - tree structures rather than fixed relations would allow you to do that.

Re:shortcomings to sql?

[Anonymous Coward]

This came up with me when I wanted to keep client info. A variable number of phone numbers,

create table client (
clientID int NOT NULL,
name char (whatever),
etc.
)

create table address (
clientID int NOT NULL,
addressID int NOT NULL,
street char (whatever),
city int (this would be a foreign key in case you were wondering)
etc.
)

insert into client values (1, 'cowboy neal') // his first address
insert into address values (1,1,'123 main st.') // his second address
insert into address values (1,1,'245 51st st.')

and that, my friend, is what we call a one to many relationship. Database 101

To find all of cowboy neal's addresses:
select * from address where clientID = (select clientID from client where name = 'cowboy neal')

that will return two rows.

Re:shortcomings to sql?

[abulafia]

Well as for the first part, this is really due to poor design.

[...]

Null was meant to take the place of all the hack jobs that were used in older databases to signify non-value, NaN and so on.

Yes, and this is a big part of the point of D. The authors assert that the way that current DB structures are designed leads to bad design- that there shouldn't be nulls. They have a lot of good reasons for this belief, and it is true that SQL leads people to rely on nulls in cases is a sort of synergystic nightmare of lazyness.

(1.08X10^8 Clueless points to the first person who says they don't use nulls, and instead set everything that is be null to ''.)

The language goes hand in hand with designing databases differently, without the influence of SQL.

Re:shortcomings to sql?

[Anonymous Coward]

I totally get your point, but there's always a workaround -- even if it is specific to one database vendor. I actually use the latter, never the former.

1. SET ANSI_NULLS

2. SELECT * FROM WHERE ISNULL(X,0) 47

Re:shortcomings to sql?

[kfg]

More to the point, RTFM, i.e. Date's book, which explains what the relational calculus is, and thus what relational databases are all about.

At the very least go over an introductory text on set theory and prepositional logic so that you might at least have a basic understanding of what a database query even is.

Before that about all that can be said to answer OP's question is that SQL is mathmatically incomplete and incorrect, i.e., it gives the wrong answers.

It amazes me that people got all worked up over the Pentium rounding error when it wouldn't even effect most people, but businesses are willing to put up with provably far greater flaws in software that affects everyone's data, and with nary a peep of complaint about it.

KFG

Re:shortcomings to sql?

[Anonymous Coward]

Ahhh, screw me. Now if I could only learn about these tag thingies...

SET ANSI NULLS (ON/OFF)

SELECT * FROM TABLE WHERE ISNULL(X,0) <> 47

Re:shortcomings to sql?

[Anonymous Coward]

well, then you learn to code around it.

"all entries where isnull(fieldx) 47"

Once you get the way that NULL works into your head, it makes sense.

Look at C. If you're declaring two pointers (on different memory spaces), does saying P1 = P2 make much sense, unless you're checking for both of them to be null pointers? No.

Sure, it's annoying sometimes to remember, but if that is such a bother, then add default values to fields.

The biggest kicker is with Oracle:

sqlplus> select nvl('','null') empty_string from dual

empty_string
_____________
null

sqlplus>

In most other SQL dialects, it returns "".

Re:shortcomings to sql?

[Anonymous Coward]

Question was how to do that for 100 tables without 100 different querys.

Re:shortcomings to sql?

[Tassach]

"you ask a database for "all entries where field X is not equal to 47" it won't return any of those where field X is null because instead of saying "Null doesn't equal 47", the value "null" is deemed not to be comparable with any non-null field."

The FA is F'ed.

select * from foo where X is not null and X != 47

or

select * from foo where isnull(X,0) != 47

What's the problem with that?

Simply put, a null in any part of an expression renders the entire expression null. What's the value of (69 + null)? It's not 69, it's null. So what's the value of (null = null)? It's not true, it's not false, it's NULL! Once you wrap your brain around that concept, nulls won't give you any more problems.

Nulls get a bad rap because non-database programmers don't understand them and therefore misuse them. SQL is a very different language than most programmers are used to using. SQL is a functional language -- you tell it WHAT you want it to do, not HOW you want it to do it. It is also set-based, which can be confusing to someone who's used to using an iterative approach. This doesn't make it "good" or "bad", it just makes it different. It's a specialized tool for a specific job. As with any language, it takes effort to master SQL. Yes, SQL does have warts; but name me one language that DOESN'T have warts.

Re:shortcomings to sql?

[Tassach]

make the first one:

select * from foo where X is null or X != 47

Bad DBA! No coding after bedtime!

Re:shortcomings to sql?

[Anonymous Coward]

could it be that the areas where it is "incomplete" (besides, as noted in "Metamagical Themas", any formal system, as SQL is, is incomplete) don't really matter much?

Sure, there can be issues with the ordering of joins and the results returned in different situations.

Sure, it would be nice if more databases supported FULL OUTER JOIN.

Blah blah blah.

Maybe SQL is just good enough for just about everything?

Well, if you thought about it, the occaisional inconsistencies with Excel (and probably Lotus 1-2-3) recalculations should be of much more concern.

Re:shortcomings to sql?

[kfg]

could it be that the areas where it is "incomplete" (besides, as noted in "Metamagical Themas", any formal system, as SQL is, is incomplete) don't really matter much?

No.

KFG

Re:shortcomings to sql?

[Anonymous Coward]

Try MySQL they really limit joins!!! (or at least they used to)

Re:shortcomings to sql?

[j3110]

That's silly...

Lets give an example, and show how it's really done.

A has aid, adata
B has bid, bdata

Don't make A have aid, adata, bids
Make B have bid, bdata, aid.

What if you would need more than one aid in B?

Then you have an N:M mapping, and you have to create a seperate relation. So, you make C that has aid,bid.

Yeah, this requires an extra query or repeated data in a join. I think that's what they are trying to fix here. It's better to have all your related data stored in seperate relations and have a smarter querying tool capable of returning the data in a relational format instead. That way you can execute one query for just about all your needs.

Re:shortcomings to sql?

[zoglmannk]

I consider myself a SQL god. Okay, that's a bit strong. Seriously though, what is so limited about SQL? Take a look at the following SQL statment that I wrote.

select aging_desc,
decode(deposit_desc,'Calling Card','Authcode','Ethernet','Dialin',deposit_desc) as deposit_desc,
(sum(nvl(charges_not_paid,0)) - sum(nvl(overpayments_left,0)))/100 as grand_total
from
(
select all1.acct, all1.mindex, all1.aging_desc, all1.inv_date, all1.deposit_desc,
nvl(charges.scharge_amt,0) as charge_amt,
nvl(trans_charges.spaidoff_chargeamt, 0) as paid_off_amt,
nvl(credits.charge_credits,0) as credit_amt,
nvl(trans_credits.scredit_processed_a mt,0) as credit_processed,
nvl(credits.charge_credits,0) - nvl(trans_credits.scredit_processed_amt,0) as credit_not_processed,
nvl(charges.scharge_amt,0) - nvl(trans_charges.spaidoff_chargeamt,0) -
nvl(trans_credits.scredit_processed_amt,0) -
(nvl(credits.charge_credits,0) - nvl(trans_credits.scredit_processed_amt,0)) as charges_not_paid,
overpayments.soverpayment as overpayments_amt,
overpayments.samt_consumed as overpayments_consumed,
overpayments.sleft as overpayments_left
from (
select acct, i2.inv_date, deposit_desc, mindex, aging_desc,
i2.inv_date || '-' || deposit_desc || '-' || acct as combiner
from inv i1, acct,
(
select distinct deposit_desc
from (
select deposit_billnum_type, deposit_desc, deposit_type
from deposit_type
union
select 'U', 'Unknown', 'U'
from dual
)
),
(
select add_months('01-SEP-04',0) as inv_date,
'Current Due' as aging_desc,
0 as mindex
from dual
union
select add_months('01-SEP-04',-1), 'Past 1-30', 1 from dual
union
select add_months('01-SEP-04',-2), 'Past 31-60', 2 from dual
union
select add_months('01-SEP-04',-3), 'Past 61-90', 3 from dual
union
select add_months('01-SEP-04',-4), 'Past 91-120', 4 from dual
union
select add_months('01-SEP-04',-5), 'Past 121+', 5 from dual
) i2
where inv_acct = acct and acct_type = 'S' and inv_flags is null
and i1.inv_date = '01-SEP-04'
order by acct, i2.inv_date, deposit_desc
) all1,
(
select inv_acct, greatest(inv_date, add_months('01-SEP-04',-5)) as inv_date,
deposit_desc,
greatest(inv_date, add_months('01-SEP-04',-5)) || '-' || deposit_desc
|| '-' || inv_acct as combiner,
sum(charge_amt) as scharge_amt
from (
select i1.inv_acct, i1.inv_date, nvl(deposit_desc,'Unknown') as deposit_desc,
sum(nvl(charge_amt,0)) as charge_amt
from inv i1, charge, deposit_type, acct, inv i2
where i1.inv_flags is null and i1.inv = charge_inv
and charge_billnum_type = deposit_billnum_type(+)
and i1.inv_acct = acct and acct_type = 'S' and
acct = i2.inv_acct and i2.inv_date = '01-SEP-04' and
i2.inv_flags is null and charge_amt > 0
group by i1.inv_acct, i1.inv_date, nvl(deposit_desc,'Unknown'), i1.inv_acct
union all
select i1.inv_acct, i3.inv_date, nvl(deposit_desc,'Unknown') as deposit_desc,
-1*sum(nvl(t1.trans_amt,0)) as credit_processed_amt
from inv i1, charge c1, trans t1, deposit_type, acct, inv i2, trans t2, charge c2, inv i3
where i1.inv_flags is null and i1.inv = c1.charge_inv(+)
and t1.trans_credit = c1.charge
and t1.trans_valid is null and c2.charge_billnum_type = deposit_billnum_type(+)
and i1.inv_acct = acct and acct_type = 'S' and i2.inv_acct = acct
and i2.inv_date = '01-SEP-04' and i2.inv_flags is null
and t1.trans = t2.trans_tran_src(+) and t2.trans_valid is null
and t1.trans_charge = c2.charge and c2.charge_inv = i3.inv and
i3.inv_flags is null
group by i1.inv_acct, i3.inv_date, nvl(deposit_desc,'Unknown')
union all
select i1.inv_acct, i1.inv_date, nvl(deposit_desc,'Unknown') as deposit_desc,
sum(nvl(

Re:shortcomings to sql?

[PinchDuck]

Gilk180: Right Freakin' On, brother! I spent too many years worrying about "sparse indexes" in IMS to bitch about SQL. If I had mod points, they would go to you.

Re:shortcomings to sql?

[ChiRaven]

People have been looking for the OODBMS "holy grail" for decades, and there STILL isn't one that measures up to what SQL can accomplish. OOA&D just CRIES for a good object data base, but the realities are far short of the needs and the promise. If these people can change that, good for them!

Re:shortcomings to sql?

[msuzio]

That's the idea, you know. SQL is a declarative language, not procedural. You tell the database engine what you want, not how to get it. The idea is that then the query analyzer figures out the most efficient how.

Re:shortcomings to sql?

[TheMiller]

It would be perfectly possible in a relationally complete language that includes a relational MINUS operator, without NULLs entering into it at all. And Tutorial D, on which the object of this article is based, does of course include such an operator. Whether NULLs are desirable or not is a matter of ongoing raging debate. I've found them easy to avoid, and queries of all sorts easy to understand without them.

Re:shortcomings to sql?

[eric2hill]

Oracle has nested table support (i.e. a small table inside a cell in another table) as well as array/collection support. No problem.

I'd still use separate tables. It keeps things 100% compatible with every database tool, report writer, web scripting engine, and development tool available. In a corporate setting where you may want to do something different in 5 years, keeping things simple is paramount.

Re:shortcomings to sql?

[I Like Pudding]

Exactly. It's this sort of mentality that leads to people querying the DB 400 times per pageview through an O/R library.

You see, when optimizing, readability is usually the first thing to go. The second is patience, with the third being execution time. This is the Tao of Optimization; how can it be otherwise?

Re:shortcomings to sql?

[pigiron]

That's not SQL it's Oracle. I'll bet it takes 40 seconds to run: on a set of tables with no more than a dozen rows EACH! Bwahahahahahahaha!!!

Re:shortcomings to sql?

[trewornan]

Two different addresses with the same "addressID" - not recommended practice! Is "addressID" supposed to be the primary key, if so this would give an error. Your comments are on the wrong lines - bad comments are worse than no comments. Strictly speaking it's not an error but most people put the primary key first.

You are a luser and I claim my $5.00

PS. Database 101 is a little advanced for you.

Re:shortcomings to sql?

[rnd()]

You would want to use multiple tables and use a join to tie the data together...

Consider a phone numbers table:
phonenumberID, phoneNumberTypeID, number

and a phone number type table:
phoneNumberTypeID, name
example: 1, cellular; 2, home; 3, office

Now, when you know a customer ID you can simply query the phone numbers table for all matching numbers, and join with the phoneNumberTypes table to determine what the number is.

Re:shortcomings to sql?

[rycamor]

Read "The Askew Wall" and other documents by Hugh Darwen at www.thethirdmanifesto.com for some really good brief descriptions of some of the many logical inconsistencies with SQL.

Really, 95% of the problems that Chris Date and Hugh Darwen have with SQL revolve around an incomplete relational treatment of data. For example, by definition, a relation cannot have duplicate tuples (rows), but a table in SQL *can*. Also, columns in a table should not have a left-to-right ordering, which they do have in SQL (they should be thought of only as attributes, not positionally). Also, SQL fails to completely implement closure, where the result of *any* relational expression becomes a relation which can be in itself modified by any other relational expression. Also, SQL fails to provide a truly extensible approach to user-defined datatypes or domains.

Etc... etc... there are many other small kvetches and some big ones with SQL. Read the Darwen docs, and you will see some interesting things.

Essentially, the problem comes down to an ad-hoc experimental language (SQL), which was only intended as a temporary solution, which was then taken over and re-designed by a committee (ANSI).

Re:shortcomings to sql?

[Anonymous Coward]

(1.08X10^8 Clueless points to the first person who says they don't use nulls, and instead set everything that is be null to ''.)

Those points go to Oracle. Historically, their VARCHAR type does not distinguish between NULL and blank (''). They've had to phase in a migration away from that stupidness - VARCHAR2 exists with the old behavior, and IIRC Oracle 9i finally made VARCHAR behave correctly.

Re:shortcomings to sql?

[Prof.Phreak]

The only shortcoming to SQL are the people who find it confusing. I'm serious. Most major database issues can be traced to people improperly designing or doing things (ie: generally lacking any sort of understanding when it comes to databases).

Re:shortcomings to sql?

[Anonymous Coward]

If you have to ask then you've obviously never used sql in a reasonably sized, real-world application (i.e. more than a couple gigs of easily related data).

Re:shortcomings to sql?

[FriedTurkey]

I am impressed by the SQL but dude you should learn to write Oracle stored procedures. This obviously is a reporting query getting aggregates. I don't know your requirements but if it is a Crystal or Actuate report you can call a stored procedure. It would save a load on the database and the your time writing these things.

Re:shortcomings to sql?

[zoeblade]

"you ask a database for "all entries where field X is not equal to 47" it won't return any of those where field X is null because instead of saying "Null doesn't equal 47", the value "null" is deemed not to be comparable with any non-null field."

Correct me if I'm wrong, but I thought the whole point of null was to act as a kind of placeholder when you don't know what the value is yet. So you can say "where X is not equal to 47" and it'll get all the tuples where it definately isn't 47, or you can say "where X is not equal to 47 or X is equal to null" and it'll get all the tuples where it's either 47, or a null value that may or may not turn out to be 47 later on.

Or am I missing something fundamental?

Re:shortcomings to sql?

[will_die]

However the cherry on top of it is that it is deprecated Oracle.

Re:shortcomings to sql?

[SweetCyanide]

One way to do this is through the use of compound types, such as the array and the row type.

Re:shortcomings to sql?

[dracocat]

I guess you would be very disapointed to know that probably most of the queries that you write are rewritten by your database to be optimized whether you optimize them by hand or not.

You would be surprised at how much your database knows about itself, and what it is able to do with that information in optimizing your queries.

But then I am sure you know all this better than me, since you sound like the DBA here, and I just rely on highler level stuff.

Re:shortcomings to sql?

[Anonymous Coward]

You claim that 3 value-ed logic is easy and you can learn it quickly. Are you aware about how many operators you need to fully support tri-value logic?

I suspect you don't. Null is _never_ needed.
Broad statement indeed but I can prove it with boolean logic and simple set mathematics.

I'd like to see your argument.

Computer Science _is_ applied mathematics.

Re:shortcomings to sql?

[TheClarkey]

It goes against the idea of normalisation. Basicaly, normalization is a set of rules that ensures that you can store your data consistently and efficently.

I think this link here might answer your question fully.

If you'd like to know more about DB theory I'd suggest Date's book: An Introduction to Database Systems.

Re:shortcomings to sql?

[zoglmannk]

The primary table, trans, has several million rows. Charge has about a million rows. The only table returning few rows are those inner selects from dual.

Re:shortcomings to sql?

[Anonymous Coward]

Or am I missing something fundamental?

Any of the fields might or might not be 47 at some point in the future. NULL just means "I don't know" -- "I can't answer the question about this data". You asked a specific question about the data: show me all the records where the fields isn't 47. It gave you a specific answer -- not speculation or guesswork.

Re:shortcomings to sql?

[Hognoxious]

If anything comes to replace SQL, I think it won't stand standard long enough as vendors will start adding more and more odd extensions, so the story will probably repeat.

A good point, but that's a separate issue to whether SQL itself is inferior to $NEWTHING.

Re:shortcomings to sql?

[rdc_uk]

"My point was just that it would be nice to have it so I didn't need a new table for every single thing I wanted multiple references of in order to avoid tons of sql calls to different tables slowing down our webserver."

That sentence tells me instantly that your _really_ seriously have misunderstood the RDBMS paradigm.

Use a table each for:
client, address, phone, product

RELATE between these tables to build your whole datum.

i.e. you get client ID to lock you client, you then query for ALL addresses for the client (you can mark one as primary), same for phone numbers (though they may or may not be collated to an address), then query for all that client's products.

Hell, in our DB, the "product" table has 3 fields:

id, type, value

products are stored thus:

"code", "name", "product name"
"code", "platform", "product platform"
"code", "rrp", "product rrp"
"code", "image", "product image"

etc.

this allows different types of product to store different sets of information (e.g. dvd and videos have their rating, joysticks do not)
you can still query "SELECT DISTINCT type FROM product WHERE id = 'code';" to tell you what bits of info are available for a given product, so you can display them.

Our DB handles multiple vendors for products, so there is a "vendor" table with their details. Each vendor can set their own price for any product, so there is an "offer" table which relates a product, a vendor and the actual price...

Building you final data from multiple tables is how you use RDBMS systems, trying to avoid that is like saying "I'm programming my computer, but I'd like to avoid using the keys with letters on them".

You could learn the BF programming language... Or you could do things properly :)

----------

"Alternativly, is there a way to do that so that you can do it with multiple tables but with a single query?"

Oh dear god! Go back to Access.

Re:shortcomings to sql?

[Anonymous Coward]

I think you will find in most RDMS that you can either store a single value in a field (eg date of birth) or a "link" to another table i.e. a list. So for mulitple phonenumbers, addresses, order items etc you would have seperate tables. You then construct SELECT style staements to "Join" these as needed, the end result of any SELECT style staement is a table.

Re:shortcomings to sql?

[jmrSudbury]

I understand why Chris Date and Fabian Pascal get upset. They have been telling people about relational databases for years, and most people still don't get it. I know of 2 database models: Hierarchial and relational. Your object database is a relational database with objects. Of course, the relational model does not exclude the use of objects, so object databases are still based on the relational model. The XML databases are hierarchial. They have meta data in the tags, but they exist in separate files. This is a great way of transferring data between people which then gets imported into a table of the relational database. Bank transactions are a good example. The associative database is based on related tables storing the data. The items and links structures are simply two tables that are related. The associative part is simply a program that uses a relational database to hold its data. Please tell me what I am missing.

Re:shortcomings to sql?

[ahmusch]

Respectfully disagree.

ORA92:create table t1 (vc2_col varchar2(10), vc_col varchar(10), char_col char(10));

Table created.

ORA92:insert into t1 values ('', '', '');

1 row created.

ORA92:select count(*) from t1
2 where vc2_col is null
3 and vc_col is null
4 and char_col is null;

COUNT(*)
----------
1

ORA92:select * from v$version
2 /

BANNER
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

Re:shortcomings to sql?

[Anonymous Coward]

Two different addresses with the same "addressID"

That was a typo. And the comment was long enough already so I didn't add "identity (1,1)" to the address specification, or use alter table to make addressID the primary key. Obviously, each address has its own ID.

Your comments are on the wrong lines

for whatever reason, slashdot ate the crlf before the comment. Welcome to the imperfect internet. You have to learn to accept that you are reading a message board and not someone's term paper. And also, you have to get that stick out of your ass.

Strictly speaking it's not an error but most people put the primary key first.

Well, that's not how we do it where I work. And we have a very good reason. Parent tables go above child tables on our ER diagrams, so foreign keys go above parent keys in the table definition and on the diagram the lines don't cross. It makes it easier to read. You should try it.

Database 101 is a little advanced for you.

I promise you I know more about database design than you do.

Re:shortcomings to sql?

[Gilk180]

Your object database is a relational database with objects.

There are object-oriented databases. I'm not 100% sure that object-store is one of them, but oo databases are definitely not relational databases. For one thing objects have identity external to their data. In the relational model, the ONLY way of identifying a relation(aka row) is by the data it contains. Additionally, relationships between objects are viewed as links/references to objects, not foreign keys. oo databases allow for methods inside objects. The fact that the storage usually uses a relational DB as the back end is irrelevant to the programmer. He doesn't care if flat files are used either as long as things are fast.

Just because something uses a relational DBMS as a back-end doesn't mean it doesn't provide a model that is not relational. That would be like saying that a relational DBMS isn't any different than a flat file because flat files(or block devices) are that back end used to store the data.

The associative part is simply a program that uses a relational database to hold its data.

I think I may have used the wrong term there. I was referring to things like the berkeleyDB, which provides an interface to associate keys and values, but not much more. Basically a persistent lookup table

Re:shortcomings to sql?

[cayenne8]

" SQL sucks. If you need to do anything much beyond a simple query you find yourself buried in the details of that particular db's implementation."

Well, not so much the databases implementation, but, you DO need to know the data model. And if a database is properly designed and normalized, is pretty easy to store and retrieve from. You do however, need to know something about data models and SQL, but, you don't need to be a DBA. In fact, most DBA work has nothing do to with this type work. It is mostly database management and maintenance.

Re:shortcomings to sql?

[don.pratt]

It is possible, just not using a standard RDMBS. I worked at a company a few years back that used Pick (multi-relational) databases. Being a SQL/RDBMS guy, I never quite 'got it', but have a look at http://members.ozemail.com.au/~dhona/pick.html for an intro.

Re:shortcomings to sql?

[jmrSudbury]

What is the difference between a link/reference to an object and a foreign key? They are both relationships. Associations if you like. The relational model does not exclude the use of objects. Relational database management systems have been using objects for years. Those objects may infact have their own methods. Relational theory does not exclude this. so, you seem to be telling me that your object oriented (oo) database is using a hierarchial design. The fact that an oo database uses an Relational DBMS means that it is a relational database as long as it uses relationships. I could use any database, put in a table that contains clientname, address, invoicenumber, itemnumber, quantity, unitprice, invoicetotal. I have seen novices do this. This is a heirachial database. Adding a couple more tables like customers, invoice, and invoicedistributions as well as relationships between them lends to less duplication of data by using a relational design. Using a hierarchal design is fine for a grocery list or phone book. If your oo database is indeed only a single table, then it may be using a hierarchial design. I just don't see too many business applications that employ only a simple list that would require an object oriented approach. Perhaps a phone book, but then why would a phone book need objects. Can you suggest some? About the flat files, I don't care how a database is implemented. How it is stored on the harddrive is irrelevant. The fact that it uses tables and relationships is that makes it a relational database. Physical implemention is outside of the relational model.

Re:shortcomings to sql?

[Anonymous Coward]

Ooh, you're right; it's still stupid. Maybe it's a planned-for-10i thing, then.

Re:shortcomings to sql?

[zaroastra]

Youre just assuming he doesnt want to do that. He says he wants. I know i certainly wanted. Ended up using postgresql and arrays. Not the best thing around (mostly, because arrays are very limited in pgsql)

I think that is the reason people are asking the question "Could there be something BETTER than SQL" Because the more you use it, the more you get into situations where complicate/extravagant solutions are reached because complicate/extravagant problems demand them.

I believe there could be something better. And I know a lot of people in academic research are thinking about it. Maybe someone somewhere even had good ideas. But good ideas without a good common implementation are wortless.

Because lets face it, the real power behind SQL is not the "already proven dumb" null value, nor the Query Language. Its the STANDARD part of it that makes it powerfull. (even if in the end its not so standart and not so powerfull)

Lotus Domino...

[Kenja]

Try using Lotus Domino for a week. You'll be begging to go back to SQL.

Re:Lotus Domino...

[Anonymous Coward]

Lotus Domino (or Notes) is *not* an RDBMS. It's good is in the fact that it's a groupware with great routing capabilites.

What's the use?

[generalpf]

Is there anything that SQL can't do? I've been using various RDBMS for years and it hasn't come up yet.

Re:What's the use?

[treat]

Is there anything that SQL can't do? I've been using various RDBMS for years and it hasn't come up yet.

Is the sole issue what it can and can't do? what if there was an easier way to express joins? Most queries I write have more joins than actual query. Even though the database already knows the relationships between the tables.

Re:What's the use?

[neurojab]

Have you ever seen a 25 way join or a 30 way UNION? I've seen queries that go past a given RDBMS's 32k query size limitation. Even worse, I've seen the code that GENERATES these horrendous queries. It's like seeing your parents having sex; it changes your life forever.

Please, please, there must be a sane way to query data from a highly normalized database.

Re:What's the use?

[Anonymous Coward]

Solve the halting problem.

Re:What's the use?

[Anonymous Coward]

Have you ever seen a 25 way join or a 30 way UNION?

Call me crazy, but if you have a 25-way join, don't you think you have bigger problems than your querying language? Maybe the person that is asking for the join needs to change their business processes.

Re:What's the use?

[timeOday]

Please, please, there must be a sane way to query data from a highly normalized database.

Prolog! Logic languages are well suited to relational data, where a table maps to a predicate. The logic programming community spent a couple decades trying to convert everybody from SQL and nobody listened.

I was about to say SQL is like COBOL, but SQL seems to be even more persistent so perhaps it's not as flawed.

Re:What's the use?

[Anonymous Coward]

Even worse, I've seen the code that GENERATES these horrendous queries. It's like seeing your parents having sex; it GENERATES your life.

Re:What's the use?

[Fred IV]

what if there was an easier way to express joins? Most queries I write have more joins than actual query.

I've seen some products do this by having a developer write a catalog that hides all the joins from the end-user as done in Cognos Impromptu.

This simplifies the experience, but if your catalog writer makes a mistake, everybody pays and it becomes impossible for anyone to get good data or even see why bad data is coming through. If you're lucky the user is close enough to the data to call out the bad data and the catalog writer is questioned. If you're not lucky, nobody notices until long after important decisions are made based on the bad data

Writing joins can seem cumbersome, but it gives complete control to the person writing the query. If you're looking to save time, start a join collection and jot away useful joins in a text file somewhere. Make a copy available to your workgroup so everyone can benefit from the standardized joins and so that there's a good chance that a poor join will be identified before the damage caused by it gets expensive

Re:What's the use?

[mcrbids]

Even worse, I've seen the code that GENERATES these horrendous queries. It's like seeing your parents having sex; it changes your life forever.

Amen, I HEAR you, brother!

I've had to write "dynamically generated" queries - and they are a TOTAL MESS, with lots of crappy if statements and string appends...

300 lines of code to properly parse a search page response, to generate a 20-line query that executes against 5 tables.

Ugh.

Re:What's the use?

[cdc179]

It's called created views. This way you just access the view in your code.

Most people lack the suficient skills to program good DB applications. And they don't find anybody with the DB skills to help on the backend.

This is where most of the issues come into play. Get a DBA that knows what they are doing.

Re:What's the use?

[fupeg]

I wouldn't call you crazy, but definitely naive. Welcome to the world of big, legacy systems. Comments like "change your business process" will get you nothing but a pink slip (or worse, a job in QA.) Go read any large company's financial statements sometime. There's a good chance that one of these monster join/unions was used at some point in generating the report that was used for the statement. Chances are also that it was auto-generated by some very expensive ERP software. Now go learn about these things and stop acting like know anything about "business processes."

Re:What's the use?

[mibus]

Man, you just gave me a really bad picture in my head.

I hope you're happy! :)

Re:What's the use?

[ggambett]

Please, please, there must be a sane way to query data from a highly normalized database.

Serve yourself!

(full disclosure : that's my day job)

Re:What's the use?

[sik0fewl]

Of what? A 30 way union or your parents having sex?

Re:What's the use?

[kpharmer]

sure - code modularization is way too unpleasant: we do it through views typically rather than table functions. Theoretically we could use table functions, but they're non-portable and often have performance problems.

and how about network data models - that allow nearly infinite flexibility. They're cool stuff, can be built in a RDBMS, take advantage of many database features BUT aren't supported with constraints.

Re:What's the use?

[alder]

Even though the database already knows the relationships between the tables.

FK is a usual (and typical) path to join tables, though one is not required to follow it - anything that matches PK will do :-)

Re:What's the use?

[jacobcaz]

• Call me crazy, but if you have a 25-way join, don't you think you have bigger problems than your querying language? Maybe the person that is asking for the join needs to change their business processes.

Go work in a big ERP system. We use PeopleSoft and one of our tech has been debugging an AP (maybe GL?) query that's not working as expected and it's a 20 union beast. That's delivered too, not some home-rolled query. I'm sure there are worse queries out there lurking in the hearts of our system too.

As for just changing our business process, well that's fine in an ideal world, but in practical reality it ain't gonna' happen without an act of Congress. You have to have someone who knows "best-practice" methodolgy come up with a new process, get sign-off across multiple departments and our auditing company so our banks and major vendors are okay with the changes, and then begin process training and roll-out. And that's for a small, private company; if you're public and have to add SOX compliance to the mix...well, good luck.

Re:What's the use?

[Slime-dogg]

I spent half of my databases class learning datalog, which, I imagine, is an offshoot of prolog. I'd have to say that writing queries in datalog is superior to anything that you can do in SQL, you just need to be able to think in that way.

Most people are accustomed to procedural stuff, and SQL has been hacked upon to fit into that mold. A simple SELECT statement is more like setting rules on a state machine, but when you get into transactions, cursors, if statements, temporary tables, etc., it becomes more like a super-verbose programming language.

I use SQL exclusively at work, and I suffer because I have to maintain stored procedures that grow into the thousands of lines, when a simple program written in C# or Java could do the equivalent in a couple hundred. The reason for the stored procs is that it's easier to schedule a stored proc to run at a certain time every day, week, or whatever. I'm certain that some of these queries would be vastly simplified if there were Datalog extensions to SQL Server.

Re:What's the use?

[Anonymous Coward]

Yes. Aggregate joins mid-query so that its possible to Do The Right Thing when joining more than two tables with aggregates.

For instance, take clients c, bills b, and payments p: Its impossible to make a single query that can give you c.name, sum(b.owes), sum(p.paid) so that you can see who owes you money. The closest you can come is a subquery.

Or, lets say you want to see people and how many times they bought from you in the last month, the month before that, and the month before that? Until I used pgplsql to write a new aggregate function count_when_true() I actually had a query that read something like c.name, count(b1.*),count(b2.*),count(b3.*)... from client c left outer join bill b1 on b1.client=c.id and age(b1.date)'1 month'::interval left outer join bill b2.... well, you see where this is going.

The people at this point screaming about the division between storage and business processes think on this: By performing separate queries from the code generating the report, I expend queryoverhead*N+c*b*N where N=number of months I want on the report and c and b are the costs of fetching a row of each. The twisted thing above expended queryoverhead+c*b*N. And by writing my new function, the entire operation could be performed in queryoverhead+c*b using count_when_true(age(b.date)'1 month'::interval) etc and only having to join b once. Plus, with the count_when_true() function, the code is MUCH easier to autogenerate for variable N.

Re:What's the use?

[Eminor]

I was thinking about a new language the other day. SQL can be very inelegant. It can get very ugly when creating a stored procedure (I know there not part of the SQL specification) and you need to call an execute on query in a string and you're using constant string values. You need to figure out the right number of quotes to get a qote at the right 'level'. It can very easily be done, but there should be a better way of doing it.

SQL can give even a seasoned programmer a hard time.

Re:What's the use?

[dcam]

This is often solved with views. In other words you cache collections of JOINs.

The problem with this is that quite often what you are joining together is specific to some parameters (for example in a stored procedure). What I'd love to see are parmaterised views that you could join together. At the moment the only way you get this is by using temp tables returned from procs and joining on them.

Re:What's the use?

[Tablizer]

query that's not working as expected and it's a 20 union beast.

Lots of UNIONs are usually a sign of poor table design in my experience. Generally it means that a column format is being replicated to multiple tables, which should instead be consolidated to one or fewer.

Sometimes OO proponents will create a table for each "subtype", which is a no-no in my book. OO design rules do not always translate to good relational designs.

However, I realize that one is often stuck with bad designs from others and needs a language powerful enough to deal with it regardless of whose fault the bad schema is.

Re:What's the use?

[Tablizer]

I believe there are some dialects that can use the relationships defined in the constraints (or ER diagram) if you want to. This can simplify join syntax. Note, however, that one should not rule out the need for custom joins.

Re:What's the use?

[Piquan]

Prolog! Logic languages are well suited to relational data, where a table maps to a predicate. The logic programming community spent a couple decades trying to convert everybody from SQL and nobody listened.

Funny you should mention that. I'm now working on a program that takes user queries (from a Perl program), converts them to Prolog, and finds the solutions against a SQL database.

It seems to work great, so far.

Re:What's the use?

[Anonymous Coward]

A 30 way union, obviously...

Re:What's the use?

[Anonymous Coward]

Two words: updateable views.

Re:What's the use?

[lucabrasi999]

Now go learn about these things and stop acting like know anything about "business processes.

I think you are the one that is naive. Either that or you need to reassess how you've been performing your job. I work on ERP systems (PeopleSoft). I have been working on it for 9 years. In those nine years, I've been saying "change your business process" almost once a week, and I've never been laid off. Even during the dark days right after 9/11, I was working because the client knew I was correct.

There is only one instance that I know of where I have ever had to join more than 10 tables. And, that was because the client REFUSED to listen to me and change their business processes. I spent two months working at it and still I don't think it was worth the effort.

If I have to create a large join, I talk to the client about finding a different way to get the data. Some folks may think I cheat, as in create a smaller view, along with some external programming (using a language called SQR). I would disagree that it isn't cheating. A 25-way join is very inefficient and is not worth the cost of developing. You should try to find a new way to do the business.

Admittedly, when PeopleSoft delivers their software, they do deliver some massive joins. But, that is their decision. And, I do not recall a single instance where I've seen a delivered 25-way join. As long as I am programming for their crappy software, I never will never create one.

Re:What's the use?

[lucabrasi999]

We use PeopleSoft and one of our tech has been debugging an AP (maybe GL?) query that's not working as expected and it's a 20 union beast.

A 20 union beast in delivered AP or GL? I've been working PeopleSoft for nine years. I do not recall that ever occuring. The delivered printed Check in AP is a bit hairy, with 10 tables or so, but I don't think that is a union.

Seroiusly, tell me where that union is. I'm curious. Post it here.

Re:What's the use?

[julesh]

SQL won't allow you to store a relation inside a cell of a relation, and then perform operations on that during a select.

Not that I'm saying this project can -- it's using bdb as its storage engine, which isn't capable of that either, but in theory the language its implementing can.

Re:What's the use?

[KZigurs]

let's start with a highly normalized database. And keep on with sane data mining approach.

32K queries are not normal. Altough I must say, I have writtend and seen some queries that takes up to 20h to execute too.

Re:What's the use?

[Anonymous Coward]

While a view is a cool way to hide the complexity from the average user, someone has to write and maintain the view, and the view is still a 20 union beast, so it doesn't really solve anything.

No points, but thanks for trying.

Re:What's the use?

[jacobcaz]

• A 20 union beast in delivered AP or GL? I've been working PeopleSoft for nine years. I do not recall that ever occuring. The delivered printed Check in AP is a bit hairy, with 10 tables or so, but I don't think that is a union.

  Seroiusly, tell me where that union is. I'm curious. Post it here.

Ahh - I was wrong - the query isn't in GL or AP, it's in Cost Accounting. The delivered query is CM_PENDING_ACCTG_LINE and it's found in the following navigation: Cost Accounting -> Inventory and Mfg Accounting -> Analyze Inventory Accounting -> Pending Transactions. Setup your search criteria and click the "details" link next to "Accounting Lines not Created" and you'll be greeted with this 20 union query:

SELECT A.BUSINESS_UNIT, A.CM_BOOK, (CONVERT(CHAR(10),A.TRANSACTION_DATE,121)), A.INV_ITEM_ID, A.TRANSACTION_GROUP, A.COST_ELEMENT, A.PRODUCTION_ID, A.CONVERSION_TYPE, A.VARIANCE_TYPE, A.VARIANCE_SOURCE, A.CONFIG_CODE, A.REVALUE_TYPE, A.SEQ_NBR, A.CM_COST_MODE, A.POSTED_FLAG, A.DESCR50, A.REVALUE_FLAG, A.MG_PROC_INSTANCE, (CONVERT(CHAR(10),A.TRANS_DATE,121)), A.TRANS_TIME, A.DT_TIMESTAMP, A.CM_DT_TIMESTAMP_A, A.CM_SEQ_NBR_A, A.CM_SEQ_COST, A.CM_DT_TIMESTAMP, A.CM_SEQ_NBR, A.FROM_PRDN_ID, A.TO_PRDN_ID, A.ORDER_NO, A.ORDER_INT_LINE_NO, A.SCHED_LINE_NBR, A.SHIP_ID, A.CHARGE_TYPE, A.STORAGE_AREA, A.PROCESS_INSTANCE FROM PS_CM_NP_RECC_VW A WHERE A.BUSINESS_UNIT = :1 AND A.CM_BOOK = :2 AND ( A.TRANSACTION_DATE < :3 OR A.TRANSACTION_DATE = :3)
UNION
SELECT B.BUSINESS_UNIT, B.CM_BOOK, (CONVERT(CHAR(10),B.TRANSACTION_DATE,121)), B.INV_ITEM_ID, B.TRANSACTION_GROUP, B.COST_ELEMENT, B.PRODUCTION_ID, B.CONVERSION_TYPE, B.VARIANCE_TYPE, B.VARIANCE_SOURCE, B.CONFIG_CODE, B.REVALUE_TYPE, B.SEQ_NBR, B.CM_COST_MODE, B.POSTED_FLAG, B.DESCR50, B.REVALUE_FLAG, B.MG_PROC_INSTANCE, (CONVERT(CHAR(10),B.TRANS_DATE,121)), B.TRANS_TIME, B.DT_TIMESTAMP, B.CM_DT_TIMESTAMP_A, B.CM_SEQ_NBR_A, B.CM_SEQ_COST, B.CM_DT_TIMESTAMP, B.CM_SEQ_NBR, B.FROM_PRDN_ID, B.TO_PRDN_ID, B.ORDER_NO, B.ORDER_INT_LINE_NO, B.SCHED_LINE_NBR, B.SHIP_ID, B.CHARGE_TYPE, B.STORAGE_AREA, B.PROCESS_INSTANCE FROM PS_CM_NP_DEPC_VW B WHERE B.BUSINESS_UNIT = :1 AND B.CM_BOOK = :2 AND ( B.TRANSACTION_DATE < :3 OR B.TRANSACTION_DATE = :3)
UNION
SELECT C.BUSINESS_UNIT, C.CM_BOOK, (CONVERT(CHAR(10),C.TRANSACTION_DATE,121)), C.INV_ITEM_ID, C.TRANSACTION_GROUP, C.COST_ELEMENT, C.PRODUCTION_ID, C.CONVERSION_TYPE, C.VARIANCE_TYPE, C.VARIANCE_SOURCE, C.CONFIG_CODE, C.REVALUE_TYPE, C.SEQ_NBR, C.CM_COST_MODE, C.POSTED_FLAG, C.DESCR50, C.REVALUE_FLAG, C.MG_PROC_INSTANCE, (CONVERT(CHAR(10),C.TRANS_DATE,121)), C.TRANS_TIME, C.DT_TIMESTAMP, C.CM_DT_TIMESTAMP_A, C.CM_SEQ_NBR_A, C.CM_SEQ_COST, C.CM_DT_TIMESTAMP, C.CM_SEQ_NBR, C.FROM_PRDN_ID, C.TO_PRDN_ID, C.ORDER_NO, C.ORDER_INT_LINE_NO, C.SCHED_LINE_NBR, C.SHIP_ID, C.CHARGE_TYPE, C.STORAGE_AREA, C.PROCESS_INSTANCE FROM PS_CM_NP_VARC_VW C WHERE C.BUSINESS_UNIT = :1 AND C.CM_BOOK = :2 AND ( C.TRANSACTION_DATE < :3 OR C.TRANSACTION_DATE = :3)
UNION
SELECT D.BUSINESS_UNIT, D.CM_BOOK, (CONVERT(CHAR(10),D.TRANSACTION_DATE,121)), D.INV_ITEM_ID, D.TRANSACTION_GROUP, D.COST_ELEMENT, D.PRODUCTION_ID, D.CONVERSION_TYPE, D.VARIANCE_TYPE, D.VARIANCE_SOURCE, D.CONFIG_CODE, D.REVALUE_TYPE, D.SEQ_NBR, D.CM_COST_MODE, D.POSTED_FLAG, D.DESCR50, D.REVALUE_FLAG, D.MG_PROC_INSTANCE, (CONVERT(CHAR(10),D.TRANS_DATE,121)), D.TRANS_TIME, D.DT_TIMESTAMP, D.CM_DT_TIMESTAMP_A, D.CM_SEQ_NBR_A, D.CM_SEQ_COST, D.CM_DT_TIMESTAMP, D.CM_SEQ_NBR, D.FROM_PRDN_ID, D.TO_PRDN_ID, D.ORDER_NO, D.ORDER_INT_LINE_NO, D.SCHED_LINE_NBR, D.SHIP_ID, D.CHARGE_TYPE, D.STORAGE_AREA, D.PROCESS_INSTANCE FROM PS_CM_NP_TVARC_VW D WHERE D.BUSINESS_UNIT = :1 AND D.CM_BOOK = :2 AND ( D.TRANSACTION_DATE <

Re:What's the use?

[lucabrasi999]

What? You can't figure that one out? Come on! And, it's only a 20 way union. The GGP said a 25 or 30 way join. :)

Seriously, That is a bear. The best part is that it's a series unions built on views (I don't see any tables, only views). Obviously, the developer that created this monstrosity had to be on some painkillers. Once the original tables have some data loaded in them, the performance of that SQL will probably slow to a crawl.

I'm actually sitting in a building with some other (company unidentified, but different than my company) consultants and I had previously asked them about a 20 way union in AP or GL and they had never heard of it. One of them did mention that in some of the Supply Chain modules (and I think Cost Accounting would fall in that area) they had seem some mega-joins. Personally, I recalled a nasty series of left outer joins in the e-Procurment module (all of theleft outer joins I am thinking of were built upon each other using views).

Apparently, PeopleSoft has been going through some of the older modules (AP and GL) and they have eliminated some of the existing nasty SQLs. Now, according to rumor, PeopleSoft was trying to fix SQL problems that occur in the newer modules, like the one you show above. Of course, eliminating that monstrosity may take a few versions.

Re:What's the use?

[jacobcaz]

Yeah, I was mistaken when I originally said it came from AP or GL. That was my understanding of the case from the assigned tech in our last general IT meeting.

We've been fortunate so far that this query runs quickly (but doesn't return the correct data). One of the things we have spent a lot of time on is tuning existing queries and building indexes everywhere. It seems like the delivered SQL is either 1) not that tight or 2) needs additional non-delivered indexes to make things snappy.

A friend is a PSFT consultant and at a recient client they created two new indexs and reduced the time AP_MATCHING spent running from 42 hours to about 30 minutes. I haven't seen the hard evidence of that dramatic of a change, but I would believe a large amount of performance could be gained with better indexing.

Re:What's the use?

[lucabrasi999]

Your friend's story is not all that unusual, unfortunately. I've heard of similar stories around AR_UPDATE, altough that was a few versions ago. I haven't heard of anything that bad since version 8 of the software came out.

In PeopleSoft's defense, they did not create their system to run on one particular database. Of course, that means when you tune it to run better on DB2, that means your next client (on Oracle) would end up requiring a different type of tuning.

Not surprised about the data issue with that Query. You may want check and make sure that the configuration of cost accounting is correct. With all of those views, I imagine some of the joins actually cross over the same tables a few times (I hope you understand what I mean by that sentence). Nine times out of ten, it has something to do with configuration.

Re:What's the use?

[BarryNorton]

Yes. If you are interested please read this

I've had long arguments on Slashdot about this before with business app hackers who don't realise what a serious deficiency this is for a real data model...

SQL has shortcomings...is this news?

[Ars-Fartsica]

SQL also has decades of optimizations in reliable code...no one will be dropping their Oracle license over this.

Re:SQL has shortcomings...is this news?

[antifoidulus]

You are right, initially anyway. Anyone who can afford an Oracle license will not drop it, but my bet is that it will be picked up by hobbyists. If it's any good, then it will probably make it into small scale, relatively free standing commercial projects(so as not to disrupt anything that you already have built), if it's still good, then it may get adopted.
Keep in mind, the definition of "good" will vary from group to group. Hobbyists may want something that is interesting to play with, that will allow them to easily to powerful things, whereas commercial users will be more interested in scalability and reliability.
This is how ideas are supposed to evolve, unfortunately the "bandwagon" mentality grips a lot of our culture, and people will just jump on whatever the bandwagon of the day is(.com, outsourcing etc) with little regard to whether or not it is beneficial for their organization.

Re:SQL has shortcomings...is this news?

[Billly Gates]

Yep.

Perl, html, and Linux were not so hot either. Especially in conservative corporate America.

When something picks up, vendors will support it or a free alternatives will. After enough people use it, it will become standard.

Re:SQL has shortcomings...is this news?

[fred fleenblat]

Makes me wonder why oracle hasn't already invented an SQL replacement with better join facilities, or at least some SQL+ syntax to help with this.

Re:SQL has shortcomings...is this news?

[kfg]

No, it isn't news. Nor is it correct to say that Tutorial D was developed to overcome the shortcomings of SQL. It was developed as a teaching language to demonstrate and implement the relational calculus in a product independent manner, which SQL does not.

SQL was an alpha development language as a first step to testing the relational database concept, but IBM decided to release it as "good enough."

It isn't. Not nearly. ...no one will be dropping their Oracle license over this.

This, however, is likely true, more's the pity. However, if this new implimentation is not done in a hardware independant way much of the point may be lost anyway.

KFG

Re:SQL has shortcomings...is this news?

[Gilk180]

In corporate deveopment, the "bandwagon mentality" is not always a bad thing. If you are using what everyone else is using and you hire someone new, they can easily start right away with development.

It's all about maintainability.

Re:SQL has shortcomings...is this news?

[Anonymous Coward]

How is SQL "not good enough"?

Re:SQL has shortcomings...is this news?

[kfg]

How is SQL "not good enough"?

Look, I don't mean to be terse, really, but a Slashdot post is not the appropriate place to give a freshman tutorial on database theory, and the very fact that you ask your question means you need one.

In that light about all I can say is that SQL corrupts data, gives the logically wrong answers to querys and ties the logical model to the physical platform (think of Java and the JVM for an idea of why this might not be a good thing for a cross platform data manipulation system).

If this answer does not satisfy the solution is simple, read Chris Date's introductory book. He's already written it. I'm not going to duplicate it here. Until you do so you will probably remain ignorant of what a database even is, which, per above, makes it rather hard to explain what's wrong with a particular implimentation (just as it would be hard for me to explain a problem in orbital mechanics to you if you hadn't at least gone through an introductory physics text).

Of course, you can always go to Fabian Pascal's website ( which Date is a contributor to), but until you read Date's book you're likely to just think of Fabian as some sort of crank, out of ignorance. Until you take the trouble to learn what you need to know to evaluate his arguments you have no real basis for judging him in error.

Of course, that doesn't stop most people.

KFG

Re:SQL has shortcomings...is this news?

[a_ghostwheel]

Oracle went in slightly different direction - by providing more embedded functions. If you have Oracle 9+ docs, go read about analytic functions - they can save you in lots of situations - especially when dealing with self-joins.

Re:SQL has shortcomings...is this news?

[Anonymous Coward]

In that light about all I can say is that SQL corrupts data, gives the logically wrong answers to querys and ties the logical model to the physical platform

You can say what you like, but that doesn't make it true. Your unsupported rant quoted above is complete bollocks.

Re:SQL has shortcomings...is this news?

[Anonymous Coward]

So you essentially saying "If you disagree, you're not clever enough to agree?" Yeah heard that one before schmuck.

Re:SQL has shortcomings...is this news?

[kfg]

So you essentially saying "If you disagree, you're not clever enough to agree?"

No. Actually, what I said was that I think you're clever enough to study college level introductory mathematics on your own without someone to hold your hand.

I could, of course, be wrong.

KFG

Re:SQL has shortcomings...is this news?

[Anonymous Coward]

Too bad in this case it IS true.

Too bad ignorant programmers think they understand databases

The shortcomings of SQL

[mistersooreams]

I use SQL a lot and I agree that has failings. The clumsiness inherent in, say, nested joins is quite amazing when you consider how important databases are in modern industry. This is a consequence of the "near-English"ness that SQL strives for, but that property is also what causes people to adopt SQL in the first place. We'll probably look back at SQL in five years and laugh... but weren't people saying that five years ago?

Re:The shortcomings of SQL

[Unordained]

COBOL was(is) also english-like. People liked it. Then they liked it less. But enough had been written in COBOL that the damn thing just won't die, even if you can do the same thing faster in new languages. Learning a language is trouble, converting old programs is trouble, and you don't want to let people forget old languages for fear nobody will be around to maintain those old, unconverted programs...

SQL will be around for a while still, because it's "good enough" and "already known" and there are lots of "legacy apps using it" and the new stuff is immature. I have trouble convincing people that transactions are important, that joins really should be done in the server, that they shouldn't create attributes named "value1" and "value2" just because they currently only have two, ... convincing them that the language they use is (to be nice) underkill is just not going to happen.

Sure, I want a new language, I wouldn't mind learning it, heck, I wouldn't mind writing it. But the problem is convincing enough people that what they have isn't good enough.

Re:The shortcomings of SQL

[sw155kn1f3]

>I have trouble convincing people that transactions are important, that joins really should be done in the server, that they shouldn't create attributes named "value1" and "value2" just because they currently only have two

Geez, you just described completely idiotic DB developer who doesn't know how to do his stuff. In corporate world you don't need to convince much - just fire them.

Re:The shortcomings of SQL

[fm6]

Geez, you just described completely idiotic DB developer who doesn't know how to do his stuff.

Perhaps. But he's also described most of the self-taught developers who do most web applications.

In corporate world you don't need to convince much - just fire them.

That's assuming anybody above him on the food chain is any more clueful. I suspect a lot of corporate types don't even know what a join is, never mind the best way to do them.

Re:The shortcomings of SQL

[Clover_Kicker]

> In corporate world you don't need to convince much - just fire them.

So, how long have you been in the workforce, Skippy?

Re:The shortcomings of SQL

[Unordained]

In the case of the owner of my ISP, there's no firing I can do; besides, I like the guy. And yes, web devs as well ... all about simple selects and then post-processing everything in the script ... after all, it works, right?

In the hacker world, it's not about pretty/good solutions, it's about making a problem go away quickly so you can move on to the next problem. It's all about writing one-time scripts in whatever language you know, not learning another language unless you're bored or can't solve your problem with what you already know ... It's not the environment I'm in, but it's one I run into now and then. It's hard to give these people better tools because nothing is "good" to them, but everything's "good enough". Sure, a drill would be perfect for this, but I only have a hammer -- but I've made do a thousand times before, and I can do it again, I don't need a drill. These are people who do, in fact, get problems solved. They're terribly useful/valuable -- they would just be more valuable if you could give them tools with which they could do the job even faster; but you have to get past that initial annoyance and even having to look at a new tool -- a new tool to add to their already long list of unix commands, scripts in random directories, knowledge of several languages, etc. Firing such a person doesn't get the job done.

Re:The shortcomings of SQL

[Tablizer]

COBOL was(is) also english-like. People liked it. Then they liked it less. But enough had been written in COBOL that the damn thing just won't die...

Indeed. Check out Active COBOL Pages, AKA "COBOL Script".

Re:The shortcomings of SQL

[sw155kn1f3]

Well, if you'll ask me: there's absolutely no excuse to do such things. Be he super-fast hacker or not. Renaming columns, giving proper names to variables etc is just a matter of being self-organized and understanding that someone will maintain this project later.
Transactions... hm... If you'll begin to use such system "live", you cannot avoid it in any project of significant size.
So what you described in your post I replied to first time, is not hacking... It's just slapped together SQL code.
Cannot fire.. Well.. Educate him. Seems like he's being just ignorant and likes to do things in his own (completely wrong) way. It may cost your company large amount of money later.

Re:The shortcomings of SQL

[Khashishi]

SQL syntax is far too wordy that it really hurts usability. It's not _too_ painful when you are writing a software interface, but when you just want to make some quick queries at the prompt, it's godawful to have to type lines which wrap around the text window. Seriously, couldn't we change

select (id, desc, name) from mytable where flag = 1

to something like (a lot of options here)

mytable(id,desc,name):flag = 1

and

select * from a left join b using id where a.foo = 'bar'

to

(a*b|id):a.foo = 'bar'

and

insert into baz values (1,2,3)

to

+baz(1,2,3)

Re:The shortcomings of SQL

[Anonymous Coward]

We'll probably look back at SQL in five years and laugh...

I'm planning to look back and scream in terror, personally.

Re:The shortcomings of SQL

[cduffy]

In corporate world you don't need to convince much - just fire them.

Not always possible. Might be that the DBA is the CEO's old friend from college, or that there isn't cash to hire someone more competant, or that the Big Boss just plain isn't good at firing people.

Oops, did I just describe my own situation?

Of course not.

Re:The shortcomings of SQL

[Tony-A]

SQL syntax is far too wordy that it really hurts usability.

Tradeoffs. Using syntax as a replacement for wordiness.
Win some lose some. It's pretty rare that anything dominates.
Even if the main body stays the same, the boundaries, the edges, the places where bugs like to hide, are different. Not necessarily better or worse. Different.

An advantage of wordy languages like SQL and COBOL is that someone unfamiliar with the language and the application can look and easily get some idea as to what is going on. This is not just managers. It includes programmers who have to do something with it a few years later. This even includes the same programmer coming back to it a few years later.

The advantage of syntax is that in addition to being shorter, the edges tend to be better defined allowing for more complex expressions being feasible. The problem with using syntax is that it really has to be better defined to just break even. This isn't just defining some of it, you really need all of it, which means playing around with abstractions rather deeper and more fundamental than you would at first expect.

Re:The shortcomings of SQL

[Anonymous Custard]

In the hacker world, it's not about pretty/good solutions, it's about making a problem go away quickly so you can move on to the next problem.

For me, it's about solving things elegantly with algorithms you can be proud of. If you only write crappy "just do it" code, then that's all you'll be able to write. But if you work at it, you'll be able to pump out elegant solutions as fast or faster than you can pump out those quick 'n dirty fixes.

Plus, it'll save you all the time you're currently spending to maintain old crappy fixes, and your elegant code will have fewer growing pains as your project scales, and you'll get fewer phone calls and more beers from the guy who moves into your position when you get promoted.

dont think so

[Anubis350]

so to overcome the (not really all that many) shortcomings of sql we will all learn how to use something completely new. Yeah, adoption going to be quik and complete........

Re:dont think so

[julesh]

Well, they've been promoting this idea for 9 years now, so it's obviously not going to happen overnight.

Heh

[FiReaNGeL]

The name doesn't help... "Tutorial D" doesn't sound advanced at all...

Try proposing to your boss to replace your mySQL database with "Tutorial D" for... no good reasons? Will happen. Soon. Right?

Re:Heh

[phrenq]

I dunno... if your boss bought in to MySQL, then he may be the suggestible type.

Re:Heh

[Anonymous Coward]

After all, the prefix "my" sounds so professional. No, wait, it makes it sound like a toy. Which it is.

Re:Heh

[isny]

Sounds like something that the government would put together as part of the tax code. "Yeah, I filed my 1040 with a schedule C and Tutorial D."

Re:Heh

[Anonymous Coward]

Yeah and look at the names of those guys. They are soooo fake "Hugh Darwen" and "Chris Date". Just like that guy "Richard M Stalling"... come on! "Dick Stalling" Look! You can see the string!

Re:Heh

[Tablizer]

Yeah and look at the names of those guys. They are soooo fake "Hugh Darwen" and "Chris Date".

Perhaps they can call the language "Datewen" or "Dardate". Darwate? Hughris? Hughate? Hell, just name it after a fricken coffee blend.

Re:Heh

[Eminor]

Try proposing to your boss to replace your mySQL database with "Tutorial D"

There is no need to replace the database. The database is seperate from the language used to access it.

Re:Heh

[forkazoo]

Well, Furious D was used on the Simpsons, and Tenacious D is already taken as well. Tutorial D doesn't dound professional enough, but you don't want it to sound so complicated that it might interrupt things. I propose that the name be changed to Penis D. It's simple and uncomplicated sounding, but still has a trait of all leaders (who are, themselves, usual;ly also simple.)

I guess this is a troll, but it has a Simpson's reference, so I guess it's all good. Bah, WTF do I care, my Karma is capped.

You are all ugly smelly doo doo heads.

Re:Heh

[julesh]

When I saw Hugh Darwen speaking about this a few years back (guess it would have been '96 or thereabouts), he just called it 'D'. Since then somebody else has come along and taken that name for another language that has become semi-popular (a C replacement, in case you haven't seen it), so I guess they had to do something to change it...

Re:Heh

[Archibald Buttle]

Yeah, "Tutorial D" sounds a little bit too much like "Preparation H".

I gotta say ...

[Daniel Dvorkin]

"SQL is sloppy and unpredictable; Tutorial D is a correct relational database language."

sounds a lot like

"C is sloppy and unpredictable; Pascal is a correct programming language."

Re:I gotta say ...

[Bingo Foo]

Yeah, but Pascal is an education-oriented language. Just wait till Ada catches on.

Re:I gotta say ...

[Anonymous Coward]

"C is sloppy and unpredictable; Pascal is a correct programming language."

How about, "A bug in C is almost always a security hole. A bug in Pascal (or most other languages) is only sometimes a security hole."

Re:I gotta say ...

[Anonymous Coward]

Too bad one statement is based on relational theory, and the other is just a programming language opinion.

Too bad programmers decide to talk about DBMSs, when they only know programming.

Too bad ignorant people mod up posts that have no real content because they are too ignorant to understand the technical content.

Re:I gotta say ...

[jcr]

Umm... What's your point?

C is sloppy and unpredictable, mostly due to the fact that it has neither arrays nor strings, but fools people into believing that it does by presenting pointers as if they were arrays. That flaw alone is responsibile for nearly every buffer-overflow crasher or security exploit I've ever seen. Add dangling pointers and C's non-existent memory management, and you've probably accounted for well over 90% of the flaws in C programs.

If we wrote networking code in Pascal, Ada, FORTRAN, LISP, Smalltalk, Python, Perl, Java, Ruby, or even most variants of BASIC, we'd never see another buffer-overflow exploit again.

We use C primarily because we like it, but let's not pretend that it's a well-designed language, please.

As for SQL, I don't know anyone who actually likes it, we put up with it because that's what the DBMS's we need to use can understand. If Tutorial D can improve my quality of life when I have to deal with a DBMS, I'll give it a good hard look, at least.

-jcr

Re:I gotta say ...

[Anonymous Coward]

Wrong.

I love (not just like) liver, turnip greens, PERL and SQL.

Please do not speak for all of us.

Re:I gotta say ...

[Anonymous Coward]

You might love it, but apparantly you can't spell it.

What's the difference between "perl" and "Perl"?

One bit. Oh, you weren't talking ASCII? :-) Larry now uses "Perl" to
signify the language proper and "perl" the implementation of it, i.e.
the current interpreter. Hence Tom's quip that "Nothing but perl can
parse Perl." You may or may not choose to follow this usage. For
example, parallelism means "awk and perl" and "Python and Perl" look
OK, while "awk and Perl" and "Python and perl" do not. But never write
"PERL", because perl is not an acronym, apocryphal folklore and post-
facto expansions notwithstanding.

Re:I gotta say ...

[Daniel Dvorkin]

My point is, basically, this: C was designed by programmers who wanted to create a versatile, powerful language that would get the job done, while Pascal was designed by a theoretician who had a fanatically pure vision of what a programming language should be ... and as you say, people like C, and it shows, which is why the bulk of programming today is done in C or one of its descendants, while Pascal is essentially a footnote in computing history at this point.

SQL vs. Tutorial D (or any of the other "more correct," i.e. closer to the relational calculus, DB languages that have come and gone) strikes me as much the same sort of thing. FWIW, I do like SQL, and in much the same way as I like C -- I may grumble at its limitations and weirdnesses, but by and large it gives me an intuitive, effective way to get the job done.

Re:I gotta say ...

[CoughDropAddict]

It's not really accurate to say that C doesn't have strings or arrays. Just because they don't do what you want them to (namely bounds checking) doesn't mean they don't exist.

Furthermore, the fact that arrays, pointers, and strings are not bounds checked is not a "flaw," it's a design decision. C is a language that interjects very little between a program and the assembly language that program is compiled into. C doesn't do bounds checking? That's because no such thing exists in hardware. Repeat for garbage collection, preventing buffer overflows, and probably most of the other things you see lacking in C. Citing these as evidence that C is poorly designed is nonsense.

Is C more dangerous than the other languages you mention? Of course it is. But if it weren't for the existence of C and its design choice of being a "portable assembler," what would take its place as the tool that at least half the languages you mention are implemented in, with good portability and performance?

So, right back at you: what's your point?

Re:I gotta say ...

[jcr]

the fact that arrays, pointers, and strings are not bounds checked is not a "flaw," it's a design decision

Since when does something being a design decision preclude it being a flaw?

The mistake was not in failing to implement bounds checking in C's pseudo-arrays, but rather in providing the syntax that lets pointers masquerade as arrays or strings in the first place. If we always had to deal with them as pointers, we might be more mindful of the hazards that regularly bring the whole house of cards crashing down.

-jcr

Re:I gotta say ...

[CoughDropAddict]

So basically you are suggesting that C programs would be improved if...

a[b[i]]

were forced to be written as:

*(a + *(b + i))

because the latter might help people remember that arrays and strings in C are not bounds checked? That's going to help avoid dangerous calls to strcpy()? That's going to prevent dangling pointers and double-frees?

C is not designed for people who need hints to remind them not to do dangerous things.

Re:I gotta say ...

[jcr]

So basically you are suggesting that C programs would be improved if...

No, I suggested nothing of the kind. I offered no hope for the improvement of C programs.

-jcr

I was about to ask the same thing.

[Mustang Matt]

Seems like it can handle just about everything but maybe I'm not thinking outside the box. The biggest limitation is my lack of knowledge about how to do the things I want to do.

Re:I was about to ask the same thing.

[BoomerSooner]

The authors said when you do a query where your float field is null you dont get the instances of null. They say this is a problem but I say bullshit. It's the fault of whatever allowed null to be the value of that field instead of 0.00 or whatever. The data with null didn't magically appear there.

I agree sql could be better but the more complexity you add the more bullshit code I'll have to figure out what programmers were doing when I'm hired to fix their code. The company I currently work for has stored procedures that are over 4,000,000 lines (total) for report generation. Amazing how difficult it is to debug and work with. Especially since it was done in Transact SQL. I'm probably biased toward PL/SQL because I learned it first but it is so much easier to code in than Transact. (I have about 5-6 years of working in TSQL and about 7-8 in PL/SQL)

Re:I was about to ask the same thing.

[Anonymous Coward]

LMFAO!!! 4 Million!?! Are you kidding me? That is fucking ridiculous!! HAHAHAHHHAHA Of course it is difficult and hard to debug. That is just stupid!

Re:I was about to ask the same thing.

[Kell_pt]

I think you kinda misunderstood the problem.

The fact that there CAN be nulls is a showstopper when attempting to think in terms of relational algebra. It's so much simpler when you have a sound foundation of maths underneath - SQL has been so distorted over time that there is AMPLE room for improvement.

By not allowing nulls you are in fact working in a simpler world, as you do not have "undefined" operations in your query.

You are right though, in that it's the designers fault if NULLs get there, that's true. But just the fact that it exists forces a whole pletora of mechanisms and distorted concepts to deal with it. It's a poor man's attempt for an "incomplete-data" solution. A real incomplete-data solution must allow for the 3 boolean tests:


Known - true Known - false Unknown


Seeing as all comparisons in SQL are just boolean, they "trick" the third state into behaving within boolean parameters. And that's a source for trouble that could be avoided and made a hell of a lot simpler.

Re:I was about to ask the same thing.

[a_ghostwheel]

Pretty much the only reason NULLs so widely used is due to performance implications. Any nullable field can be easily normalized by moving data to separate table with NULL value being represented by absence of the record in the new table. But you will pay significant performance price.

Re:I was about to ask the same thing.

[jdray]

So, how do you represent an empty field if not by NULL? Let's say I'm filling out some web site's "profile" form because I want to... um... read a news story that someone linked to. I don't mind putting in my name and e-mail address, but I don't want to fill in my street address or social security number. So the record gets written to the database with my empty fields. How are they represented there?

Re:I was about to ask the same thing.

[superpulpsicle]

SQL has just been around too damn long and has been thru a cazillion transitions from different company influence.

You start off with the classic ANSI92 SQL which has alot less functions than some of the newer SQL. And SQL queries in M$ products are different enough compared to oracle SQL.

We need a new language to start from scratch that gets carried around like shell binaries across different unix systems. As long as you have that one binary, 99.99% of the syntax will be the same.

Re:I was about to ask the same thing.

[BlackHawk-666]

How do you represent the missing fields in a LEFT OUTER JOIN, you know, the ones that were implied to be NULL by their abscence. You need something to represent the abscence of data, and if you don't use NULL or other metadata you need to use a program assigned value...and that's even more hassle.

Re:I was about to ask the same thing.

[Mant]

I've work on databases professionally for 5 years, and studied them at unviersity as part of my CS degree.

The only people I ever met who cared about relational algebra where academics. It has never once been relevant to any actual database work.

Nulls are a bit of a pain sometimes, and a bit of a hack for dealing with unknown data, but in practice work pretty well. If you also want Nulls returned from a query you just add "or column_name is null" to it.

I'm sure SQL can be improved on, but when I read these things about the problems of Nulls in relational databases (almost always written by academics, rather than guys who write SQL) I feel I must be really missing something, becuase I never met anything that isn't solved by adding some is/is not null tests, and I've dealt with some very complex SQL.

Re:I was about to ask the same thing.

[Mant]

Big database vendors will never stick to a standard, adding extra features is what sells. It also makes life easier for programers. For example, Oracle's tree building queries are completely proprietary, and a godsend if that is what you want to do. I'd much rather have it available, than not have it becuase it isn't standard.

How many different databases does a developer work with in one job anyway? Most places probably just use one or two.

Re:I was about to ask the same thing.

[jafomatic]

Why not select the result of your join(s) into a new table whose schema will default those nulls to 0.00?

Am I missing a requirement? This seems like it shouldn't be an issue.

Re:I was about to ask the same thing.

[Kell_pt]

A couple valid points here and in the other few posts.

I'm not an academic, and I do write SQL a lot. Actually, I end up trying to write as little SQL as possible, by insulating it into small functions, but I'm sure many do the same for portability and plain old good form.

As for NULLs, the example of an empty street address is best represented by ''. Because the upper layer (the client used to fill in the values) won't make a distinction between '' and NULL.

In most cases, I simply ignore NULLs, and force propper default values - I'm sure most do the same.


The question we should be placing is "is there a better way"? Is there room for improvement? And please don't bring XQuery to the equation, that's something driven by need, not by carefully designing a solution.

At least fostering discussion and looking for alternatives, we might be able to achieve something. ;)

Re:I was about to ask the same thing.

[EastCoastSurfer]

0.00 != null

Zero is implying that there is a value there and that it is in fact the number zero. Null would imply that no value ever existed, zero or otherwise.

In all of my db designs I try to avoid nulls unless absolutely neccessary. A typical situation where nulls are unavoidable would be in an end date field( no end date as of yet). You also usually get nulls back when doing outer joins.

Remember though that null != 0 != ''. Null is the complete absence of a value.

Re:I was about to ask the same thing.

[BlackHawk-666]

Because a 0.0 and a NULL aren't the same thing, especially when it comes to financial data.

Re:I was about to ask the same thing.

[parnasus]

As for NULLs, the example of an empty street address is best represented by ''

This is a valid example of using the empty string, but what about the NULL boolean value? If a survey question asked, "Are you heterosexual? (T/F)", I'm sure there would be a number of people who feel it's none of their @%&$%($^#$! business! Entering a default value of false would skew the results horribly. (Note: This is an extreme example. You would phrase the question differently, of course.)

In most cases, I simply ignore NULLs, and force propper (sic) default values...

Sometimes, NOT getting an answer to a question is every bit as meaningful as when you do. If I'm maintaining a survey system, and statistical analysis of my survey says 61% of the surveys have an unanswered Question 1, I might want to rework that question to get more meaningful data.

Re:I was about to ask the same thing.

[16K Ram Pack]

I agree. That's the only way to do it, or use some arbitary end date like "31/12/9999", although I've sometimes worried about the programmers that will be required to rework all the code in 8000 years.

Re:I was about to ask the same thing.

[Lozzer]

What relational bods will tell you is that you are confusing your logical schema with your physical implementation of said schema. Logically (they say) you have a thing table, a things_that_have_started_but_not_finished table and a things_that_have_started_and_finished table.

Like most programmers, until the tools make that as easy as just sticking a null in, you probably won't care too much. Maybe the tool in the article helps in this direction, who can tell...

Re:I was about to ask the same thing.

[zaroastra]

I'll start by agreeing most times nulls will be a long term PITA waiting to happen.

Their worst property is absortion. Every operation you perform on a null (other than the Is null test :))) will return a null.
That can lead (and I already saw that happen several times) to nasty errors, which could be easily avoided. The most disturbing being, that in most places where a null was being used, there was actually better info to put there that null.
That said, and with the above disclaimer, that nulls can be dangerous, there are also situations where they represent a valid, logic solution to a problem.

Just on the top of my head:
You have a record with a field (payed in: date) something popular in invoices. WTF do you put there before the thing is payed? A stupid predefined date? that makes even less sense than a null.

Another example is when something is both optional (may or may not have a value) but each of the values needs to be unique. I have already encountered examples of this. Only way of solving it is with nulls.

adding it all up: yes, nulls are dangerous, mostly because their implementation with absortion propertys gives nasty coding errors, and because a lot of people use/allow nulls where there isnt really a need to, and a predefined value would be better. But sometimes it makes sense to have a "no value" value. Its like undef on programming.

I have NRTFA, so i dont know how well this will go on topic, but then again, I think i understand about nulls :)

Re:I was about to ask the same thing.

[Kell_pt]

You do have a point in that NULLs are an easy solution to some problems problem. But sometimes, people ask the wrong questions, and NULL comes out as an answer.

As with many other things in programming, NULLs are there for those who know how they should be used, but sometimes they also lure people to an "easy way out" when there are better methods.

Many a time when designing a database one thinks "hmm, there could be no value for this field", so you'll proceed to putting it to NULL, w/o bearing in mind that all the way up the abstraction layer you will have to support that NULL. Simple example s are empty strings and empty arrays (PgSQL).

As for the Date_Payed field, most people would say that you should place that on a side-table that would contain only those invoices that have been paid (and the Date_Payed field). I would probably consider using a PgSQL's INHERITS feature to turn "already paid invoices" into a subclass of the more general table "invoices", adding a field there. That makes nulls pointless, because if you select from the upper table you get all invoices, wether if you select from the child table you get only those that have been paid. Just an alternative of course, but that's what I think people should consider: better practices.

It's a bit like some practices in programming that although not "deadly" end up detracting from the whole quality, robustness and readability of a program. Compare that to reusing one-character variable names or monolithic functions of hundreds of lines - it works - but it is... bad form? Bad practice? :)

That's how I see NULLs - sometimes they are a necessity (because we lack better alternatives), and they are used too often (because people don't look for alternatives). That obviously someone else's problem - but when it turns out that the fact that "everybody" does it is creating such an inertia that alternate solutions aren't researched - then we have a problem. And I think that's what's happening to SQL (and NULLs) - stagnation.

Cheers.

Me and my buggywhips

[bigattichouse]

My buggywhip manufacturing plant uses good old fashioned chained flat files. No one in their right mind would EVER use some *toy* database structure, especially that whole relational database poppycock. balderdash!

Re:Me and my buggywhips

[Timesprout]

You and your newfangled buggy whips. Here in the real country we ride horses the way the good lord intended. When we need to store data we just tell old Uncle Jim and he remembers it. Uncle Jim even invented relational data, why he can tell you just about anything the famiily ever done right back to when great great great great great great grand pappy Bill ran away to marry his 14 year old cousin Mary Lou.

Re:Me and my buggywhips

[jewps]

That's not funny at all! My work uses flat files to store 60,000 clients' information, 300,000 transactions and 20,000 items. The best part is, I gotta intergrate some kind of a web front end to all this without completely killing the current system. Access would be a saviour but, no such luck. f**k!

PS, in all, those files are about 600mbs each. The transaction one is about 3 times as big.

(And seriously, I am not joking)

Re:Me and my buggywhips

[firefarter]

My parents are cousins, you insensitive clod!

Re:Me and my buggywhips

[bigattichouse]

I feel for you. I've done a few of those "hey we need a front end!" things myself... let me know if you need any help.

Re:Me and my buggywhips

[Mr.Sharpy]

We use some ancient flat files in production here where I work as well. They reside on an IBM iSeries, which luckily allows you to do DB2 SQL queries on them. If you just use select * from one of them you will get a single column rows that each contain a single record from the flat file. That's not very useful but you can use string functions to parse the file into tabular columns based on what you know about the file layout, and then use whatever sql you come up with to create a view. It's definitely not as fast as parsing each record in the program code, but it's a little more flexible and definitely more reuseable. The good thing about flat files is that their internal structure doesn't change very often because of programs that depend on it being static. Most typically, anything changed just gets appended onto the end of the file, so you rarely have to modify your view.

What kind of system are these files stored on? If not AS400/iSeries, I can think of a few other tricks to pull off similar functionality. If it is an iSeries, I can think of an even better solution.

sql sucks

[treat]

I really, really hate SQL. I have never found a way to generate SQL queries programmatically when there are many tables (and joins) involved. It quickly becomes quite hairy. But writing the queries explicitly causes massive code duplication.

I'd love some replacement for SQL that is suitable for dynamic query generation.

The only other solution I have is to query all the data and use only what I need, or to do a lot of little queries and join the data in my application.

Re:sql sucks

[MavEtJu]

If it becomes too complex because of too many tables, make some views which reduce the complexity for generic parts of queries.

Re:sql sucks

[Anonymous Coward]

There are so many systems for dynamically generating SQL. A common subclass is the typical object-relational (O-R) mapper. If you use something like Hibernate (discussed on Slashdot yesterday) for instance, you might not even have to write any SQL.

Embedding lots of complex SQL in a program is usually bad practice. If you can't figure out how to generate queries dynamically, and you haven't started using O-R mapper for your language/environment, you probably aren't qualified to write programs that access databases.

Re:sql sucks

[Anonymous Coward]

This is the sort of thing that metadata is for. The only time hard coded SQL should be used in code is for small and/or static uses and, of course, for reading the metadata itself. If properly designed, the code to actually perform queries should be generic enough to run and produce the correct output no matter what databases, fields, criteria (the where/having clauses), sort order or anything else the user asks for. In this way, SQL is more than suitable for dynamic query generation. I've written applications that can dynamically build massive queries including complex table joins and proper grouping and sorting all without the code that generates the actual report having anything hard coded into it about what the underlying data even looks like, let alone which fields the user wants to see or what selection criteria they've entered, etc.

If you're trying to manually join table results in your code, then you are definately doing something wrong. I can guarantee that just about every SQL database out there can do that task faster than you could try to do in code (and it'll save you a headache or three in the process). I'd recommend getting a good beginning SQL book then reading up all the documentation you can on the particular database you're using. For metadata, you can probably do a google search and fine a plethora of hints, tips, articles, books, theories and ideas of how metadata should be used, etc. For some other advanced SQL topics (mainly dealing with Data Warehouses) I'd highly suggest reading any books or articles you can by Ralph Kimball. I know I've learned a lot from reading his stuff.

Re:sql sucks

[fupeg]

As many others have pointed out, there are decent tools out there for generating SQL for you. I've used Hibernate quite a bit. It has saved from writing a ton of SQL, but I've still written a lot of HQL (Hibernate Query Language.) Hibernate also has a Criteria class for completely eschewing a query language in favor of purely programmatic queries. It is very limited though.

All that being said, the problem with Hibernate and any other SQL generator is very simple. They are not themselves querying and accessing data. They are specifying an intermediate language that is then in turn parsed and executed. If writing SQL is liking speaking to a translator, then using Hibernate is like speaking to a translator who then speaks to another translator, etc. What the Tutorial D guys are after is a native language that makes the translation(s) as transparent as possible.

Re:sql sucks

[l0b0]

Sure, and then you join three views based on a substring of a value which is not even a key, and all hell is loose. Then there is decrypting the view to extract exactly those substrings which you need, and break the purpose of the view because you want your statement to finish in less than 30 minutes, goddammit!</rant>

Ever heard of TSQL?

[Sean80]

Ever heard of TSQL? Neither would have I, if I hadn't been forced to read about it in college. It would seem that there has been a huge number of variants of SQL over the years that have tried to make it "better." The benefits just never seem to outweigh the cost of learning a new language.

Re:Ever heard of TSQL?

[l810c]

I've always used the term TSQL interchangeably with T-SQL or Transact-SQL. Microsoft's SQL implementation as opposed to PL/SQL which is Oracle's.

Re:Ever heard of TSQL?

[Michael Woodhams]

This isn't yet-another-varient of SQL - it is a from-the-ground-up replacement. Think Perl vs Python, not K&R C vs Borland C.

(Disclosure: I've programmed SQL, Perl and K&R C, and read Darwin and Date, but I have not programmed Python, Borland C or Tutorial D.)

Re:Ever heard of TSQL?

[ajs]

The cost is not in learning. People learn new languages every day. The problem is in compatibility.

Let's say that tomorrow MySQL adopts Tutorial D. Ok, so you are going to write code that talks to MySQL and you think about Tutorial D.... well, MySQL still talks SQL, and it turns out that so does everything else. Someday you may want to switch databases because suddenly DB2 is all the rage. If you use Tutorial D, you can't switch.

Any move away from SQL would have to be a broad industry move or would have to come from an effort that is so divorced from the current uses of databases that compatibility simply is not an issue.

Re:Ever heard of TSQL?

[radish]

Strictly speaking Transact SQL is Sybase's variant. Microsoft adopted it when they forked SQL Server from them.

Re:Ever heard of TSQL?

[Tablizer]

Someday you may want to switch databases because suddenly DB2 is all the rage. If you use Tutorial D, you can't switch.

Maybe somebody can make a Tutorial-D to SQL translator? I don't know the syntactical implications of such, though. Automatic language translation can be a daunting task.

Re:Ever heard of TSQL?

[MindStalker]

I'm of the understanding that Tutorial D allows things that are simply not possible in SQL, atleast not without a processing script.

Re:Ever heard of TSQL?

[KZigurs]

And yet we learn ;D

T-SQL, PL/SQL, postgres procedures and so on and so forth.

Who cares. If we will have to use it, we will learn it. Until then leave annoncements of yet another OSS evangelists (ok, this may not be the case, but since I doubt that I'll assume it is :D) to themselves.

Don't forget microsoft's X-Query

[joel2600]

For those of you that haven't been assimliated into the borg, microsoft's new version of SQL server accomodates for a new query language called XQuery which takes a lot of the best parts of XPath and XSLT and combines them and obviously the underlying framework is XML. This will cover a lot of the shortcomings over Transact SQL for those that are willing to adopt it, and honestly, it's really not that bad.

Re:Don't forget microsoft's X-Query

[XMyth]

As well as letting you write stored procedures in any language which compiles to .NET IL.

Re:Don't forget microsoft's X-Query

[Squeeself]

I've never used Microsoft's SQL server, but this sounds supiciously like this: http://www.w3c.org/XML/Query

Re:Don't forget microsoft's X-Query

[PhrostyMcByte]

XQuery is a standard, not one of Microsoft's creations.

Re:Don't forget microsoft's X-Query

[Anonymous Coward]

XQuery is a standard

For the love of $DEITY, not everything remotely associated with the W3C is a standard. In fact, virtually nothing they do is a standard. Tim Berners-Lee was extremely careful when setting up the W3C to emphasise that they weren't a standards body.

CSS? Not a standard. XML? Not a standard. XHTML? Not a standard. "Web standards"? Not standards. HTML? HTML has actually been standardised by a real standards body, and so, if you are using ISO-HTML, which is a stricter version of HTML 4.01 Strict, then yes, you are using a standard. But if you are using any other form of HTML, then no, you aren't using a standard.

XQuery? That's just a public specification. An incomplete, draft specification at that.

Re:Don't forget microsoft's X-Query

[SoupIsGoodFood_42]

Yeah, yeah. We all know that hardly anything the W3C do are true, bona fide, ISO standards, but they are still standards as far as most people are concerned. No need to be so pedantic.

Re:Don't forget microsoft's X-Query

[Talthane]

And actually, as far as e-government work in the UK is concerned, they are standards, because adherence to those "specifications" is mandatory for anything you do as a result of central government publications and things like the Disability Discrimination Act. The ISO doesn't necessarily have a monopoly on making something a standard.

Re:Don't forget microsoft's X-Query

[Anonymous Coward]

adherence to those "specifications" is mandatory for anything you do as a result of central government publications and things like the Disability Discrimination Act.

Just because most experts say that attempting to adhere to the WCAG would probably be enough to satisfy a judge on the "reasonable effort" stakes, it doesn't mean that the DDA mandates WCAG compliance.

I don't know why you felt the need to quote the word "specifications". They are specifications, any fool can see that.

The ISO doesn't necessarily have a monopoly on making something a standard.

No, there are other standards bodies of course. The W3C isn't one of them.

Worth ditching SQL?

[jack's wasted liver]

Any language is going to have quirks and inconsistencies between dialects.
SQL has worked so far but if Tutorial D really is better then bring it on.

I've gotten over SQL the "short commings" though

whats wrong with sql ?

[Dj-Zer0]

i dont think there is anything wrong with sql, if one to talk about optimization then thats simply something to do with the sql engine they use, if you think joins are sloppy then reconsider properly setting up proper indexes,

Re:whats wrong with sql ?

[Anonymous Coward]

Have you ever actually used a database? If so, please give me your explanation on properly set up proper indexes reducing the "sloppiness" of join syntax.

Not a replacement language...

[ryanmfw]

If you read the article, this isn't about replacing SQL, but more about testing new ideas and languages that could replace SQL. This is better than just saying, "We have a better language. Switch now or be assimilated.", and I'm glad someone's finally taking this approach. Unfortunately, the article only mentions one specific problem with SQL, but I'm sure there are others that these people might eventually solve.

Re:Not a replacement language...

[Timesprout]

I agree, it seems more experiment based than anything. Its a good idea as well. We all know SQL has shortcomings (hey nothing is perfect) and the OO database concept seems to have died a death. There are a lot of ORM tools and persistence frameworks coming into mainstream adoption now to avoid SQL in general day to day development. Several of these also have their own 'query language', so why not have another look at the root language.

Re:Not a replacement language...

[lucabrasi999]

If you read the article

Even if I couln't see your UID, that phrase above would give you away as being "new around here".

Re:Not a replacement language...

[ryanmfw]

It was a sort of backhanded comment on most people here. I'm not *that* new.

Cheers. :-)

Re:Not a replacement language...

[Tablizer]

Unfortunately, the article only mentions one specific problem with SQL

Here is an informal list from the c2.com wiki:

# Nesting makes it harder to factor out duplication as opposed to (optional) named units
# Group By inconsistent and/or confusing
# Implementations are inconsistent between vendors
# Standard lacks tree-based operations. What tree-based operations would be compatible with a relational model? Transitive closure? The point is that everyone comes up with various ways to hack up trees in SQL (e.g discussed in "SQL for Smarties", Joe Celko), but the hacks tend to be awkward and slow, so some people think that some model of trees, any model of trees [or graphs, or multigraphs], should be native to SQL to avoid the need for hacks. See http://docs.alphora.com/D4LGExplode.html for an implementation of the EXPLODE operator proposed by Chris Date for hierarchical queries. {Move to RelationalAndTrees}
# Inconsistent syntax (see below)
# Large, complex syntax instead of using combinations of simpler syntax units. Think of comparing the COBOL parse tree (or generators) to C's or Lisp's parse tree. Moved discussion below.
# Nulls are a flawed concept. Especially for strings.
# Defaults to generating duplicates, which can cause confusion and erroneous results.
# Encourages long, hard-to-digest run-on "sentences"
# Not a DBA-extendable language (see BusinessSystemTwelve)
# Joins could be simplified if using "standard" links (WhereAndAnd)
# The formal specification is too damned huge
# Many SQL-based systems don't allow user-defined views (DatabaseFlaws?)
# Column aliases are sometimes not recognized in outer queries
# SQL's syntax is prone to security problems: SqlStringsAndSecurity

Re:Not a replacement language...

[julesh]

There's a lot of information in Date & Darwen's paper entitled "The Third Manifesto". Unfortunately, I can't find a copy online, unless you happen to be an ACM member.

Mainly they had problems with a number of operations that were difficult to perform with SQL. I remember from a presentation that I saw Hugh Darwen giving back in '96 that they believed nulls to be evil and counter-intuitive.

Re:Not a replacement language...

[stephenbooth]

I found a presentation that Hugh Darwen gave to Warwick University in 2003 on how to avoid Nulls, entitled "How to handle missing information without using nulls". It seems the reccomendation is to split every table up into separate tables (minimum of one per column in the original table) each of two columns, a key column and a data column. For any column that could potentially contain a null create a further one (or more) tables of one column (the key) each (for simplicity I'll refer to these as flag tables). If the column has a value in it for a particular record in the original table then record that value along side the key value in the data table. If it has no value (was null) in the original table then record the key value in the flag table. Where a null could have different meanings create a flag table for each meaning. The example he gives is a salary where someone can have a known salary, unknown salary or be unsalaried. This would result is some pretty horrendous queries as you would have to take account that the values for one column are spread accross two or more tables and that, depending on the data, one insert statement would have to insert into multiple tables and make decisions as to which tables to insert into. Foriegn key enforcement would also be pretty scary as you'd be enforcing rules like "If a value exists in table1 then the key must also exist in (one and only one of tables 2a, 2b, or 2c) and (one and only one of tables 3a or 3b) and (one and only one of tables 4a, 4b, 4c, 4d...4zzzz)...".

An alternative suggestion he gives is to instead of leaving the column null for the row, insert a text value explaining the reason there is no value. So, in the above example of salries, there will either be a numeric value, 'Salary Unknown' or 'Unsalaried'. This implies that all of your columns would have to be text as whatever the type of your actual data you'd need to provide for the possibility that you'd want to store a text string in there.

Neither option strikes me as being sensible. If you felt that null values not being picked up by clauses like "x != 'y'" then you can always lobby for an ammendment to the SQL standard to define Null as a value that does not equal anything, i.e. "x != 'y'" returns true when x is Null. Since that would result in a major change in behaviour (and so break a lot of application code) I don't think it would get through, or is desirable.

Stephen

Re:Not a replacement language...

[julesh]

If you felt that null values not being picked up by clauses like "x != 'y'" then you can always lobby for an ammendment to the SQL standard to define Null as a value that does not equal anything, i.e. "x != 'y'" returns true when x is Null.

The SQL committee is unlikely to make such a major change at this stage. The time for that lobbying would have been before the language was standardised. As it stands at the moment, this would break many thousands of applications.

I have to admit not understanding his approach to avoiding nulls (despite the fact he gave that same talk back when I was at warwick in '96). If I wanted to achieve similar results to current SQL NULL systems without using NULL columns, I would define a table for the nullable values (e.g. "create table employee_salary ( id int primary key references employee, salary decimal(9,2) not null )") and then not add a row to it for any item where I would normally use null. It's a little inconvenient (in SQL; I would hope that in D there were language constructs to make dealing with it easier), but it works. I see no need for any more tables than this.

Re:Not a replacement language...

[stephenbooth]

As it stands at the moment, this would break many thousands of applications.

As I noted in my comment. I actually don't that that that would be a desirable change in many situations even without the application breakage. Null means no value, it works in practice.

I've been an Oracle DBA for 8 years now and have yet to come accross a production situation where nulls cause a problem. The nearest I've ever seen was in a CRM system for a person gender column where the question was raised about if we didn't know a person's gender was that because they hadn't been asked or because they had declined to tell us. It was rapidly pointed out that as we had to provide a lookup table on gender any way and would just store a code in the person table with a foriegn key to the look up table (for legislative/equal-ops reasons we had to identify more than just Male and Female as possible genders; at the time there were 7 legally recognised genders, I believe we're up to 11 now) we'd just make the field not null and create codes for "Not Known" and "Declined To Say".

Your idea of a separate table is similar to his initial reccomendation of separate tables, just without the supplementary tables to identify rows that would have had null values in them for that column.

Stephen

Re:Not a replacement language...

[julesh]

for legislative/equal-ops reasons we had to identify more than just Male and Female as possible genders; at the time there were 7 legally recognised genders, I believe we're up to 11 now

(FX:Head hits desk with a thud)

What? 11 genders? I give up...

Your idea of a separate table is similar to his initial reccomendation of separate tables, just without the supplementary tables to identify rows that would have had null values in them for that column.

Yeah. I just can't work out why his way of doing is any more useful than mine. Oh, well.

Re:Not a replacement language...

[stephenbooth]

On the number of genders, that includes things like "Don't Know" and "Declined To Say". Welcome to the world of public sector databases! IIRC the original list was:

• Don't Know
• Declined To Say
• Male
• Female
• Androgynous
• Female To Male Transexual (post op)
• Male To Female Transsexual (post op)

From what I've heard the others (that bring us up to 11) relate to people at the different stages of gender reassignment. I've heard talk of adding some more in to cover people who are biologically male but identify with the female gender but haven't begun (and may never begin) gender reassignment and people who are genetically female but identify with the male gender but haven't begun (and may never begin) gender reassignment. There has been some debate as to whether a person who is genetically male or female but identifes with the opposite gender but is sexually attracted to that opposite gender is the same gender as someone who is genetically male or female, identifies with the opposite gender but is attracted to their genetic gender. Personally I believe that they are as I think that gender identity is a separate issue to sexuality. Although, to be honest, I hadn't really given any of this much thought until I started working with systems where we had to take it into account.

There are similar issues around race and ethnicity, indeed, for sometime there was a debate as to whether race and ethnicity were the same or different things.

I think his solution is more down to a rabid hatred of nulls rather than having a rational, real world, issues.

Stephen

SQL is good for some things, but not for others

[Rand Huck]

One big thing I think a database could use is a hierarchy key instead of using parentid's as "foreign keys", is just one of the shortcomings. If I wanted to make a threaded thing for my forums, for example, I'd have to make a big PHP script just to sort it properly. I would have loved to have MySQL do it automatically. SQL has a very limited syntax, as well, and is inconsistent. "INSERT INTO table VALUES ('', '', '');" That's one of the only times you see the parenthasis used in that way. You don't see it here: "SELECT * FROM table WHERE id = '';" or "UPDATE table SET id = '';" I would like to have the ability to easily use my own functions just as you do with any language. It's especially important in database software.

Re:SQL is good for some things, but not for others

[cmowire]

Well, you could always try it in PostgreSQL with some of the more exotic indexed data types that are available... ;)

Re:SQL is good for some things, but not for others

[Rand Huck]

Right, but when you have a host, and can't afford a T1 connection and web server, you gotta take what they got. ;)

Re:SQL is good for some things, but not for others

[pizza_milkshake]

as far as hierarchies are concerned you can do all that within sql, although it can get a bit tricky; but then it will be no matter how you do it.

as far as the second part of your post is concerned, those are shortcomings with mysql. most modern mature databases give you all kinds of options for extending them (ie postgresql)

Re:SQL is good for some things, but not for others

[glwtta]

I think you are confusing "SQL the language" with "MySQL the broken RDBMS implementation", both Postgres and Oracle can easily do what you want with hierarchies; as for user defined functions, support for those is implicit in most database systems (usually in a variety of languages, both SQL dialects and procedural).

I am not sure what's incosistent about the syntax you mentioned, but maybe that's just me. Though I'd be interested to see in what ways it is "very limited" (especially if those aren't the limitations of a particular databas engine, or relational databases in general).

Re:SQL is good for some things, but not for others

[Rand Huck]

Since MySQL is usually the database of choice in Unix-based web servers, especially with PHP in the mix, I usually pick on it.

There are many get-arounds with MySQL (many to many relationships can be simulated with a table inbetween it) but it would be very helpful in database structure to have a way around that... maybe with a new field type that holds an array of id's instead of a foreign key in a two-column table.

I haven't had much experience with SQL databases outside of MySQL, I admit.

Re:SQL is good for some things, but not for others

[ticktockticktock]

For hierarchies, Modified Preorder Tree Traversal would work nicely. It is a method for storing hierarchical data in a database in which it is fast to retrieve any sub-trees of any part of the tree using a single query.

Re:SQL is good for some things, but not for others

[dozer]

You do realize, of course, that both Postgres and Oracle's (and MS SQL Server's) hierarchy mapping functions are not a part of "SQL the language" right? They're custom extensions, and fairly incompatible with each other.

Re:SQL is good for some things, but not for others

[Anonymous Coward]

MySQL is usually the database of choice in Unix-based web servers

Umm, you misspelled "morons who don't know any better"

All of *my* web apps use Postgres.

Re:SQL is good for some things, but not for others

[Anonymous Coward]

Is there a database that manages many-to-many joins without some sort of intermediate table? Wow...that would be cool to see!

In playing with ECO in Delphi 8.Net, it appears that even in an "OOP"-based method (which gets persisted to the database), you manage these with some sort of intermediate data storage container anyways. So I don't really see the point of this argument.

In fact, array fields are bad SQL design. Just look at Codd's writings on it, not Date's.

Re:SQL is good for some things, but not for others

[Anonymous Coward]

Yes, so what? use a Left-Right tree to do manage your hierarchies (look for "Celko tree" in google), if you need to make these queries fast. The hierarchical addons in Oracle, at least, can be kind of sllllowww.

One way I saw of doing this within PMM was a denormalized hierarcy table:

level1 level2 level3 level4 level5 ...where the value in the detail tables was listed in level5, and there were generated summary tables to use.

With the right indexing, grabbing sets on this was pretty fast, especially if you generated your summary table using GROUP BY...CUBE (it is SQL-92), like you would want to do for a data warehouse application

Besides, this is what apps like Hyperion, Business Objects, Teradata do anyways, they just keep it in memory.

Re:SQL is good for some things, but not for others

[glwtta]

Of course. Most programming languages implement much functionality as extensions, which are not part of the "core" language - doesn't really mean that SQL needs to be replaced. If you mean that MySQL isn't broken for not implementing them, that's true also; it's broken because it doesn't implement many features which are part of the SQL spec (or are so common as to be de facto standards).

Though I would argue that they are not in fact all that incompatible: the syntax is different but they accomplish the same thing, you should only see the difference in your persistence layer (which would have many other, but still mostly syntactical differences).

I am not a big fan of either one really, this was just brought up as an example by the OP.

Re:SQL is good for some things, but not for others

[glwtta]

Is there a database that manages many-to-many joins without some sort of intermediate table? Wow...that would be cool to see!

I think this would be cool only until you started using it in a real project.

If you really wanted to abstract the linking table out, the proper place to do it is probably in the app, not the database. Hibernate does this for you, but as they themselves point out, it's probably a bad idea, not the least reason for which is that in all but the most trivial apps, you eventually start to want to put extra data into that linking table.

Re:SQL is good for some things, but not for others

[I Like Pudding]

You're nuts. I've also written a threaded forum in PHP - here's the code that gets a thread and treeifies it:

// returns a threadstruct for an entire thread
function getThread($thread, $db) {
$tid = $thread["tid"];
$post_select = "SELECT * FROM posts WHERE tid='$tid' ORDER BY post_time ASC";
$post_rs = @pg_query($db, $post_select) or die("Error selecting posts for thread display.");

$threadStruct = array();
for ($j = 0; $post = @pg_fetch_array($post_rs, $j); $j++) {
if ($threadStruct[$post["par_pid"]] == null) {
$threadStruct[$post["par_pid"]] = array();
}
$threadStruct[$post["par_pid"]][] = $post;
}
@pg_free_result($post_rs);

$threadStruct["tid"] = $thread["tid"];
$threadStruct["fid"] = $thread["fid"];
$threadStruct["thread_sticky"] = $thread["thread_sticky"];

return $threadStruct;
}

If you need a DB to magick up a 6 line (and I was being verbose) for loop to sort a thread, you have bigger problems than SQL or any other DB technology would know how to solve. The Prevlayer people would tell you otherwise, of course, being that they're Computer Scientologists.

Re:SQL is good for some things, but not for others

[GooberToo]

Since MySQL is usually the database of choice in Unix-based web servers,

Sorry, needs some corrections. MySQL is the readily available DB and is rarely, if never, the database of choice for Unix-base web server, when they are being put together by people qualified to make such choices. Being readily available, hardly makes it the popular choice.

Re:SQL is good for some things, but not for others

[K-Man]

The SQL3 standard includes recursive queries. DB2 has had them for years.

Re:SQL is good for some things, but not for others

[Anonymous Coward]

Umm, you misspelled "morons who don't know any better"

All of *my* web apps use Postgres.

Like it or not, most hosting package offer MySQL, NOT Postgres.

You can't blame PHP developers for using MySQL, considering it has such a rich library of functions built in. I'd hardly call these people "morons who don't know any better" for choosing the more tightly integrated database.

Re:SQL is good for some things, but not for others

[Piquan]

both Postgres and Oracle can easily do what you want with hierarchies;

How would you do that in PostgreSQL? I have a project that needs a hierarchy lookup, but the only way I can think of is to write a recursive function.

Re:SQL is good for some things, but not for others

[Anonymous Coward]

Like it or not, most hosting package offer MySQL, NOT Postgres.


Yes... because MySQL is easier to use because it isn't as strict of an RDBMS as others are. People use it just like why water flows downhill... it's the path of least resistance. People who go to MySQL don't have to learn how to use a "real" RDBMS, heck, most don't even know the reasons why you would use a real RDBMS. Most just see a database as a "big pile of data" that they can query against and wouldn't be able to tell the difference between a real RDBMS and a driver on top of a text file.

You can't blame PHP developers for using MySQL, considering it has such a rich library of functions built in. I'd hardly call these people "morons who don't know any better" for choosing the more tightly integrated database.


What you're saying is that you can't blame people for being lazy and/or ignorant (not stupid, look up the definition) and going with the easiest (not necessarily the best) solution.

Re:SQL is good for some things, but not for others

[glwtta]

See 'tablefunc' in the 'contrib' directory of the postgres source. It's trivial to install and, among other things, provides the connectby() function.

I haven't had need to use this in a real application, but it does work quite well with a non-trivial test dataset.

Re:Grammar?

[e9th]

Hey! Having graduated colledge as a technical writer, much of whose work has been lost due to virii on my boxen, I think you are being unfair. I hope you loose your job, as I did.

Re:Grammar?

[LiquidCoooled]

UPDATE tblUsers SET Karma=Karma-1 WHERE UID=743982;

This could mean...

[Performaman]

that SQL is SOL.

Re:This could mean...

[srussell]

that SQL is SOL

This may be true, but in SQL, NULL != NULL.

This is what I love most about SQL:

create table foo (col1 int, col2 int);
insert into foo values (1,1);
insert into foo values (2,NULL);

and then:

select * from foo where col2 = NULL;
select * from foo where col2 != NULL;

And neither query returns anything. Isn't that great? "=" is equality, but only sometimes. That cracks me up.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Tutorial D...

[malakai]

It might be logically 'perfect' in terminal of relational math, but as a language, it's obtuse. The only book I could find on Bi Temporal database design ended up with all the tutorials written in this Utopian/Acadamian SQL language... holy crap was that annoying.

The reaons for wanting a change from SQL I agree with, but Tutorial D you'll never catch me using.

We need something to combine the power and speed of SQL query syntax with the nested filtering ability of XPath, yet doesn't require the entire DOM to be in memory to work.

Re:Grammar?

[Anonymous Coward]

You mean lose your job, don't you? And, I am sorry that you lost your job. A friend of mine, also a writer, has had an awful time keeping a writing gig for more than six months. Whenever a company needs or wants to "downsize," the writers seem to be the first to go. That is a shame, as is evidenced by most corporate writings these days.

HOLY CRAP!

[Anonymous Coward]

Those are some strange looking guys...

if u don't like SQL....

[stonebeat.org]

you can always use:

Relational Algebra / Domain Relational Calculus / and Tuple Relational Calculus

Re:if u don't like SQL....

[cmpalmer]

Aaargh, major college flashback...

I am ashamed to admit that I sat through my database classes in college getting my CS degree and thinking, "This is kind of interesting, but I'm not interested in writing software with databases. After all, I'm not going to work for a bank or an insurance company."

Well, no bank or insurance company, but I am writing applications that sit on top of many gigabytes of data. Of course, when I took those courses, people were still arguing over whether relational databases were a good idea and how long it would take before someone implement Codd's rules in a useable product.

An Alternative to Blindness?

[Anonymous Coward]

http://shit.slashdot.org/article.pl?sid=04/10/12/2 159209

Predicate Imputation

[Baldrson]

I've been watching the way relational calculus has been studiously ignored by both academia and industry for over 20 years now. Date and Darwen make some noise about recognizing that SQL's foundation is the application of predicate calculus to set theory but don't really do much of anything that would be recognizable to Pierce, Russell or Whitehead. Moreover, their "manifestos" about things like the object-relational paradigm don't do much to get to the heart of the issue from first principles.

Almost all the Object Oriented stuff people layer on predicates are, at best, an ad hoc, and poor, means of optimizing execution speed.

Let me explain.

One of the principles of polymorphism is that the same method has the same abstract meaning regardless of the kind of object. A predicate considered as a method subsumes such polymorphism by simply trying the various possible implementations of the method and committing to only those that succeed. If more than one succeeds then so be it -- that's the whole idea of relations as opposed to functions.

So, one reason you want all this OO stuff is the inheritance hierarchies keep you from going through all possible interpretations of a given method when the vast majority of them will fail for a given object.

Another, related, problem is that inheritance provides defaults without requiring a lot of thinking on the part of the computer. What I mean by "thinking" here is the sort of thing that is done by statistical imputation of missing data via algorithms like expectation maximization (EM) or multi-relational data mining via inductive logic programming.

So, the other reason you want all this OO stuff is so you can avoid mining a background database to provide reasonable defaults for various aspects of the data.

Some might be concerned that over-riding isn't absolute in such a system -- that you don't absolutely block, say, more generic methods when you have more specific ones present, and they're right. You don't block those methods -- you lower their priority by lowering the probability of those implementations via the statistical methods of imputation and/or induction. In a microthreading environment they most likely won't get any resources allocated to them before other higher priority implementations have succeeded. In a single threaded/depth-first environment they will be down the list of desired alternatives -- but they won't be discarded until something equivalent to a prolog cut operation kills them off.

However, and this is the important point, the work that has been expended toward OO facilities has vastly outstripped the effort that which has been put toward more parsimonious ways of optimizing predicate systems.

One of the better predicate calculus systems out there -- more promising due to its use of tabling to avoid infinite regress on head-recursive definitions and its optimization of queries using some fairly general theorems of predicate calculus -- is XSB. It has an interface to odbc and a direct interface to Oracle, but it would be better if it had something like a recoverable virtual memory substrate to support its roll-back semantics.

Re:Predicate Imputation

[Anonymous Coward]

http://www.codecomments.com/Prolog/message272868.h tml

At least he stole it from himself, and not somebody else ;).

Re:Predicate Imputation

[Hard_Code]

So, uh, does that mean you are saying object-relational frameworks like Hibernate are a good thing and that they are ahead of "more parsimonious ways of optimizing predicate systems", or what? Should we hop on the ORM/OODB bandwagon, or are you saying something different?

Re:Predicate Imputation

[LFS.Morpheus]

Let me explain.

You had me at "predicate calculus." ...Had me lost, that is.

Re:Predicate Imputation

[Tablizer]

I think what some of what he is saying is that polymorphism guarentees one and only one dispatching result (in general). However, set operations can return zero, one, or multiple results. The two philosophies conflict with each other, especially when trying to use compile-time dispatching determination.

Personally, I think sets are superior to trees (such as polymorphic "type trees"). But, they take a different mindset to learn to handle. Trees might be conceptually simpler, but they don't scale well in my observation. They get messy as they grow and things no longer fit into a clean type hierarchy. One either has to do massive refactoring (rearrangement), or live with duplication of leaves.

Thus, one can perhaps say that type trees are simpler up front, but sets win in the long run WRT simplicity and flexibility.

Note that multiple inheritance is a crude kind of "set-based polymorphism", but has limitations. One generally only uses it to inherit diverse traits to avoid method collisions. Thus, it is not a decent OO-based alternative or implementation of built-in general-purpose set-based dispatching.

In short, it is a fight between sets and trees as the dispatching mechanism. Relational leans toward sets and OO leans toward trees (or at least mutually-exclusive taxonomy lists).

Re:Predicate Imputation

[Anonymous Coward]

The elementary set operations will return as a result a set. Between any two sets any of the operations will result in a deterministic result. Morphisms defined upon sets map to elements in the codomain. Relations defined upon sets are subsets of the Cartesian product (that is ordered n-tuples) of the sets upon which it is defined. On sets, morphisms are unique n-ary relations.

A tree is an undirected, connected, acyclic graph. A graph is a set of vertices and a set of edges and thinking of them in terms of binary relations upon sets is trivial.

Sets cannot be "superior" to trees, because trees are sets.

In short, you have no fucking idea what you're talking about. Take some math classes sometime.

Re:Predicate Imputation

[Baldrson]

In more mathematical terms OO is typically functional whereas predicates are typically relational. The Object-Relational "impedance mismatch" is little more. With all the enegy poured into OO programming -- particularly collection classes -- you'd think people would wake up to the idea that functions are a special kind of relation (N:1 mapping, rather than N:M) and that therefore maybe you should just try to optimize relational programming so that you get collections for free and functional programming optimization will fall out naturally.

Re:Predicate Imputation

[Tablizer]

Sets cannot be "superior" to trees, because trees are sets.

Huh? Trees are a SUBSET of sets. All trees are sets, but not all sets are trees.

Re:Predicate Imputation

[Anonymous Coward]

Here is an easy one for you. What is a subset of a set? A set. Trees are sets. Trees are not all sets. The null set isn't all sets. The null set is a set. Sets are not "superior" to the null set. The null set is a set.

You're an idiot. I can tell from your language that you don't know what you're talking about. Go to college. Even if you are some middle age washed-up net kook, there's no reason for you to not at least learn about the mathematical terms you toss around so poorly. Take a few classes at a community college if you can't get into a degree program at a real university.

You often confuse sets with relations, and don't understand that morphisms on sets are relations.

You also don't know what polymorphism is.

Re:Predicate Imputation

[Tablizer]

Sets can be used to represent any tree, but the reverse is not the case. It takes only one example to show me wrong (lets ignore nullness for practicallity sake). That would be a lot more effective than simply insulting my background.

Note that I don't know if sets can preserve node ordering (depth) from a tree (I never tried). I am talking about it from a classification/taxonomy standpoint. If ordering is an issue, technically you may be correct, but the issue at hand is usually regarding classification/taxonomies/typing. (It may be possible to record levels using level sets. Messy, but technically possible.)

Present me with a small example of what you claim, and let's play with it. Deal?

Re:Predicate Imputation

[Tablizer]

Sets can be used to represent any tree, but the reverse is not the case.

I take that back. I am wrong on the second phrase. Trees CAN be used to represent any set. However, they are *unweildy* from a practical standpoint. One has to start duplicating information to compete.

Re:Predicate Imputation

[Anonymous Coward]

As a trivial (read elementary Computer Science) exercise implement finite sets in terms of trees. In turn implement trees in terms of these sets.
These are undergraduate computer science exercises and orthogonal to the mathematical definition of trees.

Note that I don't know if sets can preserve node ordering (depth) from a tree (I never tried). I am talking about it from a classification/taxonomy standpoint. If ordering is an issue, technically you may be correct, but the issue at hand is usually regarding classification/taxonomies/typing. (It may be possible to record levels using level sets. Messy, but technically possible.)

Let me translate this into mathematical terms:

<ignorant gibberish>

Present me with a small example of what you claim, and let's play with it.

It's correct by definition. Go to college.

Re:Predicate Imputation

[Tablizer]

I did go to college and learned about trees and sets. However, I have since replaced that knowledge with *practical knowledge*. Trees are harder to manage than sets from a human perspective. Whether my terminology is right or not is secondary.

Trees have real-world problems. I don't know how to put this into mathematical terms in most cases, however.

For discussions on some of the practical problems with trees, see:

http://www.c2.com/cgi/wiki?LimitsOfHierarchies

The best math and definitions in the world are of little use if they don't produce practical benefits. It is often case that there are "Turing Equivalency" battles among software engineers. It may be possible to represent anything with trees that general sets can, but that does not mean it is practical. Assembler language works, for example, but most find it harder to work with than higher-level languages. I don't know if there is any math proof of such.

The best I can describe it is that many simple edits on sets require more refactoring or more duplication in a tree that represents the same information. Maybe I am refactoring the tree the hard way and that there is a simpler approach that will save trees as the Ultimate Data Structure, but so far it is not documented in public that I know of. You are welcome to present your findings if you have them.

Sourceforge project page

[lub]

Is right here.

From the introduction: "Rel is intended to serve multiple purposes:
-It is a tool for learning, teaching, and exploring relational database concepts in general;
-It is a tool for discovering the power and expressiveness of a true relational language;
-It is a tool for learning Tutorial D;
-It is a relational database server;
-It may serve as a prototype or "working blueprint" for future implementations of Tutorial D or any "D" language (more on this later);
-It may serve as a platform for experimenting with and/or examining database engine internals. "

Why push water up hill?

[Loco3KGT]

Water is the only fluid that has the ability to climb surfaces due to its cohesiveness.

Re:Why push water up hill?

[fred fleenblat]

Incorrect.
Liquid helium also has the ability to climb, though not for the same reasons.

Re:Hurry please

[Anonymous Coward]

--I run fedora on an older slower machine than the one you have (all I have is antiques really). I did the text based install (it's easy, but they have a GUI as well), and only did the gnome desktop stuff, not kde, and also skipped loading open office, as it really needs more than minimal to function. Small harddrive,low RAM, oldCPU at 200, etc, dictated my choices but even then it's a LOT of stuff.... a kitchen sink install is 4 cds! I think I got mine down a little over 2 gigs and I know there's stuff on here I haven't even looked at yet, let alone use.

It works swell for me. If you want MP3 playback, (like in the xmms player)google for instructions, RH leave that out from some licensing issues, but it's a snap to fix it. Easiest is just get the RPM direct from xmms guys and trash the included one.

I also hear mandrake is easy to use, but I haven't tried it since 7 or 8, I forget now. It is KDE sorta centric and I have found I prefer gnome, but it's really a choclate/strawberry/vanilla issue, it's all good. I am not much of a distro fanatic, I just use fedora because it's a large and heaviloy maintained distro, so I know it will be worked on and will be here for the long haul.

really, most any of the new distros should work, although you could bump up the RAM a lot to make it work better. I've found RAM to be a lot more important than processor speed.

As an alternative you might want to tryout knoppix, it's a hoot to have a full fledged operating system with tons of stuff running from just the cd and a temp ramdisk. Too cool really.

zogger

Re:Grammar?

[Anonymous Coward]

Hey! Having graduated colledge as a technical writer, much of whose work has been lost due to virii on my boxen, I think you are being unfair. I hope you loose your job, as I did.

It's a shame such wit was lost on the illiterate moderators. I laughed hard at this one!

Re:Grammar?

[808140]

Dude, do you hear that whooshing sound? It's the the sound of the GPs joke going right over your little head.

Re:Grammar?

[Anonymous Coward]

Perhaps you lost your job because, as a technical writer, your spelling is inadequate and your grammar is pure spaghetti. I believe you intended to say "lose your job," college has no d, you don't graduate college but graduate from college, and the sentence structure is extremely confusing.

Link to the actual project

[The Pim]

Geesh, links to a press release and trade rag article, and not even the name of the project itself? Go read some real information about Rel, look at the examples, download the source, have fun.

Mmmm....

[iminplaya]

So now we have Ogg Voorhis?

Re:Grammar?

[Anonymous Coward]

Perhaps you have no sense of humor.

Benefits of alternative languages

[jesterzog]

For those interested, the paper describing this language (linked to from the article) is available here. There's a link to the grammar of the language at the end of that paper.

I use SQL quite a lot. It's certainly great for a lot of things, but it does have some limitations here and there. For instance, trying to deal with things like hierarchical structures, or joining on having identical/similar children, is a nightmare in SQL. Even if the query doesn't need to be efficient to run, it can still be extremely complicated to write and test. SQL simply wasn't designed or intended to deal with those sorts of structures.

Unfortunately, short of using external code outside the database, it's so often a choice between using SQL or nothing else for writing a query in a particular database rather than an option between SQL and another language. In some ways it's like being forced to write every program in C or every program in Java or every program in Lisp, where realistically one or another might be better suited to a particular task.

I suppose one of the reasons for only supporting SQL is that a predictible query language makes it easier to arrange data structures so they can be queried most efficiently. Still, it'd be nice to see an alternative front-end language or two supported in one or more of the major databases. Not every query needs to be ultra-efficient, and there have been many times where I would've liked to trade an efficient query execution for a language where what I wanted was more writeable.

Re:Benefits of alternative languages

[daviddennis]

I glanced through the paper, and to be honest the new language looks like something only a mathematician could love.

I think I prefer the old SQL to having to learn about TUPLEs and typing all my keywords in UPPER CASE WHICH I THINK MAKES IT TOUGH ON THE EYES.

I still want to say

insert into database set x = 100, y = 'foo'

instead of

TUPLE (100, 'foo'), (200, 'bar')

That's almost unreadable! Of course maybe the former is just mySQL syntax, but it's still enormously easier to read, write and debug than what they seem to be proposing.

Give me SQL syntax, however awkward, before math theory syntax any day!

D

Re:Benefits of alternative languages

[jesterzog]

Well I wasn't necessarily referring to this particular language -- I've only glanced over the paper myself. Besides, just because another language is supported probably wouldn't mean you'd have to give up SQL. It'd increase your options, though.

In general, I think it's great that researchers are constantly looking for new and better ways to do things in database languages rather than just listening to those who say SQL is the only language that's useful and needed for databases... and that is an opinion that seems to do the rounds quite a lot, although I don't think it's very justified.

If a few alternatives could be implemented in the major databases some time just to increase the choices available for developers who use databases, I think it'd work out better overall.

Re:Benefits of alternative languages

[daviddennis]

I agree that an improved language would be interesting.

It just doesn't look like the one in the article is it.

You could, of course, solve the null problem by simply defining new operators that were the same as regular ones except that NULLs were treated as zeroes or empty strings. Or you could define all your fields as NOT NULL, which would have the effect of defaulting them to zeroes or empty strings. You don't need a whole new language to do that.

Now, I would love to have an easier to learn JOIN syntax. Took forever for me to figure it out. So I don't think the current SQL syntax is without flaws. It's certainly worth working on improving it. But let's not throw the baby out with the bathwater. The language should still use English terms that are reasonably comprehensible to the person learning the language. Keywords like SELECT, FROM, WITH, and so on make the language easier to learn than if mathematical notation is substituted.

D

Re:Benefits of alternative languages

[kpharmer]

BTW, SQL does support hierarchies - Oracle & DB2 in particular are very good at it.

Also, keep in mind that you can easily wrap low-level SQL operations (insert,update,delete,select - each for one row) with another language. That would allow you to use your own database language.

Of course, the performance wouldn't be the best, and certain features would be difficult to implement. But, it could be metadata-driven, and would allow the option of using either language.

Re:Benefits of alternative languages

[Per Wigren]

For instance, trying to deal with things like hierarchical structures, or joining on having identical/similar children, is a nightmare in SQL.

Oracle (and maybe some other?) databases implement a SQL extension (the CONNECT BY syntax) to handle this. It's also available for PostgreSQL using the hier-patch. Hopefully it will make it into the ANSI standard sometime because it's really useful!

Say you have a table with the columns "id" and "parent" (which is a foreign key to the id-column). Then you can get a tree by using:

SELECT * FROM $table CONNECT BY PRIOR id=parent START WITH $id;

It will also append an extra column to each row called "_level_" containing the "depth" of the row.

Re:Benefits of alternative languages

[bedessen]

Oh come on, did you even look at the documentation? Your example isn't even close to correct syntax. It's not as bad as you're trying to make it out to be.

inserts:
INSERT r TUPLE {x 1, y 3};
r += TUPLE {x 1, y 3};

deletes:
DELETE r WHERE x = 2;
r -= WHERE x = 2;
r -= [ x = 2 ];

updates:
UPDATE r WHERE x = 2 SET ( y := 3, z := 4);
r @= WHERE x = 2 SET ( y := 3, z := 4 );
r @= [ x = 2 ] SET ( y := 3, z := 4 );
r @= [ x = 2 ] ( y := 3, z := 4 );

http://dbappbuilder.sourceforge.net/Rel.html

NO!!!

[jclinux]

I've been learning Tutorial D in my database class, and it is so much more confusing than SQL. Maybe its cause I'm from a C and SQL background, but D really sucks IMO. SQL might not be terribly accurate from a mathematical sense, but it is much more practical at least in the smaller projects I've had experience with.

Re:NO!!!

[Anonymous Coward]

If you're at all familiar with SQL, then Tutorial D should be quite obvious.

Pushing water uphill

[sreeram]

Not that hard.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Re:The shortcomings of SQL - Postgres

[paul.schulz]

Prior to PostgreSQL, the Postgres database
was based on another query language other than
SQL.

One of the main reasons why
PostgreSQL took off after this was that
it changed over to SQL in response to
community/industry request and requirements
(and openness to community contributions),
bringing it in line with the industry
standard.

I suspect that the next evolutionary step will
be to allow multiple query languages to be used
on the same DB engine.

Re:Grammar?

[e9th]

How the heck did you get UID==nick?

Re:Hurry please

[Anonymous Coward]

Thank you so much!!

Comment removed

[account_deleted]

Comment removed based on user account deletion

It's not about optimization...

[Ayanami Rei]

this proposed language is like a superset of sql. It also sort mixes itself with PL/SQL-like constructs as well. I think it's rather novel and it could make your optimization tasks easier if you had access to something like this... less trying to think in terms of the language, more in terms of how you want to acutally manipulate the data.

This is _not_ a query builder. It's going back, looking at what relational algebra is, seeing what people do with SQL, and then making sure the language has all the idioms required to be "complete" and not arbitrary.

Re:It's not about optimization...

[Anonymous Coward]

SQL is a set oriented language, and thats something most people just don't get. Of all the languages I know, APL is the most similiar (once you ignore the funny symbols.)

PL/SQL violates most of the pricniples SQL was built on and is for people that really don't get it. Anything that attempts to add PL/SQL like constructs to the language is really to make it easier to right poor code.

[You should mod this informitive flamebait]

Re:It's not about optimization...

[smittyoneeach]

Trouble is, problems have their set-theoretical aspects, their procedural aspects, their object-oriented aspects, and their graph aspects.
Consider the pleasure needing to do something with a hierarchy, and store the nodes and edges in tables.
Either the INSERT or the SELECT is going to suck, or, if you've got a link to something that can map between tables and a graph very simply, please share.

Re:It's not about optimization...

[Anonymous Coward]

Are you retarded? Mathematically speaking, hierarchies (graph theory) are defined as SETS and FUNCTIONS.

The relational model is merely SET THEORY and Mathematical RELATIONS (aka functions)

Simply because SQL has some implementation bugs doesn't mean that Relational Model cannot do what you just suggested.

Re:It's not about optimization...

[Tablizer]

Consider the pleasure needing to do something with a hierarchy, and store the nodes and edges in tables. Either the INSERT or the SELECT is going to suck,

Relational algebra does not rule out tree-oriented operations that I know of. Oracle has proprietary tree-oriented extensions, I would note.

It is easy to represent trees in tables. Simply have a ParentID column that references the ID number of the parent record (zero or null for top-most). I would recommend making a generated key if you have a compound key, however.

Re:It's not about optimization...

[pdjohe]

Here's a bit of the language enhancements from D-Tutorial. (Note also, that D-Tutorial is the initial building/learning form of future "D" languages.) I'll admit it does simplify things...

INSERT: A shorthand syntax is available for INSERT. The following:

INSERT INTO r RELATION {TUPLE {x 1, y 3}};

May also be specified as:

INSERT r TUPLE {x 1, y 3};

Or:

r += TUPLE {x 1, y 3};

The lattermost syntax is intended to be vaguely evocative of the "increment by 'n' and assign" operator found in C, C++, Java, and other languages. DELETE: A shorthand syntax is available for DELETE. The following:

DELETE r;

May also be specified as:

r -= ALL;

The following:

DELETE r WHERE x = 2;

May also be specified as:

r -= WHERE x = 2;

Or:

r -= [ x = 2 ];

The use of "-=" is intended to be vaguely evocative of the "decrement by 'n' and assign" operator found in various popular programming languages. UPDATE: A shorthand syntax is available for UPDATE. The following:

UPDATE r WHERE x = 2 SET ( y := 3, z := 4);

May also be specified as:

r @= WHERE x = 2 SET ( y := 3, z := 4 );

Or:

r @= [ x = 2 ] SET ( y := 3, z := 4 );

Or, because SET is optional:

r @= [ x = 2 ] ( y := 3, z := 4 );

Comments: Comments are specified using conventional C++ and Java syntax. Eg:

// This is a comment line

/* This is a multi-line
comment block */

More info and examples here.

Re:It's not about optimization...

[smittyoneeach]

Well, that was less than helpful.
My point is that, in going to/from a graph to a set of tables, either the packing or the unpacking is going to be slow, not that the model cannot support the operation.
And I've got a couple of Joe Celko books. I've read and understood them. Whether or not this constitutes retardation is left as an exercise for the reader.
<grabs for bag of lead paint chips>

Re:It's not about optimization...

[ynohoo]

Oh yeah, having half a dozen different ways of doing the same always makes it easier... not!

although many language designers seem to think so. Like replacing "if..else" constructs with "case" statements - what a massive productivity gain that was...

Re:It's not about optimization...

[reanjr]

I could be wrong, but wasn't that originally for compiler optimization? The compiler would know ahead of time that it was comparing a single value multiple times and could compile accordingly. I think I heard this somewhere on the internet. And if it's on the internet, it must be true.

Re:It's not about optimization...

[Kell_pt]

Erm... excuse me, but switch/case DOES make it easier to read, for the following reasons:
- it implies that all tests are on the same variable
- you can do it on a non-pure function, because it will only evaluate once
- it "looks" better than a chain of nested IFs and reads better.

I could go on... apparently you've missed the point, let me try and put it this way:
a) Programming is (mainly) a means for expressing logic and behaviour. b) Language puts barriers on what you can express. c) Therefore, a limited language, limits programming.

If you doubt this, think of human languages. Now think of "snow". Now ponder why is it that esquimoes have about 20 different terms for it. They can capture and express nuances in their speech that for us would seem irrelevant. If you think none of this matters, then the metaphore was lost, and maybe you should worry yourself with issues other than these. :)

The bottomline is, a more expressive language allows you to do more, better, and in a more detailed manner with little extra work. After you learn your 10th language, you reach a point where syntax doesn't make a shed of a difference in your understanding of any code. At that point, only the logic is at question, not the medium. But if the medium is restricted, then the logic is also restricted. Allowing for people to use subtle nuances of the syntax also allows different people to express like they prefer, thus broadening the use for a language. For a programmer worth his salt, syntax becomes nearly irrelevant when reading - although it has impact for matter of style and brevity.

Funny too how most people only know imperative languages and completly overlook the functional (Haskell, LISP, derivatives) and logical (Prolog,eCLiPse) implementations. SQL is fully imperative, if you ever tried querying a database using a Prolog layer you'd FEEL the difference, and the need for something that can convey meaning and information out of data (oh yes, they're different things). But I'm starting to ramble, so I'll just shut up and hit the bed. :)

Re:It's not about optimization...

[ynohoo]

That's funny, I thought new programming languages and paradigms evolved to keep CS professors under tenure. We're talking to binary gates, not eskimos.

Re:It's not about optimization...

[Kell_pt]

It's possible that you thought wrong. :) You should be aware that most commercial languages do have an academic background. Of course there is a lot of academia that ends in dead ends, but afterall, that's what researching is.

And we're most definitly NOT talking to binary gates - abstraction is one of the 1st things thought in good programming courses, and while it is crucial to have a good understanding of the underlying processes, it is a key factor in good software development.

That's like saying that "to speak" is simply to get air to pass through the throat. Yes, that's the process allright - but "to speak" is also to convey meaning, to express thoughts, etc.

Of course, all this matters little when talking to yetis, or any of their less-furry and more stone-like brethen.

SQL.. heirarchal queries

[Anonymous Coward]

currently walking dags for nodes requires database specific implementations. assuming oracle didnt patent the damn thing (and i'm guessing that they would want it standardized and more widely used since they have an efficient implementation), it's clearly a shortcoming of sql.

Null is not Zero in SQL

[Magickcat]

The article criticises SQL but the author has little familiarity with SQL for example:

"but the syntax is often inconsistent and unless you use one of the many vendor-specific supersets of SQL it can be tricky to express complex series of operations in a concise manner."

But in fact, SQL is so popular because complex expressions need little changing from specific vendor offerings. If people choose to program using the subsets, then well and good, but the ANSI standard is generally thought to be sufficient. This is like arguing for the abolishment of HTML and XHTML because Microsoft make a flawed browser - hopefully the database language is better than the reasoning here.

It then goes on to say "The idea is that there should be no arbitrary restrictions on the syntax of the query language, but at a lower level the database shouldn't run up against idiotic limitations. The limitation in existing implementations that generates the most comment from the various parties in the debate is the problem with "null" values in relational databases. Put simply, a database field has a type (50 characters, for instance, or a floating point number to two decimal places, or an 8-bit integer), but when you don't fill the field in (i.e. it's "null") it loses all its meaning. Even the ANSI standards state that if a field is null it's said not to exist - so if you ask a database for "all entries where field X is not equal to 47" it won't return any of those where field X is null because instead of saying "Null doesn't equal 47", the value "null" is deemed not to be comparable with any non-null field."

Well, for starters, null is not numeric zero, null is the absence of any data whatsoever, and every SQL doc in the world tells you to not mistake it for zero. Any arithmetic expression containing a null always evaluates to null. For example, null added to 7 is null. All operators (except concatenation) return null when given a null operand. That's exactly why it's the ANSI standard.

If you want to find "all entries where field X is not equal to 47" then pass your attribute a value like "0".

SQL is neither clunky nor obsolete. Tutorial D may actually be a better database modelling method, but the article's criticisms aren't sufficient to exault Tutorial D whatsoever. The "Project D" syntax and model may possibly be better, but these criticisms aren't a convincing reason to scrap SQL.

Re:Null is not Zero in SQL

[johno]

OK, but the author didn't ever say that null was equal to numeric zero. I don't see what your point is.

Re:Null is not Zero in SQL

[Anonymous Coward]

I would disagree....

whilst you can write systems using ansi standard SQL - as soon as you put it into a production environment you will want to use non standard extensions - for performance etc.
in addition note that each database supports a slightly different subset of the ansi standard... so the set thats available on all main databases is smaller again.

Look at the hoops any big software vendor that supports the main databases has to go through.
Case in point... Oracle, DB2, Sybase and Informix handle Null differently - so for example PeopleSoft resolves this by not allowing Null values.

Mind you the problem here isnt SQL or the standard per se' - its that the vendors dont support it fully and extend it considerably .

Re:Null is not Zero in SQL

[Magickcat]

Null is the absence of any data, but the author is criticising it because he thinks it should behave the way numeric zero does in a variable. If he wants his attribute to act like "0", then it should in fact be "0".

SQL by design distinguishes null from "0" - it's not a limitation or a flaw in the design, it's quite deliberate, but it's a distinction that people who are not familiar with SQL often get confused about.

Re:Null is not Zero in SQL

[GooberToo]

SQL by design distinguishes null from "0" - it's not a limitation or a flaw in the design, it's quite deliberate, but it's a distinction that people who are not familiar with SQL often get confused about.

I completely agree with your statement. This form of confusion is typical of someone that isn't familair with SQL. And yet, oddly, the very people which seem to be commenting at this basic level, appear to be casting out SQL and marching in something better. If they fail to understand core principals of SQL, how can be improve on it? I guess by strictly adhering to Date and Darwen, but I'm forced to agree, the quoted passages won't win people over.

Re:Null is not Zero in SQL

[Anonymous Coward]

Date's probably sick of arguing about it. Read his "Principles of Database Systems" regarding how SQL messed up NULL for a convincing argument.

Re:Null is not Zero in SQL

[SoupIsGoodFood_42]

But I think their point is that implying null == 47 is a bit silly. Not that null != 0 is silly.

Re:Null is not Zero in SQL

[Eivind Eklund]

Why oh why can't I moderate "Wrong", so people see that and stop up-moderating.

Originally criticized quote: "but the syntax is often inconsistent and unless you use one of the many vendor-specific supersets of SQL it can be tricky to express complex series of operations in a concise manner."

Notice the parts that are bold here.

There are a bunch of cases that are tricky to express well in SQL, and where a consice representation requires vendor-specific SQL. The two examples that come to mind for me are hirarchies and string matching; hierarchies are quite icky to do without vendor extensions (there are a number of tricks that makes this sort of work, but it is still not possible to do consisely and clearly), and case insensitve string matching (not to talk of complex string matching.)

And that's not even considering varying performance profiles for queries - even assuming the SQL is interpreted the same for different database engines (a false assumption for many cases), the performance profiles vary a lot, and this has to be tuned at the query level - while it should have been tunable by meta-operations on the queries.

As for the talk in the parent criticizing the quoted article's understanding of NULL: There is nothing in either the quoted article or the critique indicating a misunderstanding of NULL. However, it is quite clear that the parent misunderstand the quoted article and then attacks his misunderstanding.

The quoted article discuss the logical inconsistencies in the SQL NULL demonstrated by the two statements "NOT field = 47" and "field 47" resulting in a different result set in the precense of NULL.

As for "SQL is neither clunky nor obsolete", I request that the poster actually spend a month or two trying to find out how SQL is clunky, and what damage this clunkiness does in practice. I've so far attempted to analyse the problem for a little over a year, and I'm still People that have not attempted to program in different variants of the relational model than SQL should IMO have enough pride to refuse to have an opinion.

Eivind.

Re:Null is not Zero in SQL

[kk2796]

The fundamental difference (between Date's no-null approach and SQL's null approach) is this: Say you have an entity described by a key value and a single attribute. In some cases, it's possible that instances of the entity can be entered into the database, but for some reason, the attribute cannot be specified.

The SQL approach: Use a single relation (table). Store all entitities in this table, but plop a NULL into the attribute field for any entities with an un-specified value. Leave no way for the end-user to know *why* the attribute is unspecified.

Date's approach: Use 2 or more relations (tables). The first relation will contain the key value and attributes for all entitities where the attribute was specified. The second relation will simply contain key values where no attribute was specified, perhaps because the attribute was unknown. The third relation will contain key values where no attribute was specified, perhaps because the key value was known to simply not *have* this attribute. (Alternatively, use just one relation in addition to the first, with a key value and a column entitle "Reason_attribute_is_missing" or some such)

The key point made by the Dates camp is that the latter approach is more descriptive and, more importantly, it is actually fully relational in the sense that it does not clutter the database with meaningless NULL's. Supposedly, the former approach is used in SQL because SQL does not have the language features needed to implement the pure relational approach in an elegant fashion - if at all (for instance, in SQL, how would one use the second approach, but still have the key value - which si spil over multiple tables - be a foreign key to some other relation?)

Tutorial D/Rel attempts to address these (and other) concerns by going back to the drawing board, and designing a language capable of implementing a pure relational database.

Re:Null is not Zero in SQL

[Anonymous Coward]

I wish I could mod some people "misinformed zealot".

SQL joins.

[FooAtWFU]

Yes. Investigate the wonderful world of SQL joins. Generally, you'll find yourself doing something like, oh, I don't know:

select client.name, client.id, product.id, product.name, product.price from client_table client, product_table product where client.id = product.client_id and client.id = ? and product.discontinued = 0 order by product.price

Assuming you substitute something for ?, that will effectively join the two tables into one, and give you a list for all products from a certain client (given by ID) which have not been discontinued, and order these results by price.

Re:SQL joins.

[dcam]

Or in the more recent join sytax:

SELECT client.name, client.id, product.id, product.name, product.price
FROM client_table client
JOIN product_table product ON
client.id = product.client_id
WHERE client.id = ? and product.discontinued = 0
ORDER BY product.price

Re:SQL joins.

[Anonymous Coward]

Of course it will. You obviously don't know alot about using databases.

The idea you described in your initial post of variable size rows and storing 1-n data items of the same type in a single row is considered horrible practice. The very first normal form forbids it (there are 4 normal forms where each higher form is considered 'better' design).

You always want to break this into multiple tables as the other reply suggests. This is basic relational database design, and if you are doing any database work and don't know about this already I strongly suggest you read up!

I think he's trying to say:

[Ayanami Rei]

I think he's trying to say that joins shouldn't be this hard to describe in the language.
Compare the symbolic forms:
Example, theta join
And the implementation in SQL:
SQL join example

Specifically in Tutorial D (and hence Rel) you would do this:

T1 = JOIN(EMP, DEPT)

And subsequently do shit with T1. That's it.

Re:I think he's trying to say:

[Gilk180]

That may be, but I haven found few operations easier to express in relational algebra than in SQL.

for example:

I want the name field from a if it's id is in b.

In relational algebra,
PROJECT[A.name](THETA_JOIN[A.id=B.id](A, B))
or
p[A.name](A |X|[A.id=B.id] B)
Sorry, ascii sucks.

In SQL
SELECT DISTINCT A.name FROM A,B WHERE A.id=B.id;

I find the SQL version to be more readable, etc. The same functionality is provided by both and is easily transferable.

cartesian production becomes SELECT * FROM A,B

natural join becomes (A NATURAL JOIN B)

theta join becomes SELECT DISTINCT * FROM A,B WHERE

selection becomes SELECT * FROM A,B WHERE ...

projection becomes SELECT columnA, columnB FROM A,B

With nested queries, everything is easily translatable from relational algebra to SQL (Technically, all SELECTS should be SELECT DISTINCT, but whatever). Otherwise, temporary tables can be used.

The real reason relational algebra seems easier to deal with is because you can use symbols to represent things and there are no data types. It is an abstract language that cannot be implemented because it is under-defined.

If you try expressing a hairy SQL query with relational algebra syntax, you will end up with a hairy relational expression.

Re:I think he's trying to say:

[Anonymous Coward]

if it's id is in b

"its".

Re:I think he's trying to say:

[shakah]

Referring to your "SQL join example", doesn't a SQL view get you your T1 equivalent?

(Apologies in advance for possible syntax errors)

CREATE VIEW region_sales AS
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name

With that, the end-(user|developer) wouldn't be bothered with JOIN syntax, or perhaps even know about the underlying tables. They could, as you so eloquently put it, "do shit with [it]":

SELECT SALES
FROM region_sales
WHERE REGION='East' ;

Re:I think he's trying to say:

[Anonymous Coward]

I can see where a syntax like: "select * from t1 join t2 using relationship_name" would be usefull short hand for spelling out all of the join fields. That way it is part of the design or the domain defined by the constraints meta data. No room for error there if you leave out a key field and perhaps, in a perfect world, disallow any other arbitrary join that is not defined by a constraint. Views and solid standards would work too I guess. In my world though it is never clean because I a moving whacky over grown ims, vsam and flat files over to powerfull commodity hardware. And the requirements are make sure we can use it just like we used to AND make sure we can do more with it off of big iron. They seem to pay more if you are willing to be kinky like that.

Re:I think he's trying to say:

[mcrbids]

(Technically, all SELECTS should be SELECT DISTINCT, but whatever)

Um, no.

Distinct is essentially equivalent to a "group by", and is typically used to hide or work around a failed cartesian join.

Sometimes I use "group by", when I'm trying to get a count. The following example shows student count by county. (and would usually have more clauses, to determine that we want *active* students, etc)

Select county.name as county, count(*) as count from county, students where students.county_id=county.id GROUP BY county.name ORDER BY lower(county.name) ASC

I basically never use DISTINCT. Every time I'm tempted to do so, I find that what I really have is a query not thought through properly. One of the tables in the join was not properly handled, squirelling your results.

For example:

Select a.* from a, b, c where a.b_id=b.id and a.name='bob' and c.country='USA';

It's a simple statement - but this is a failed cartesian join since the join on table c was not properly related to a or b, typically with a clause likee "and c.a_id=a.id" or something.

An urge to use distinct is generally a sure sign that you either

A) Have corrupted or hosed data in the database, or

B) Are missing a clause in your statement, resulting in a cartesian join.

When you start mixing inner and outer joins, it's easy to do!

Re:I think he's trying to say:

[Gilk180]

The reason all SELECTS should be SELECT DISTINCT only referred to my example. In the relational model, all elements are distinct. The tables are sets, not multisets(or bags). So mapping from relational algebra to SQL, all results should only contain distinct elements.

For instance, projecting a single column from a table in the relational model will only result in the set of distinct elements from that column. In SQL, doing the same will result in one element per input row, which may mean multiple copies of some elements.

No you don't

[Phil John]

you tell them you want postgresql or you'll move to another host who costs the same, probably co-los in the same datacentre and offers the features you require.

I've even seen free hosting deals with postgresql on.

This is one place where I agree.

[Ayanami Rei]

For all that Rel (Tutorial D) wants to be, I don't understand this obsession with NULLs.

I think the concept of null values is very sound, and how they behave in comparisons makes perfect sense.

Just put non-null constraints by default on the structures in the language, and then provide a way to mark relational fields as possibly having NULL values. Can they at least allow that? :-)

Re:This is one place where I agree.

[lub]

The problem with NULL is that you don't know what it means. Suppose you have a field 'Job' in a table 'Persons'. When it's not NULL, you know that person has a job. But when it is NULL, it could mean:
-this person has no job;
-the job of this person is unknown.
More on this.

Re:This is one place where I agree.

[MrLint]

null means you havent entered any data. Much like a crossword puzzle a blank means you dont know, you havent put in a letter yet. An attempt to derive meaning from literally nothing. This is speculation in its purest form.

Re:This is one place where I agree.

[ahmusch]

The problem with NULL is that you don't know what it means. Suppose you have a field 'Job' in a table 'Persons'. When it's not NULL, you know that person has a job. But when it is NULL, it could mean:
-this person has no job;
-the job of this person is unknown.

Then the problem is in your relational model. You need a record in the Job table akin to "No job." If the person has no job, link the job field in the person record to the "no job" record in the Job table. If there is no data regarding the job of that person (for example, no input data was provided), then NULL is completely correct.

Alternatively, you could set up the appropriate "indeterminate job" records in your job table and your question becomes much, much simpler. Of course, then you have to know exactly which indeterminate states you're looking for -- no data provided, invalid data provided, job person had was eliminated, and the like.

This would be a solution you'd probably use if the reason for the indeterminate nature of the state were relevant -- if your application doesn't care why the state is irrelevant, don't bother trying to map the reason and go with the NULL.

The value of NULL is that it means what it means -- namely, and indeterminate state. If you "know" someone has no job, using NULL is incorrect -- link the Person to the "no job" Job record.

RPG

[stanwirth]

RPG already overcomes the shortcomings of SQL

Re:RPG

[Anonymous Coward]

You also get more experience points!

Re:RPG

[julesh]

RPG? Rocket Propelled Grenade?

Null=Null is Null, except Group in Group By

[Saint Stephen]

One of my favorite idiosyncracies with null is Null = Null is Null (unless you turn that off in the DB), but Group by groups together equal values except it also groups nulls!

Re:Null=Null is Null, except Group in Group By

[Anonymous Coward]

I heard the bell is a sentence with a transitive object.

Oh wait, let me rephrase that: "I heard the bell" is a sentence with a transitive object. Sometimes, those quote marks really help.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Any query language is going to be ugly...

[1iar_parad0x]

I looked at the articles, which are too broad by the way, and I didn't see enough of an explaination to make out real detail.

However, it seems as if Tutorial D is nothing more that a purely mathematical improvement of SQL. In other words, it's like comparing Lisp to Prolog or ZF Set Theory to HBG. (Okay, I'm not a logician..., so you math wizards can hold off on the flames.) I'm all for improving SQL. It just seems like Tutorial D addresses the usual complaints about the hazy concept of a NULL in a database.

SQL or any derivative thereof will be inherently complex. This is because SQL is merely an implementation of Relational Algebra. That's the key. Real RDBMS's are inherently mathematical in nature. Complex SQL queries are tough. Too often the average database programmer couldn't even tell you what the definition of a set, function (i.e. the mathematcial definition), or a cross-product is. Yet, they'll write code (or not) that incorporates complex joins. Incidentally, they'll do the same thing with regexs too. Programming still has it's deeply mathematical roots. Not as much as I'd like, but still...

In short, if you're looking for some cool new English Query tool to save the day, you won't find it here. Still, if you're a SQL wonk looking for a new toy or an improved SQL, this may be of interest to you.

Oh, by the way, I used to have a copy of Date's database book. I sold it along with my copy of Foley and Van Dam, Sipser's Theory of Computation, and Aho and Ullman's compiler design book [the "dragon" book] because I needed to pay the rent. Interestingly enough, the books retained a pretty high resale value even after all these years.

Re:Any query language is going to be ugly...

[Tablizer]

SQL or any derivative thereof will be inherently complex. This is because SQL is merely an implementation of Relational Algebra. That's the key. Real RDBMS's are inherently mathematical in nature.

I disagree with the "ugly" premise. I have been working on an SQL replacement language myself (described briefly in another thread). I don't believe that (practical) relational operations are inheritantly complex or confusing. There are two problems with the way SQL and/or relational is often presented that gives the false impression that relational operations are inherantly messy, unnatural, and/or confusing.

First, the "base" operations that Dr. Codd presented with his relational papers are not necessarily the same operations that a relational language needs to present to the user (programmer). For example, in Boolean algebra, the "purist" (and only) operation is NAND (NOT-AND). Any of the other operations can be built with NAND alone. However, humans generally don't relate to NAND because our language has gotten us too familiar with AND, OR, and NOT. We thus use the human-friendly operations instead of the "purist" operations. The trick is to find relational operations that are friendly to humans, yet are still based on the "base" operations. As long as they are defined via the base (proper) relational operations, they are valid.

Second, SQL does not make it very easy to break big problems into smaller problems. The approach I envision would allow this. There would be intermediate virtual tables that would feed to later operations. One can concentrate on creating one virtual table at a time.

x1 = foo(....)
x2 = glab(....)
result = join(x1, x2, ....)

One can refer to chunks via name, whereas SQL forces one to nest stuff to acheive the same thing. (Plus, SQL sometimes requires one to duplicate something if two different "roots" of the parse tree need the same construct. By-reference instead of by-nest would fix the duplication.)

Re:Any query language is going to be ugly...

[Anonymous Coward]

Second, SQL does not make it very easy to break big problems into smaller problems. The approach I envision would allow this. There would be intermediate virtual tables that would feed to later operations. One can concentrate on creating one virtual table at a time.

If you use SQL Server you can allready do this with table variables. The syntax is a bit ugly, but it doesn't exactly require a new language.
Also, views function like tables. You can use them to reduce duplication.

Re:Any query language is going to be ugly...

[Tablizer]

If you use SQL Server you can allready do this with table variables. The syntax is a bit ugly, but it doesn't exactly require a new language.

That is true, but it's still a bit hard to read in that format IMO. It might be a reasonable compromise, though. We can perhaps agree on that.

Also, views function like tables. You can use them to reduce duplication.

But the SQL writer (app developer) usually does not have access to create them. And, they are global more or less.

Re:Any query language is going to be ugly...

[gyg]

The approach I envision would allow this. There would be intermediate virtual tables that would feed to later operations. One can concentrate on creating one virtual table at a time.

x1 = foo(....)
x2 = glab(....)
result = join(x1, x2, ....)

One can refer to chunks via name, whereas SQL forces one to nest stuff to acheive the same thing.

Isn't that what views are supposed to do?

Re:Any query language is going to be ugly...

[Tablizer]

Isn't that what views are supposed to do?

As stated nearby, app writers usually cannot create their own views, let alone local temporary views. However, the view concept can perhaps be extended to handle this. Some dialects have things somewhat like that. For example:

select * into x1 from sdfsdf
select * into x2 from x1 where ....

Re:Any query language is going to be ugly...

[gyg]

Interesting. I did not know that. Would you say though that the inability of app writers to create local temporary views is a SQL weakness or an implementation weakness? Sounds to me like it's an artifact of how access is managed in a given DB, no?
The dialects you mention do deserve to be common practice IMHO. Which ones are they, BTW?

Re:Any query language is going to be ugly...

[Tablizer]

Interesting. I did not know that. Would you say though that the inability of app writers to create local temporary views is a SQL weakness or an implementation weakness?

The standard does not define it as part of a SELECT statement that I know of.

The dialects you mention do deserve to be common practice IMHO. Which ones are they, BTW?

IIRC, it is Microsoft and SyBase dialects, but it has been a while, so don't take my word for it.

Perhaps it is interesting.

[abulafia]

And if it were actually relational, then it might be interesting in the current discussion. But it ain't. That it comes up is funny, in context, because mistaking things like XML for relational is something that Date regularly has massive heart failure over.

Re:Perhaps it is interesting.

[Anonymous Coward]

And for good reason, too: network- and hierarchic-database systems kicked the bucket because of inherent limitations (e.g. read through a CODASYL paper sometime) that RDBMSs don't suffer from.

Because guess what: XML + XQuery is a reimplementation of a hierarchic database system. It may be optimal for hierarchic data, but wait until the majority apply it to tabular/set-type data. Ugly, ugly.

Re:Perhaps it is interesting.

[abulafia]

Because guess what: XML + XQuery is a reimplementation of a hierarchic database system. It may be optimal for hierarchic data, but wait until the majority apply it to tabular/set-type data. Ugly, ugly.

Hey, no need to wait - I've seen people doing this already.

Some code in a project my company just took over was using XML for moving what amounts to array-of-arrays style data around _internally_, in the application. Had XSDs and everything. One of the worst cases of consultant-work padding I've seen in a while.

D Programming Language

[retrakker]

Welcome to acronym-palooza? Anybody heard of D Programming Language? It's around since 1999 and has been covered here on ./ Some research (read as "Query") if that name is taken already you can expect from some database guys?!

Re:D Programming Language

[julesh]

This is based on the D programming language suggested by Date and Darwen in their 1995 paper (presented to ACM), The Third Manifesto. I suggest the digital mars people ought to have done some research instead, if they're even slightly bothered.

Re:D Programming Language

[retrakker]

The paper of Darwen and Date (fetch a copy through citeseer) reads in the footnote referering to the term "D" : 2 No special significance attaches to this choice of name; we use it merely to refer generically to any language that conforms to the principles laid down in subsequent sections. Thus, there is no academic or other reason that the digital mars people needed to take care of that.

enough of the same old stuff...

[f00zy]

The SQL issue seems to resurface on a regular basis. Not nearly as often as Microsoft complaints, but still enough to be enough. SQL + your language of choice can do pretty much anything you want. Sure, we all want and expect new implementations, but please people, stop the mindless rants against things that DO work. SQL works. It could be better, but it does what it does AND it works.

Sounds like...

[TheVidiot]

Jack Black and some bald dude have gotten into databases?

Re:Sounds like...

[Anonymous Coward]

Hehe, a buddy of mine once told his database program used "squeel", after a bit of head scratching I realized that it was his way of pronouncing SQL.

SQL is a poor relational data model implementation

[ikewillis]

I don't think lottameez's problem was so much with the concept of a join but the semantics of SQL and how it implements the relational data model.

SQL was a language designed to allow relatively unskilled operators to be quickly trained for data entry using the language directly, and thus it was designed to be English-like, flexible, and forgiving.

SQL is only vaguely reminiscent of the true mathematics behind the relational data model. It continues to be used and expanded, despite its restrictions and shortcomings, because it is far and away more popular than any other database language.

The fundamental assumption of the relational model is that all data is represented as mathematical relations, or rather, a subset of the Cartesian product of n sets. Unlike SQL, in the mathematical model reasoning about such data is done in two-valued predicate logic (that is, without a null value), meaning there are two possible evaluations for each proposition: either true or false. The data is operated upon by means of a relational calculus and algebra.

The relational data model permits the designer to create a consistent logical model of the information to be stored. This logical model can be refined through a process of database normalization. A database built on the pure relational model would be entirely normalized. The access plans and other implementation and operation details are handled by the DBMS engine, and should not be reflected in the logical model. This contrasts with common practice for SQL DBMSs in which performance tuning often requires changes to the logical model.

How about BOSQL

[JPyObjC Dude]

A big move has already startedto move to Business Object Servers to host enterprise level application deployments. BOS's are currently used to run the core engineering departments of most F500 companies. These BOS's typically use a Query Language that is catered to printing and expanding data of and from business objects. I currently use a language called MQL which stands for Matrix Query Language. I have talked to some developers who use XML and they say that is has similarities.

Basically any Query language should alow developers to build data models quickly without having to worry about the complexities of managing the data base. Most BOS's use a RDBMS in the background and manage all the SQL's under to the database.

If you want to find an area where the bucks are.. BOS's are it. The big companies are spending billions on these systems and they deployements are projected to only increase.

We were able to spec, build and deploy an enterprise HR Performance and Development system for a fraction of the cost and time with these systems. The only issue with these multi-teir systems is that they are very resource intensive but then again, processors and memory is getting cheaper by the day.

Hmmm how about an open source BOS... :]

Re:How about BOSQL

[DrFalkyn]

Could you describe more about these "Business Object Servers" and exactly what they do, with a real word concete example, or is it just another buzzword like Microsoft's "Digital Nervous System" I remember from a while back.

Re:How about BOSQL

[JPyObjC Dude]

A business object server is a component of a multi-tier application structure. The BOS sits between the interface and the RDBMS. The BOS allows a developer to specify object types, attributes, relationships, policies. These business objects can be basically anything from a cad drawing to a issue artifact to a engine part. The BOS takes care of this data stored within the RDBMS as well as indexing (however custom indexing is possible).

Business object servers are currently very expensive investments and this is why it is only used by the big companies to manage big resources. They were originally developed for CAD configuration management. The use of a bos to config managagement of engineering data is known as Product Data Management or PDM. The new buz word in the IT boardrooms is PLM or Product Lifecycle Management. This is basically where you use the BOS and it's frameworks to allow for lifecycle control of products including project managment and document digitization.

A BOS would allow for a developer to model a business down to it's work products and work flows and allow for automation of these workflows including user notifications and the such.

Take a look at Matrix One. Not that I particularily love these guy's but their core engine is very well built. The basic underlying framework they have come up with is extremely flexible. It can be used to automate pretty much any process you can throw at it. The best thing about it is it handles all your Oracle SQL for you. The programmer just needs to interface with the BOS via their object relational query language.

I've been trying to light the torches to start up an open source BOS but it is really just now in the very early stages as such a project would be a massive undertaking like on the magnitude of the Mozilla project. Not something I could build in one of my lifetimes. I'd need dozens of developers to pull it together.

I know that the demand will be there as my company alone is currently forking out millions of dollars a year for these systems and deployments on their frameworks. And our IT forcasts are showing that it will only increase as time progresses.

My work on this

[Tablizer]

I have been working on an SQL alternative myself for a while. My approach is more functional (not procedural) in nature. If the language is designed based on this, then new operations can be added without having to add to the syntax of the language. This would help vendor compatibility because if vendor B does not offfer an operation that vendor A does, then a DBA can simply add a clone of the "function" without tweaking the language parser.

Tutorial D uses infix notation, which tends hard-wire operations to a syntax parser. Prefix (functional-style) is more flexible, consistent, and easier to parse. For example, new parameters can be added to prefix without changing existing calls. It is just an extra, perhaps optional, parameter. It is hard to do with with infix.

My relational replacement would also make it syntactically easier to perform relational operations on things such as column name lists. The column list is simply a table in its own right (perhaps with syntactical shortcuts); thus it can have table operations (relational algebra) done on it just like tables. It is "conceptual reuse" you can say.

Re:My work on this

[leandrod]

> I have been working on an SQL alternative myself for a while

Have you grokked the relational model already? Last time I checked you didn't, and you keep talking 'tables' instead of 'relations'.

Re:My work on this

[Tablizer]

Last time I checked you didn't, and you keep talking 'tables' instead of 'relations'.

I like variable names that most people can relate to. It is called "people skills".

Re:My work on this

[leandrod]

> I like variable names that most people can relate to

What does this has to do with variables?

> It is called "people skills".

So keep your skills, but please add some technical and theoretical fundaments.

A proposal: publish something of your current work. If it's sound, it goes up to DMoz. If not, it gets dissed.

Re:My work on this

[Anonymous Coward]

What does it matter what it's called? A rose is a rose. Are you going to raise objections if somebody calls an air-conditioning unit "the cooler"?

Re:My work on this

[Anonymous Coward]

Prefix (functional-style) is more flexible, consistent, and easier to parse. For example, new parameters can be added to prefix without changing existing calls. It is just an extra, perhaps optional, parameter. It is hard to do with with infix.

I don't see what is hard in Python and C++ style default function arguments.
If you add a new parameter to an old function, you can set a default and all clients are happy (and if there is no acceptable default, you need to change call sites whatever the syntax is).
Maybe you are confusing language complication with implementation difficulty: prefix syntax is cheaper, not more powerful.

Re:My work on this

[Tablizer]

It is confusing to take an infix operation (2 parameters) and upgrade it to handle 3 or more parameters. Sure, one can make the old stuff work by having a 2-param infix operator and a 3+ param prefix version, but that can be very confusing to users. You have to document both, and frankly I think that would raise eyebrows.

Remember, if we are going to replace SQL, we have to make it easier than SQL. Mixing infix and prefix for the same given command is not going to help that goal.

Re:My work on this (corrections and additions)

[Tablizer]

It is hard to do with with infix.

Correction. Should be: "It is hard to do this with infix."

My relational replacement would ...

Correction. Should be: "My SQL replacement would ..."

Also I forgot to mention that it's roughly influenced by an early IBM relational language called "Business System 12" (BS-12). It seems IBM thought the more wordy language, SQL, would appeal to PHB's more than the mathy style of BS-12. I guess the failure of PL/1 (spelling?) against COBOL had something to do with that decision. Thus, SQL is the COBOL of relational languages, more or less. Having the letters "BS" in the name is also a strike against it :-)

Why not awk?

[Anonymous Coward]

We (BitMover, the people who produce BitKeeper) are building a database on top of our SCM system (yes, that's a little weird but it works out well).

We played around with straight SQL syntax, flirted with ruby as the syntax, and ended up with awk (actually nawk). It was the best balance between simple enough for normal people to understand and powerful enough to get the job done.

So we can do stuff like

bk db -s'select from bugs /xyzzy/ {print $ID}'
bk db -s'select from bugs where $OWNER == "rob" {print $ID}'
bk db -s'select from bugs where $OWNER =~ /rob|lm|wscott/ && $STATE =~ /open|assigned/ {print $ID, $SUMMARY}'

etc. The where clause and the print clause (print is implied) are full on awk scripts, do whatever you want with them.

We're big fans of not reinventing the wheel. Awk is a little weird but it's 95% of the way there and a few tweaks made it perfect for us. So why invent a whole new language? Especially when all the awk code is about 100K of text on x86 and includes a full on regular expression library.

I think with Rel...

[Ayanami Rei]

that they are trying to give you the best of both worlds. Expressions that can be expressed simply in SQL have short forms in the language too. Expressions that were hard to express in SQL but easy in R.A. are still pretty basic forms.
By not trying to be like SQL, and more like {T,PL}SQL, I think they get the freedom they need.

Exactly!

[Ayanami Rei]

It just means "unknown". Not N/A. But "unknown".

In a T/F column, it's not true, and it's not false. It _never_ short circuits and expression, behaving as TRUE with ANDs and FALSE with ORs.

I think it's great.

Re:Exactly!

[maxwell demon]

Except that if it means "unknown" it should never behave as TRUE of FALSE, not in ANDs and not in ORs.

x and unknown is
* false, if x is false
* unknown (not true) otherwise

x or unknown is
* true, if x is true
* unknown (not false) otherwise

So if NULL behaves like TRUE with AND and like FALSE in OR, then it's inherently broken.

in anger???

[thomasa]

Quote
Most people who use databases in anger are familiar with the concept of a relational database.
Unquote

Maybe someone could enlighten me as to why the word
anger is in the first sentence of the article. Why
would using databases in anger be relevant? I use
databases in finanical situations, is that similar
to anger?

Dynamic Relational

[Tablizer]

One thing I always wondered was if there was a way to make a table that had a different number of columns for each row.

I don't know if it would solve your particular problem, but I have kicked around the idea of dynamic relational databases.

Hey, I worked for that company....

[Dasein]

Not on K-Man but on the network model database -- before it got the SQL front-end.

Wow -- that was something like 12 or 13 years ago. BTW, the people I worked with there were a great bunch of guys.

I would be interested to find out if there are any other mdbs alumni on slashdot.

Re:Hey, I worked for that company....

[RocketJeff]

I would be interested to find out if there are any other mdbs alumni on slashdot.

Well, there's at least one - me. I worked in the Consulting division in Chicago from 1989-1991 (Hired when they closed the Dallas office).

It was (and probably still is)an 'interesting' company. A victim of expanding too fast and in the wrong direction.

K-Man could have competed with dBase, but dBase was better positioned and marketed.

mdbs was an excellent non-relational DB, but got steamrollered by the relational movement.

Guru (K-Man with some simple AI thrown in) was interesting, and is still one of their products.

Object/1 was an excellent development tool but it was written for OS/2 as Windows 3.0 was being released and the Windows version too way too long to develop.

mdbs (the company) still exists, but I'm not sure how well(or how many) employee's it has now (it went from 150 when I started to less then 80 when I left).

Re:Hey, I worked for that company....

[Dasein]

Yep, we were there at the same time. There was a QA guy that moved from the Lafayette office to the Chicago office about that time. Can't remember his name.

According to Barry Parshall, mdbs is(was) sueing a lot of its former customers. It's been a few years since we had that conversation though. They're operating out of a P.O. Box now.

All I can really say is that working for Kim Logan made me the programmer I am today. He taught me to know where all the bodies are burried. I can't tell you how many times I've bailed out other developers because I could debug optimized x86 code.

Re:Hey, I worked for that company....

[graybeard]

Hey, I trained Barry Parshall to replace me! (I think.) He started in customer support ... you can look it up.

If that had been my second job, I would still be there. (I hope.) I was fresh out of Purdue, didn't know how good I had it there. I learned so much. I would put that group of developers against any other, anywhere. And I agree, Kim Logan is the bomb; he was my second supervisor.

Re:Hey, I worked for that company....

[Dasein]

I started in technical support working for Barry!!

4 million lines!?!?

[Mustang Matt]

I can't even begin to fathom that many lines of sql.

How would you even begin to troubleshoot something like that? I'd think you'd have to start breaking it into smaller sections.

Re:4 million lines!?!?

[jdray]

He did say "total" for their stored procedures. I suspect they're in small chunks, but many, many chunks in total.

Re:4 million lines!?!?

[panda]

Ha! Ha!

When I read that, I thought to myself, "So, you're the sucker they hired to replace me...." I think I know where the granparent might work.....

Whether or not I'm right, the place I'm thinking of had 1 stored procedure that was over 30,000 lines long with a lovely comment at the top about what happens to programmers who make changes that break it.

Yes, it was all Transact SQL that flavor of SQL used by Sybase and Microsoft SQL Servers.

you were born too late

[kpharmer]

see, if you were born around 1940 you could have been using IMS/DB, VSAM, ISAM, IDMS, etc back in the 70s.

Tons of opportunities there for low-level access to your data. Of course, there's a reason that all those database management systems were abandoned for a 'busted ass super high level language'. It's because they sucked to maintain, they didn't evolve well as business requirements changed over time, and if you had the *most* basic of business questions - you'd never get an answer without a month of writing code.

But don't despair - pick up a little more SQL and you may find it isn't that tough.

Re:you were born too late

[Hognoxious]

see, if you were born around 1940 you could have been using IMS/DB, VSAM, ISAM, IDMS, etc back in the 70s.

I was born in the '60s and I've used IMS/DB (I think - it's the one with "obtain calc" - what utter twaddle), VSAM and IDMS. We also had an IBM c080l compiler and I noticed it was older than me. Oh, and that was in the '90s!

Er ... you insensitive clod!!!!!

Re:you were born too late

[gfody]

If I wanted low-level access to my data I could just choose to not use a database server.. I can implement my own balanced trees and indexes however I want, however that would be a lot of re-inventing for no good reason.

I know sql, very well in fact. I'm not asking for low-level access to my data I'm asking for low level access to my database server. Something native to my client language or my language of choice that would integrate with my code insight and IDE features, use enums, classes, variables, exceptions etc. Instead of a SQL reference and a billion static string resources.

I've written a few dozen GUI-on-top-of-big-ass-SQL-statement modules.. every single one was a PITA because of sql syntax naunce and high level bullshit.

Now, from a sql command line.. simply typing up the query to look at my data I think sql is fine. It could use a revision for some of its querks, but that shouldn't be the interface my client application uses to get the same data!

Re:you were born too late

[kpharmer]

> I know sql, very well in fact. I'm not asking for low-level access to my data I'm asking for low level > access to my database server. Something native to my client language or my language of choice that
> would integrate with my code insight and IDE features, use enums, classes, variables, exceptions
> etc. Instead of a SQL reference and a billion static string resources.

To do this, you probably want a OODBMS, one that is tightly integrated into java, etc. Those options exist today - and provide very seamless persistence services to your application.

Relational databases are metadata-driven on the other hand - if you take a look you can see that all tables & columns are described in system tables. This gives other kinds of benefits - such as allowing the database to provide a generic joining capability in which you don't have to plan out how to do the joins - it does this for you.

OODBMS' can be very fast - but their performance stinks for long scans, huge queries, etc. Additionally - in everyone that needs access to your data should go through your API. While that make sense sometimes (for transactions), it completely fails when you want to do a simple report (Try marshalling 1 million objects in java).

No recommendation for you here, perhaps this is something that would be easier to manage from the IDE?

Procedural extensions to SQL-- how and why

[einhverfr]

Now, I am sure you realize that most of the common databases (MS SQL, Oracle, DB2, PostgreSQL, Interbase, Firebird, Ingres, etc) have the ability to embed procedural extensions into SQL, at least in stored procedures.

The reason is that stored procedures are incredibly useful-- you can largely automate database management and the way in which it integrates with other software using stored procedures and triggers. without the procedural extensions, you cannot have stored *procedures.*

At the same time procedures should not be how applications *access* the data, and so you are right-- that is the idea-- a clean separation between data retrieval and programming procedure.

I am of the opinion that

[Anonymous Coward]

IT is not a replacement for SQL.

How About SQL Server 2005 with Built-in CLR

[techSage]

So that you can use VB or C# and probably other CLR supported languages to write database code.

See here under ".NET Framework Integration."

MySQL

[Anonymous Coward]

Yeah, a name like MySQL sounds so selfish. We should all have access to the source code. Therefore, it should be OurSQL!

It doesn't make my toilet bowl springtime fresh.

[AmazingRuss]

Other than that, it's pretty g-darn spiffy.

NULL is problematic.

[Estanislao Mart�nez]

In practice, NULL is used to mean two things, and it's often not clear in an individual instance which it is:

1. We don't know what the value for that field should be (or whether there is one).
2. We know that there is no value for that field.

The first of these is "the database has no information for this field", the second is "the database has the information that there's no value for that field". I've seen a fair amount of confusion in cases where it's not clear which of these a NULL stands for in one case.

Re:NULL is problematic.

[slamb]

Yes, for this reason, if I were designing an SQL replacement, NULL would not remain as it is. I would probably replace it with two values, UNKNOWN and INAPPLICABLE, corresponding to the two cases you described. In fact, Dr. Codd, the father of relational algebra, suggested having multiple types of NULL. (There might even have been more than two. I don't remember what the others were.)

I might also introduce keywords POSSIBLY and CERTAINLY that collapse tri-state logic (true, false, maybe) into boolean logic. Thus, POSSIBLY(a = 5) would be true when a is UNKNOWN but CERTAINLY(a = 5) would be false.

Date advocates a different approach - no NULL at all. Instead, he has some sort of parallel table structure; a row in one table for the value being present and in another for the value being absent. With some more complex way of constraining it so there would be no contradictory information in the tables. I don't like this approach - having no NULLs seems simpler than having two, but not once you add in the weirdness of contraints. And not once you realize many tables have multiple nullable columns. Joining so many tables together would get ridiculous quickly.

In practice NULL seems to not be a huge problem for me. Occasionally a field can either unknown or inapplicable, and I need to distinguish between the two; I have to do a kludgy thing with another field and a CHECK constraint. But for the most part, it's just an extra half second of thought when writing the logic, which isn't too bad. But it does trip newcomers. It would be worth fixing if you were designing a new relational query language from scratch.

Re:NULL is problematic.

[lfourrier]

And not once you realize many tables have multiple nullable columns. Joining so many tables together would get ridiculous quickly.

In what normal form ?
When you look to 5nf, tables have 3 columns : (id, key, value) All the flexibility you want, but don't think SQL.
Date advocate no Null, but he also advocate something else in place of SQL. Tutorial D, in fact.

Re:NULL is problematic.

[pixelcort]

Wow, a whole lot of talk about NOTHING!!!

Re:NULL is problematic.

[basingwerk]

Was Rumsfeld a database expert too? "As we know, there are known knowns. There are things we know we know. We also know there are known unknowns. That is to say we know there are some things we do not know. But there are also unknown unknowns, the ones we don't know we don't know. "

Re:NULL is problematic.

[TheMiller]

This description of Date's approach to NULLs is incomplete. The far simpler method that he recommends is to set aside a special value to replace NULL. For instance, an empty string is often a good choice to mean "unknown" in a string column. You can also set aside multiple special values to mean different things -- for instance, "N/A" for Not Applicable. This is more flexible than NULLs, and doesn't suffer from multi-valued logic problems, since these special values really are values. I haven't yet seen a case where it was a problem to set aside a few values out of the range of a field type for these purposes.

Choosing the special values carefully can make queries simple, too: Consider how the special values are ordered compared to non-special values. For instance, I've set aside two special date values to represent "infinite past" or "infinite future"; the former is a value that sorts before all valid non-special values, the latter sorts after them. Use the former to mean "we don't have a specific date, but consider this event to have happened already". The latter is used to mean "we don't have a specific date, but consider this event as never having occurred".

This approach is quite practical and gives you a lot of flexibility.

Re:NULL is problematic.

[Anonymous Coward]

In-band signalling is almost NEVER a good idea. The COBOL guys learned this on 1/1/00. The phone guys learned this when Cap'N'Crunch made their 2600Hz whistle.

What do you do when the domain of your tuple is same as the domain of the data types? Do you make up some value that you hope never comes up in real life? No, that's absurd.

The proper thing to do is create a standard value for it, which ANSI SQL calls NULL. The only problem with NULL is that SQL doesn't properly define operations on it, not that it exists at all.

aQazaQa

Re:NULL is problematic.

[khallow]

First of this thread that I agree with. We shouldn't have to resort to weird values to convey NULL. Nor have multiple NULL's. Normally, when I access an entry, I just want to know whether it's there or not. NULL tells me fast. If I were doing something like inserting guesses for missing data, then I'd like to know whether a particular NULL should be guessed or isn't applicable. So maybe we can add some flags or other decoration so that if you really want to know why a NULL is there, you can find out.

Re:NULL is problematic.

[TheMiller]

That's the way that Codd went in second version of the relation model. But it just makes even more of a mess out of what *should* be simple two-state predicate logic. Personally, I'm firmly in the "no NULL" camp. I like it when boolen logic operators do what they're supposed to.

Re:NULL is problematic.

[TheMiller]

I understand the similarity that this has with in-band signalling. But part and parcel of this is that the special values are, by definition, no longer considered "in-band". You have to take steps to treat the special values as special, and not confuse them with "real" data. It's simple: A type defines a set of possible values. So you define the set of values for a field to include a valid range, plus some special values, and you define how these interact. The fact that the special values are represented in a way that's similar to the other values is a physical consideration, not a logical one. BTW, what the heck is the "domain of a tuple"? And NULLs are not values, which is the entire point of the discussion. SQL couldn't properly define operations on it, because it's a lack of something, not a something. If you replace NULL with a special *value*, you'll have exactly what I'm describing.

No new languages needed.

[Doc Ruby]

By creating a new language, "Tutorial D", developers are excluding the other languages as much as they're including new features in the new language. Why not just add a Java package that includes the new syntax? To get anywhere in software development, even Tutorial D code will have to interoperate with existing systems and programmers with existing skills. Someone will have to code a "Tutorial D" JDBC driver, and ODBC, and all kinds of middleware that eats performance, developer time, and introduces the maintenance pitfalls of complexity. And by adding a package to an existing language, they can skip reimplementing the features of the existing language that they include in this new one, like loops, branches and character output. The effort seems as vain as the endless 19th Century conceits of inventing complete philosophical systems from scratch, to serve the reputations of egomaniacs dominating university debates. Why can't everyone just speak Object, with procedural slang and set-theoretical poetry?

Re:No new languages needed.

[Anonymous Coward]

If you are in any way familiar with Date's works, then you will find out that Tutorial D is a teaching language intended to demonstrate relational database concepts. This means that any language can be a "valid D" language if it meets the criteria laid out by Date and Darwen. Tutorial D is one syntax implementation of this. Tutorial D is not necessarily a replacement, per se, to SQL. It is a fact though that there is an implementation of it (that being Rel.)

Secondly, unless I am showing my naivete of Java, how do you "just add a Java package that includes the new syntax"? That is cool if you can add syntactic elements to Java. ;-)

Re:No new languages needed.

[Doc Ruby]

Yes, I have "read" Date's relational bible (really just referred to it a lot on demand). True that I am really referring to Rel, the subject of this story, linked from the article linked from Slashdot.

The syntax of Rel maps directly to the "object messaging" in Java (or any other similar OOP languages, like C++), AFAICT from the articles I just mentioned. Operator overload, and new classes. There might be some syntax too awkward to model directly in "sets.relation = relation(elements("x", "1"), elements(y, "2"))" or somesuch, but the utility of Rel is obviously not its syntax, but its data/relation metamodel and operations. Coding Rel in YACC seems to duplicate quite a lot of work already in eg. Java, while adding Rel classes to Java seems to leverage all the benefits of both, losing only the wit and charm of some of its oddly SQL-styled syntax.

Re:No new languages needed.

[Eivind Eklund]

You're losing one major benefit of having a lighter weight language than Java: Ad-hoc queries.

Having to write and compile code to do ad-hoc queries instead of running it in a standard line-based CLI with command history.

So, I see the value in playing with a full new language.

However, I personally prefer having the relational model available as an API integrated with my main language - so I'm actually working on an implementation of a "D" as a library for Ruby, my favourite language. There, the objection I had above mostly fall away - because Ruby is available in a command line form, and is light weight enough / has enough support for sort of creating domain specific languages that you get little benefit from creating an entirely new language like Tutorial D (I can do my ad-hoc queries in the interactive Ruby shell irb.)

Eivind.

Re:No new languages needed.

[Anonymous Coward]

Java is not remotely powerful enough to be used as you describe.

Lisp programmers, on the other hand, have been doing this for decades. For example, when object-oriented programming became the rage, they wrote the Common Lisp Object System, a few hundred lines of Lisp code that turn Lisp into one of the most powerful OO languages around.

The reason Lisp can do this is that the core language has a very simple syntax, and (consequently) a very easy-to-use macro facility for extending the language. Macros work at the structural level, not with sourcecode text...you're dealing directly with the parse trees, and the parse trees are very simple. Extending the language to fit your problem is all in a day's work.

Java has nothing like this. Most languages don't. Heck, Java doesn't even have operator overloading. It's got a nice set of libraries (Swing rocks, imo), but you're always going to be stuck with Java's syntax, object model, etc.

Re:No new languages needed.

[Doc Ruby]

I suggested Java because it's crossplatform, as any successor to SQL must be. Will your Ruby-D extension run on HW from mobile phones, through the "big 3" PC/OS'es, through mainframes and supercomputers? How about coding a Java program with a javascript interpreter as a DB client, with the D extensions to both? OTOH, if you just like Ruby, who am I to quibble?

Re:No new languages needed.

[Doc Ruby]

Is there a lisp system with a flowchart GUI? Rel looks shoehorned into the text/predicate form, and these relations cry out for topological processing. If it's got a flowchart cockpit, and lisp under the hood, with relations where the rubber meets the road, it's a hotrod anyone can drive.

Re:No new languages needed.

[Eivind Eklund]

Mostly, I'm doing this in Ruby because I personally want to use it with Ruby - and if nobody else use it, having it available for my own use is enough motivation to develop it. If I were to develop it in Java, it just wouldn't happen - because Ruby is so much more pleasant to program in.

Also, I don't see replacing SQL per se as my goal. My goals are (haphazardly arranged):

• To make a tool that is useful to myself. I like programming in Ruby more than I like programming in Java.
• To make a tool that is useful to my friends. And my friends mostly program in Ruby, Perl and C/C++.
• To learn more about how implementations of the full relational model work inside programs. For me, this requires implementations in Ruby or Perl, because those are the languages I've done much professional programming in lately (moving towards Ruby as much as I can)
• To inject knowledge about how the relational world built on non-SQL queries can be to the overall programming community. I believe I can do this more efficiently in Ruby than in Java, because I know many of the central Ruby players, the Ruby community is small enough that a technology fairly easily can go through the entire community, and many of the central development method people (Martin Fowler, Alistair Cockburn, Ron Jeffries) are using Ruby. Some of them are also using Java, but Java is slower in technology spreading and has more 'competiton'.
• Learn about the interaction between the relational model and unit tests; I know the negative interactions between SQL and unit tests, but I know that some aspects of those problems are pure SQL problems - and I want to see how much of them are.

None of the things I hope to achieve are likely to happen through a Java implementation. Even if a Java implementation should replace SQL, that would not make my world significantly better. For me, having to use a language with "training wheels" (required type annotations) and the resulting limitations would be even more of a problem as having to deal with the limitations of the SQL model. Groovy looks like it might rememedy this, but it ain't there yet - and Ruby is available today :-)

Eivind.

SQL has a strong foundation

[spagetti_code]

SQL is based on set theory - that is it has a strong underlying foundation. Using sets permits large scale manipulation of data using relatively simple constructs (admittedly getting a little ugly with all the modern addons).

Nothing that I have seen has such a strong basis. This was brought home to me quiet a few years ago when I was programming a reasonable size/complexity database (over 100 tables, millions of rows, untold triggers (pre-DRI days), stored procs, views etc) in Sybase SQLServer. In the next cubicle was a guy programming in Progress. One day I wrote a std query that crossed 4 or 5 tables and modified a large set of data - minutes to write, seconds to run. We were comparing notes and he was amazed - he said that he needed a large amount of code with nested this and for loop that to achieve the same end.

Point of this nostalgia trip is that when someone comes up with a system with a stronger theoretical base, I'll be very happy. But not yet. SQLs failing are in implementation, not concept.

Water... uphill?

[Piranhaa]

Meh, Im sure in small amounts water isn't all that difficult to push uphill...

If you need an object store...

[Anonymous Coward]

/sigh

Another article by someone that doesn't really understand what a database is or what a database is not.

SQL is a horribly complex system for doing set algebra, and it does an astonishingly good job at it.

Please don't clamor for a change just because you don't understand the full power of this amazing tool.

functional programming to the rescue

[Estanislao Mart�nez]

HaskellDB
SchemeQL

new approaches

[axelnissim]

I have been really intrigued since some months ago about how to represent a network, meaning a model of an undirected unweighted graph, a la friendster. Of course there are some ways to do it, but does anyone know some "SQL direct2 way to do it efficiently and handle the kind of operations required, like BFS? How can one make an adjacency table without duplicating all Id's? I kind of solved the problem by stating a rule that any adjacency should always go from the lesser Id Number to the biggest one, meaning that a relationship between nodes 8967 and 4567 would focefully be saved as Id1=4567 Id2=8967 and never backwards. It indeed reduced the number of queries I needed to wun in order to get a depth 4 BFS. Has anyone another approach that could be worked out on SQL, specifically on MySql?

you're setting up a strawman

[Estanislao Mart�nez]

Contrary to what you're assuming, the people in question do not propose that NULL should be comparable to non-NULL values in SQL. What they propose is doing away completely with SQL's NULL semantics, and putting something substantially different in place (or maybe even nothing).

Re:you're setting up a strawman

[Magickcat]

I imagine they do indeed propose to do away with null, however the article doesn't argue why null should be done away with. Instead, it criticises null's functionality whilst misunderstanding (or perhaps misrepresenting) it's correct syntax and use. So it's not a compelling arguement.

Re:you're setting up a strawman

[Estanislao Mart�nez]

[The article] criticises null's functionality whilst misunderstanding (or perhaps misrepresenting) it's correct syntax and use.

*sigh*

There is no misrepresentation or misunderstanding at all. This is *exactly* what the strawman is that you've set up.

Re:you're setting up a strawman

[Anonymous Coward]

*sigh*- unfortunately, not.

RTFA.

Re:you're setting up a strawman

[Anonymous Coward]

Habla usted inglés hombre?

Not the first.

[leandrod]

It is not true that no one ever did anything with Date's and Darwen's ideas on a relational language. Check, for instance, the category I edit at the Open Directory, or even Darwen's own The Third Manifesto website with its list of projects.

Probably the poster is confused about the nature of Tutorial D. As it names indicate, it is but an example of a possible 'D', and one targeted at instruction at that. This accounts for its COBOLishness. It is possible to implement a non-Tutorial D that is completely faithful to the Relational Model and the Third Manifesto, yet has a distinct flavour.

For example, Alphora Dataphor implements D4, which was a compliant D until having had to incorporate SQL NULLs quite recently, and it has a Pascal flavour to it; Opus and Duro are C-like; there was a guy wanting to implement a C#-like D-flat language; Alfredo Novoa is implementing Tutorial D itself in MS.Net; and so on.

By the way, it is interesting that until now the more ambitious projects, that seem to be Alphora's and Mr Novoa's, are in MS .Net. Time for the free software community to rise to the challenge!

Hierarchies

[ergo98]

One technique (albeit the examples are specific to SQL Server, the technique works elsewhere as well).

http://www.yafla.com/papers/sqlhierarchies/sqlhier archies.htm

While Oracle and SQL Server 2005 have hierarchical commands, they are in actuality recursive function calls and thus much slower than most database set logic.

Re:Pushing water uphill

[Anonymous Coward]

Doesn't anyone have video of that thing on the web yet?

Re: not true

[Anonymous Coward]

a correlated subquery in the WHERE clause will do the job without nulls:

SELECT * FROM T1
WHERE NOT EXISTS (
SELECT * FROM T2
WHERE T1.ID = T2.ID)

(some dialects also offer an IN () syntax)

vs:

SELECT T1.* FROM T1
LEFT JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL

Some might argue the correlated subquery is cleaner than the left join, since you aren't SELECTing any columns from the LEFT JOINed table, just using it to filter for NULLs, so why include it in the FROM clause to begin with?

I tend to agree that NULLs are a bad idea. I create my tables with a NOT NULL constraint, and then enforce dummy values for "Unknown" and "Not applicable" in the stored procedures & application code.

The usefulness of NULLs in the example above is more an artifact of SQL than a solid argument for allowing them in the language.

So...

[FunOne]

Why don't you participate in the development in one of the OSS database systems (like Postgres) to develop a CREATE TEMPRORARY VIEW syntax. Shouldn't be too hard to do, just make it valid for the current session (like a temporary table).

Bingo, there is your 'variable' functionality without inventing a whole new language.

Re:So...

[Tablizer]

Why don't you participate in the development in one of the OSS database systems (like Postgres) to [add temporary views] Bingo, there is your 'variable' functionality without inventing a whole new language.

1. It is not that likely that I will get a chance to use it at work.

2. SQL stinks in other ways that I would like to see fixed. In other words, I think it is time to explore a complete overhaul. Why be stuck with a "good enough" language forever? We are finally getting away from COBOL as the dominent biz language, so how about we work to get away from its relational cousin known as SQL?

They keep tacking stuff onto COBOL to try to modernize it, but the result is a language only a mother can love. They are even adding OOP to it.

SQL is easy

[Anonymous Coward]

You just have to expand your mind a teensy bit. You can do with a single line of SQL what may take several pages to do with an object-oriented language. I can understand how someone who isn't used to that much power may find SQL a bit confusing at first, but with a little effort and experimentation, it starts to become second nature.

Not that it really matters to ME. I took a test for my current job that included a few very basic SQL queries. Not only did I ace it, but I was later told that most of the other candidates who claimed to know SQL wound up handing the test back blank. So, your refusal to adapt to a new and powerful tool is giving me a distinct competitive advantage. Perhaps I should just say "thank you."

--A/C

Turing completeness

[vlad_petric]

Yes, there are things you can't do in SQL. Most mentioned example is transitive closure.

As opposed to regular programming languages, SQL is not Turing-complete, meaning that there are computations that can be expressed in a Turing-machine language, but not in SQL

Turing completeness does come for a price though - it's undecidable whether the execution of the program will even finish (instead of infinitely looping). SQL OTOH is guaranteed to complete in poly time of the input dataset. Different tasks - different hammers

Query-By-Example

[Anonymous Coward]

What? No love for Query By Example? I feel so lonely :(

Come one come all

[mikefe]

If you missed your chance in the early 20th century, now you ladies have a second chance to have a Date with Darwen.

view columns, not just view tables

[Tablizer]

One thing that would be nice is "view columns". In other words, a calculated column (alias). Often one ends up making an entire view just to get a look-up of one column, such as a city description from a city ID. If one could create a virtual column on an *existing* table, it could simplify the view catalog and changes.

Vendor Lock-in is a myth for me

[mcrbids]

I use PostgreSQL for all my DB stuff. Comparing it to MySQL is like comparing a mini-pickup to an 18-wheeler. PostgreSQL ANSI support is good, and it's very stable.

When I write custom apps (pretty much all I do) I write to the full capabilities of PostgreSQL. It's "man enough" to handle the biggest projects I'll likely ever see, and I'm not worried that the vendor will up and leave, since there isn't one.

In fact, PostgreSQL has had an interesting history in that the primary supporters of it have changed several times. PostgreSQL has weathered them all with grace, and remains today an excellent database package with a good, active community and quality developers.

So, I don't worry about JDBC, I don't write to cross platform, I could give a whit about Oracle or DB2 or whatever, but I don't. PostgreSQL is free, plenty good enough, and it won't go away.

Why bother trying to make my application portable?

PS: The article's example about null is stupid. Null = "I don't know". In that context, how could you include values of " 47" if you don't know what the value should be for a particular record?

I'm interested in this new DB language, if it actually offers a real benefit - but the article does a lousy job of getting me excited about it...

Re:Vendor Lock-in is a myth for me

[idlemachine]

The article's example about null is stupid. Null = "I don't know". In that context, how could you include values of " 47" if you don't know what the value should be for a particular record?

The actual example was "all entries where field X is not equal to 47", so in SQL that would be "SELECT * FROM Table WHERE X 47".

The argument is that because null values aren't considered to be comparable to non-null values, no record where the field X is null would be returned. For those records, X isn't 47...which is why the current syntax is considered to be imprecise.

Basically, if you have to mentally add an implicit "AND X IS NOT NULL" to every condition, wouldn't it be better to make everything explicit and clear instead?

(And yeah, this was a lousy article and thread...the last time Tutorial D was covered here - all of, what, a month ago? - I was a lot more inclined to read up on it... The snide editorials in the article summaries ("although we think it would be like pushing water uphill though") certainly don't help.)

Re:Vendor Lock-in is a myth for me

[mborland]

Hear, hear.

Postgres has been able to handle every problem I've slung at it, as you say, with grace. The value of the product to me is that they follow standards and have provided baseline RDBMS functionality (transactions, etc.) for a long time. It's not always as sophisticated as some databases (doesn't have all the storage options that say, Oracle has) but even for the enterprise systems I'm working with, this is not an issue.

The complaints about null are pointless. If you want to include/exclude null...just say so in your query! The complaint seems to be that of someone in their first week of SQL training (or starting a logic course). Yeah, it may not make 'common' sense, but only because commonly people don't face the concept of null, which is in fact very important in either procedural or SQL programming.

As for application portability...well, I used to really aim for DB portability. And my guess is that well over 95% of my queries are portable (the remainder would be easy to hunt down if necessary). But frankly, sometimes you have to plant your flag and say 'this is what language I'm programming in' or 'we use this database because it works, dammit!' I'm proud to do so with Postgres.

Re:Vendor Lock-in is a myth for me

[ahmusch]

Well, when working on a COTS product, vendor lock-in is a real problem. Until, of course, you abstract all the database access to views to hide from the user the native calls required (like join syntax, and date conversion, and the like).

Re:Vendor Lock-in is a myth for me

[kpharmer]

> Basically, if you have to mentally add an implicit "AND X IS NOT NULL" to every condition, wouldn't it be better to make everything explicit and clear instead?

The wacky part is using NULLs as a primary excuse to develop another language. First off - NULLs are optional in relational databases. Don't like them? Fine, don't use them. declare your columns 'NOT NULL'. It's that easy.

Secondly, most work-arounds for unknown data suck:

* The easy ones involve keeping an 'unknown' value row in most of your tables. That works great - except for high cardinality columns that aren't lookups - like monetary fields, etc.

* the more common easy work-around is to reserve some value as NULL (2000/01/01 for a date, -1 for an integer column, 'n/a' for a varchar, etc). This really sucks - not only do you have to exclude this from your queries, but you need to know exactly what value to check for, and it's very likely that you'll need a variety of values for each type.

* the approach I've seen most often by the more skilled of the anti-null crowd involves the creation of more tables - in order to isolate the nullable columns onto other tables that have condition rows. That's ok - but it involves an *explosion* in the size & complexity of the data model. There are some benefits - like it may do a better job of explicitely describing the subject process. But the downside - is that we already *denormalize* (prejoin) many tables together for better performance - primarily for reporting & analysis. Of course, the anti-null crowd also believes that denormalization is a sin - and that database vendors could theoretically provide great performance without denormalization. Unfortunately, their supporting analysis is based upon transactional rather than analytic systems. Ultimately, this approach looks like something that was baked up by people who might work on database software, but don't actually build real-world systems using it.

Re:Vendor Lock-in is a myth for me

[Eivind Eklund]

NULL looks like something to complain about for a week after you learn databases. Then it doesn't look like something to complain about for the next five or ten years where you work with databases. Then you start to get interested in modelling and database optimization and query API designs, and NULL becomes something to complain about again.

All these comments about "NULL is not a problem, you lack understanding of SQL" or "SQL is a not a problem, you just don't understand it" are quite insulting.

Most of us that criticize SQL know it quite well. We know its history (did you know that SQL was selected as standard because there were two warring factions with their own agendas, and SQL was brought forth as a third alternative that everybody agreed was worse than both, but which was politically possible to accept?), we know the idosyncracies of NULL (how it blocks us from doing the easy transforms of the query trees, because there suddenly isn't available a sensible NOT operation - NOT A > B is different from A And we think we may know how to fix some of the problems, so we're trying to do some work in that direction, to see if we're right.

Unless you think SQL is perfect, you should be cheering.

Eivind.

Theory and practice

[einhverfr]

Unlike SQL, in the mathematical model reasoning about such data is done in two-valued predicate logic (that is, without a null value), meaning there are two possible evaluations for each proposition: either true or false. The data is operated upon by means of a relational calculus and algebra.

In other words, it assumes that all values are definite (i.e. in SQL terms, NOT NULL) this will not fit all databases (i.e. how do you handle missing or unknown information). Null is a design feature of SQL designed to handle the real-world issue of unknown values, but does not really belong on the underlying mathematical model.

The relational data model permits the designer to create a consistent logical model of the information to be stored. This logical model can be refined through a process of database normalization. A database built on the pure relational model would be entirely normalized. The access plans and other implementation and operation details are handled by the DBMS engine, and should not be reflected in the logical model. This contrasts with common practice for SQL DBMSs in which performance tuning often requires changes to the logical model.

Ok, you have a couple issues here and they are completely practical.

First, one can create completely portable code in SQL which is completely normalized to at least third normal form (higher normal forms seem to be available for certain specialized uses). This does not always mean that it will perform ideally because of two issues:

1) Certain constraints on I/O bandwidth may restrict performance on joins

2) Query planners are not going to be perfect because the mathematics describe how the data fits together, not how to get it fast.... The query planner may have to choose between many different options for retrieving the data. So...

Usually, IMO, performance tuning, when it requires altering the storage schema, is done using one of two methods:

1) Denormalization-- essentially saving joined inforamtion in a single table as a way of saving page scans. this alters the relational logic, but does not break it.

2) Higher normal forms-- f. ex. breaking off specific MVD's onto separate tables so as to make specific searches faster (indexes make this less important, but in certain circumstances, it can be helpful). Note that this does also alter the logical layer but does not break it.

3) Summary tables... Quick hack but is useful when the summary don't need real-time statistics.

Re:Theory and practice

[insac]

> Usually, IMO, performance tuning, when it requires altering the storage schema, is done using one of two methods: > 1) Denormalization > 2) Higher normal forms > 3) Summary tables I agree with you if you mean that these are the tricks the (optimal) RDBMS could use "under the cover" to optimize the access plan (making them "transparent" to the application). Let's take the "Denormalization" as an example: we have two entities A and B that are loaded once a day with a batch. We know that they're mostly joined together in the queries. My idea of an "optimal" RDBMS is that I can define the two entities and the application can query them individually or joined together. Then I decide to start the "tuning" phase and I give the RDBMS an "hint" to change the physical layout of the data and keep them just in a single "data store". I keep inserting tuple in A,B entities and the RDBMS would keep the "abstraction" of two indipendent entities. The joined query (that is the more frequent) can get the data from the 2 entities from the same blocks. If the application evolves in a way that the individual queries become more frequent I'd have to "revoke" the hint and separate the datastores WITHOUT changing the application. The SQL RDBMS are beginning to have these features (what I describe here is more or less the Oracle CLUSTERED TABLE) but on other databases the solution is to change the logical schema when they just want to change the phisical layout of the data.

Re:Theory and practice

[insac]

Sorry... I should have previewed the message..

Re:Theory and practice

[einhverfr]

Note that denormalization is becoming less and less common with the development of reliable hash joins, etc. and higher normal forms are becoming more common in large data warehousing applications.

Then you can use views to provide a denormalized illusion to your application ;-)

Re:Theory and practice

[einhverfr]

Note that it is less efficient but also possible to use views to provide a normalized illusion to your application when comparing it to a denormalized database....

alternatives

[fionbio]

Lisps allow usage of SQL-like syntax inside programs without stuff like "SELECT X FROM Y WHERE Z = ? ORDER BY A" or "SELECT X FROM Y WHERE Z = '" + something + "' ORDER BY A". Look here and here.

Re:alternatives

[fionbio]

BTW, I think this is much better than Embedded SQL which makes programs look like mess (for those who don't think that Lisp programs are mess anyway :), and can be used together with Lisp macros.

OODBs versus RDBs

[Tablizer]

Maybe try an object-oriented database....Relational databases aren't the only game in town

Object DB's don't have enough solid theory behind them. There is too much inconsistency and lack of consensus in the attempts to formalize them.

It may be because one man, Dr. Codd, created relational theory, and thus there is a narrow set of principles to follow. OO has yet to find a Great Consolidator of similar caliber. (I am skeptical it will happen, but won't rule it out.)

OO-DB's tend to resemble the type of systems that Dr. Codd was actually trying to "fix" when he came up with relational theory. Thus, OO-DB's tend to seem "primitive" to relational fans.

Part of the problem is that database themselves run counter to "pure" OO encapsulation. Encapsulation dictates and/or allows each entity to define and handle its own operations. This creates at least two problems:

1. There is no guarentee that the operations will be compatible or consistent across classes. One class might have a Find operation, but another call it Search, for example.

2. Encapsulation requires that a single entity (class) handle its own implimentation. However, automated optimization requires the implementation be consistent and known to something outside the entity. In other words, implementation efficiency coordination breaks encapsulation. Objects can't be selfish about their implementation.

Attempts to solve these tend to make it turn into relational in the end anyhow, making it seem futile to keep OO-ness.

DO NOT

[Anonymous Coward]

do not place the client logic in the database!

do not place the client logic in the database!

do not place the client logic in the database!

*runs around in circles*

Not So Fast!

[Anonymous Coward]

Your statement is inaccurate. You sure-as-hell can perform this operation in D without requiring any NULLs. The problem is that it requires a developer to understand relational theory a great deal more than most currently do. Please check the theory before making assumptions.

Tutorial D has already been done.. Alphora

[sb68]

Tutorial D with a truly relational rdbms has already been implemented. The company is Alphora. The product is Dataphor. see http://www.alphora.com/

OQL

[Cygnus78]

But what's happening with OQL ?

If you're sensible, you're never touching raw SQL

[Julian Morrison]

Talking SQL nowadays strikes me as being like talking raw assembler. You can do it, the tools are even rather nice nowadays, but any sane developer would use a higher level wrapper.

Like for instance, Hibernate. It does serialize/reconstitute, handles nested objects and data structures, and it's very nearly as easy as programming with regular heap-allocated objects. In any big app if you didn't use some such, you'd end up reimplementing it.

I see no reason wrappers like Hibernate have to backend onto SQL and only SQL. They could as easily emit and control this "Tutorial D" language.

Re:The shortcomings of SQL - Postgres

[Unordained]

Interbase/Firebird also supported some other language ... GDML, was it? Documentation

As I recall, it's all but impossible to get to anymore. As for multiple languages in the same engine, the SQL support has multiple dialects available, with slightly different support for certain datatypes (particularly date/time-related.) So there's a bit of that already, though not tons.

Business system 12 for an example of yet another language in early systems.

The SQL standard already features recursion

[tarvin]

Per Wigren wrote:

Hopefully it will make it into the ANSI standard sometime because it's really useful!

The SQL standard (since SQL:1999) already specifies recursive SQL, through its WITH RECURSIVE construct (optional feature ID T131, "Recursive query"). You will sometimes see people referring to it using the name "common table expressions".

DB2 implements it, and it seems that SQL Server 2005 will also feature it.

Here's an article describing differences between WITH RECURSIVE and CONNECT BY.

Tutorial D is just one implementation of D.

[akc]

If you follow the links in the article, you will get to the original article from the guy who has written this software. Tutorial D is just one implementation of the 'D@ specification. The reason it is called Tutorial is that it is not an enterprise strength version, but rather one for learning and experimental purposes.

Domino is a great app but missunderstood.

[patrixx]

Notes/Domino today is like the Amiga in the eighties and nineties - A great system but terribly missunderstood.

TopicMaps Query Language - yet another alternative

[CmdrGoatCheese]

Yeat another alternative to SQL is the TopicMap QueryLanguage (TMQL). Topicmaps is a general format for databases, much like RDF and OWL. There is some work in progress on the forthcoming iso standard TopicMaps Query Language. The drafts suggests a hybrid between prolog/datalog and sql.

Lotus Domino is fundamentally different to SQL

[dominux]

but I for one won't be going back to your SQL overlord. In RDBMS systems the fundamental element which stores data is the table, that is the thing that is real. There are views or queries which are virtual and there are records which only make sense with reference to the table they are part of. In Domino the fundamental element of data is the document, this is roughly equivalent to the level of a record in SQL. Basically any Domino database can be thought of as a collection of independent records, these can be displayed in views, which look a bit like tables, but only in the sense that a query in SQL looks like a table, views are virtual, but they are indexed so they are seriously quick. The advantages of this way of thinking is that documents (records) are not constrained by the restrictions of the table they happen to be in, I can add a field to any document without breaking anything, I don't need to redesign tables.
In addition to that the datatypes are much simpler and more powerful, there is no distinction between numeric data types, they are all stored as 8 byte floats, text fields are all variable length up to 15k, any field can store an array (in fact single value fields are stored as a single element array), and there are rich text fields which can store any amount of stuff. Overall this is a way more powerful toolkit than an RDBMS for most tasks in most businesses. That isn't to say it is a magic bullet which can replace your ERP, but for stuff I come across it is better.

using ENGLISH terms

[SAPHRguru]

English terms are only beneficial to english speakers....
SELECT,, UNION, DELETE, WHERE, etc.. mean little to non english speakers.... (and may even be more confusing!)
Why do syntactic terms have to be meaningful to everyone - all that happens is that newvbies imbue terms with invalid semantics-- which they then need to unlearn!

Mod Up

[Anonymous Coward]

Someone please mod up. Absolutely awesome.

Valentina is worth a look

[CoolBru]

it's an interesting mix of SQL and OO interfaces. Version 2.0 (in development) has full SQL 92 support, but you can also treat the DB as a bunch of objects from just about any environment you're likely to encounter. It's intending to support OO things like inheritance, while retaining SQL compatibility.
It's also staggeringly fast - I do 5-term ORs on a 2Gb, 20 million record DB in 0.1 sec on a P3/600 with low RAM. http://www.paradigmasoft.com/ I'm just a contented user.

Re:Grammar?

[Anonymous Coward]

DELETE FROM tblUsers WHERE UID=634315;

Re:Grammar?

[LiquidCoooled]

SQL ERROR 234833: You must be logged in for this operation to proceed.

A big SQL problem...

[Anonymous Coward]

IMHO, is that it's poorly understood by many developers. Joe Average learns how to program procedurally, picks up some SQL and doesn't change his approach.

At worst, I've seen developers suck a result set into a memory structure and then pass over it because they don't understand aggregates. I've seen fairly experienced (and otherwise well-thought of developers) perform multiple joins to the same table because they're unaware of how to avoid this. Personally, it's unusual for someone to use UNION correctly (typically UNION ALL is actually the intention, but they don't know this, and don't know the distinction or the impact of the wrong decision).

More generally, even databases aren't as well understood as they should be. From experience asking for a definition of isolation within a RDBMS in an interview raises the bar high enough that this question alone drops (well) over 95% of candidates.

If a SQL replacement help address this, that's only a good thing. I'm still wary of silver-bullets; a new syntax approach might help (although learning hows null works isn't that difficult; joins are not hard - buy a book and learn something properly). Meanwhile, there's little than can't be achieved in SQL directly (and that's without T-SQL or PL/SQL extensions or even cursors).

Null !=0 for scientific data

[Proteus]

Zero is implying that there is a value there and that it is in fact the number zero. Null would imply that no value ever existed, zero or otherwise.

One place this is particularly useful is in recording scientific data. A value of 0.00 means a measuremet was taken, and that measurement is zero. A value of NULL means no measurement was taken. That is the primary difference between 0 and NULL: while both "false", NULL works as an undefined value.

In all of my db designs I try to avoid nulls unless absolutely neccessary.

Probably a sound practice. NULLs should only be allowed where the lack of data is significant.

Same house, new paint?

[Baavgai]

I think all the "let's go improve on SQL" folks are missing the point. Indeed, the article touched on it but didn't understand it.

Databases are about storing and retrieving DATA. Tables with rows in a fixed structure are an excellent way of maintaining data. Using key values to associate one collection of data with another is a logical way to approach organization of resources.

Now, you don't like the language ( SQL ) that's a standard for manipulating that data? Why? Because there are huge joins, poor logic to retrieve information, etc. Sorry, that's implementation and has little to do with DATA. If you have to join 30 tables to get some useful information, you're asking wrong the question. The right question is, "is the information there, is it available and understandable, how do I present it in a timely fashion?"

Any system that functions on nightmarish SQL constructs is only going to give way to some other "alternative" that has similar problems.

Now, how will this odd math centric "Tutorial D" store it's data? In tables, maybe? That's called a front end. That's what developers are for, to abstract storage into function representations. End users never need see SQL, just as they'd never see this. So, what's new?

Tutorial D?

[chaoticset]

Didn't he drop some tracks with DRM and the D-bomb?

Datalog Programming In XSB

[Baldrson]

You might want to check out datalog programming in XSB. XSB uses "tabling", which is a form of memoization of prior calls, to optimize datalog programming.

Re:deprecated?

[zoglmannk]

Out of curiosity, what is deprecated?

After searching google I haven't found any SQL, Oracle functions, or syntax extenstions being deprecated. I don't doubt that it is possible, but I am curious as to specifically what you are refering to and to which version(s) of Oracle it applies to.

Lastly even if something is being depreicated, I'm sure it will be around till the end of time. I'm not using anything too crazy.

I think I would change careers....

[CausticPuppy]

I think I would change careers if I ever had to write a statement like this:

SELECT * FROM CUSTOMERDETAILS C, CUSTOMERORDERS O WHERE CERTAINLY (O.ORDERID = 500) AND PERHAPS (C.CUSTTYPE = 4) AND JUST_SO_HAPPENS(O.CUSTID = C.CUSTID) AND (O.COMPLETEDATE MIGHT BE UNKNOWN OR O.STATUS IS NOT NECESSARILY 0) PLEASE ORDER BY O.ORDERNUMBER IF NOT TOO MUCH TROUBLE

Derby, not Derbyshire

[Anonymous Coward]

you insensitive clods! ;-)

Re:deprecated?

[will_die]

The use of (+) for joins has been phased out of Oracle at around 8i, and then deprecated in 9i. Also while decode has not been offical deprecated it has been strongly recommended that you use CASE instead.
example of new join format

Re:Grammar?

[Anonymous Coward]

perhaps... your mom!

What is all this shit

[Anonymous Coward]

What is all this shit about SQL is clunky with JOINS and that there has to be a better way.

How about this way: LEARN HOW TO PROPERLY CONSTRUCT A DATABASE.

Seriously, I been programming in SQL for years now and I have to say that it is easy as hell to get my data. Yeah I have a lot of table that are relational to each other and what not, but I use a lot of view in order to get the data I need. I only have to write the view once when I need to join 10 tables together and then just query that view from now on.

A lot of the "shortcomings" that people are saying here of the SQL language really has to do with the shortcomings of the DBA. If you don't know how to use VIEWS, INDEXES, UDFs and constuct your database properly, then you shouldn't be building them in the first place.

And just a note about the "D" language. It looks too friggin cryptic and hard to understand. SQL to the most straight forward language I have seen. With just a look, a moron can know what your doing to the tables. "D" looks to much like C++. If I wanted to use a C++ style language then I would use flatfile and parsers.

System 1022 and System 1032

[kjcole]

On the DECsystem-10 and then later on the VAX, we used a relational database with a query/procedural language that non-technical people could actually use. And the techincal folks liked it as well. No need for a GUI for those who could understand basic English. To find records, the command was ... FIND (which did not mean "find and print"). To print records, the command was, come on now... Yes, PRINT! ;-) And none of the redundancy of SQL. There was an attitude that if you were working with a table or group of tables, that you'd probably continue working with them for several commands or queries. This persistence eliminates the need to constantly add "FROM bla-bla-bla" (and often "WHERE bla-bla-bla") to every command.

Short comparison (if I recall correctly):

SQL

SELECT colname2, colname5, colname32
FROM tablename
WHERE colnameX = valueY;
UPDATE tablename
SET colname2 = valueY
WHERE colname2 = valueY;
SELECT colname42, colname20, colname9
FROM tablename
WHERE colname2 = valueY;

S1032

OPEN tablename
FIND colnameX EQ valueY
PRINT colname2, colname5, colname32
CHANGE colname2 = valueY
PRINT colname42, colname20, colname9

Re:System 1022 and System 1032

[kjcole]

And it had "joins" too, using a command named MAP. Again, from memory, assuming two tables had key fields with the same names:

MAP table1 TO table2 VIA colname1, colname2

(It was relatively simple if the colmn names weren't the same also.)

Re:deprecated?

[zoglmannk]

Thanks for the info. I am a little amazed that the + operator is being deprecated. At the same time I am surprised that Oracle 8 and previous versions did not allow ANSI SQL join operations. The only time that I use the ANSI join is when doing a full outer join, which is rarely needed.

I've used both syntaxes and I prefer the + operator. It takes less characters and is usually more clear. It still works in 10g, so I doubt that it is going away any time soon.

Re:Who remembers Knowledgeman? So, do these

[davidsyes]

"overlookers" of Progress get the "'Darwen' Award"? DOH!!!!!

I Have A Benchmarking Question.

[LifesABeach]

What is the difference between, lets say, mySQL, OpenCyc, and 'D?

Is there a "Ben Franklan" score sheet on 'D vs. mySQL vs. SQL?

For me, this would be a web site worth visiting.

[Arbitrary subject]

[warrax_666]

this is a perfect example of why you should NOT use a boolean for a tristate variable. You are abusing a boolean (which can also be NULL) to contain an enum with three different states (Yes, No, SmackedMeInTheFace). What you actually want is proper enumeration support. That would force people to be explicit about precisely what values are allowed and about how/when they can be combined.

I think most people's gripe with NULL is that things become horribly undefined when you have automatic coersions and start to applying boolean logic.

Re:[Arbitrary subject]

[Kell_pt]

>> this is a perfect example of why you should NOT use a boolean for a tristate variable

Precisely, totally agree! That's what nulls entail in this case: you end up representing something with a boolean that is NOT a true/false variable.

In most cases that you'd use a NULL, you can use similar approaches. There might be situations where NULLs are handy, but in most cases, those automatic handlings aren't worth the possible trouble.

As for NULLs in foreign keys, just add a value in the other table that represents a no-value.

Re:If you're sensible, you're never touching raw S

[PurplePhase]

Okay, I need to get more informed about Hibernate et al., but it has so far seemed like an object generator based on tables. My current project has been using XML/XSL to generate Vo/Dao/DaoFactory objects for 4 years. Meh, it has it's uses.

The problem with such frameworks is:
1) One VO class per table, 1 object per row = tons of objects
2) Returning more fields than needed = more objects instantiated
3) joins = separate query/DB connection for every related^N table, esp. on proxy/delayed-instantiation objects

It seems that for any amount of performance on a big-system DB you need SQL. Then again maybe I just need to read more Hibernate and create new objects for every query - as long as searches can use conditionally-set fields that might work, or fields in the WHERE not actually returned in results, etc...

8-PP

Hello.

[warrax_666]

C was designed by programmers who wanted to create a versatile, powerful language that would get the job done

Only the last of those was actually a motivating factor for the creation of C. C was created specifically for implementing UNIX because no other "high-level"(*) langauge of the time could actually "get the job done". They simply weren't low-level enough. Sometimes you just need to be able to do horribly unsound things like unchecked typecasts, accessing memory directly, etc. However, almost no user-space software should ever need to do any of these things and should definitely be written in higher-level languages. Just check any security mailing list and compare the number of security problems stemming from improper C usage (unchecked buffers, improper casts, etc.) to 'real' security flaws (default passwords, lacking credential checks, etc.).

I don't know about development practises where you work, but liking a language is almost never a good criterion for choosing it. I would, however, accept "I need to do 'unsafe' things", or "I need to be really portable"(**) as valid reasons.

Oh, and C is by no means "versatile" or "powerful", just look at LISP or any ML dialect.

(*) Pascal et al. were considered high level at the time... personally I wouldn't consider anything that doesn't have 1st class closures to be "high-level".

(**) C is the most portable language currently in use. There are very few Turing Complete platforms which don't have a C compiler.

Re:I was about to ask the same thing. How I deal

[davidsyes]

with it.

This may seem simplistic, but the way I deal with null values is by considering where NULL is necessary to know, but not allowed by bog down my mind or schema. I create an additional column that is a "calculated field" In that calculated field is simlpy a calculation/formula that does something like this:

Example Fields (Yes, the database tool (Lotus Approach, in win98 in Win4Lin, running in Mandrake 10.0) I use lets me have spaces and mixed casing in the field names, AND I can use these in MySQL, too.

First Name | Family Name | DOB | Age |NullAge
Txt | Txt | Date | Calc | Calc

The text fields are whatever length I decide to set, usually 35 to 45 chars for family and given name, but sometimes 35 for middle, since some people have UK-like multi-hyphenated, triple/poly-syllabic heritage middle or surnames.

DOB is simply a date field, fixed in size.

"Age" is a calculated field that has a formula such as:

Today()-DOB or
Today()-(DOB)

When DOB is NULL (and in this case, today is 10/13/2004), then Approach returns 731867. Since nobody alive will have that number of days in their age, it's easy to have the "Age" field supplemented with yet another field that looks for ages beyond something I think sensible.

Let's suppose a DOB of 4/5/1965.

Age, using the formula:

Today()-DOB

returns 11436 (number of days)

Now, for example, I can have a calculated field called "Age Abnormal" check this.

"Age Abnormal" would have a formula such as this:

If (age >135, 'bad age', Age)

This simply means if the age is over 135 years, substitute text instead of the actualy age. (Astute observers will note that days will return until we fix the issue, further down...)

Alternatively, I can put this formula in the "Age" field instead of creating a separate, space-consuming field called "Age Abnormal". But I sometimes create these extra fields as a way to "parse" my mental logic and make troubleshooting much easier, at the expense of some used space. (When over-parsed, it is possible to use one of these as a dependency for other calculations, and if one is deleted in the name of disk space efficiency, a lot of troubleshooting may be necessary...unless good notes are on hand.)

So, I create another field called "Age Years", just to have it as a raw calculation, sitting there "just in case" I need it for some other calculations. (Yes, 365.25 may not be accurate for all years, and there may be some who'll have separate year tables serving as lookups, probably for financial scenarios where guessing or simple calcs are unacceptable...)

In Approach, this would work (for my purposes):

If ((right(today),4))-Age>135, 5, 2))

But, I really want dates' YEARS calculated on and displayed, not the raw number of days. So, I can use

As a separate field/column, I named it "Age 2"

If(right (today(),4) -Year (DOB) 135, 'bad age', Age/365.25)

---------Opinionated stuff----------

Also, while I realize that DBAs are accustomed to using "Emp_FN" instead of "Emp FN" or "Emp First Name", I don't, because eventually it's not human friendly. I appreciate that in the early days disk space and performance were major issues, but I despise cryptic field names that end up being a pain for regular users, or for myself. It is pleasing that MySQL doesn't have a problem with my use of spaces in field names, but it did require me to change "Date Created" to something like "DteCreated" or "Dte Created", since date is a reserved or key word. Approach will let you use a keyword or any word you want up to a certain length, when using .dbf as the backend. I use it for .dbf for local desktop, but when I test forms in Approach from within w98 on the same box that MDK10 is running, I use MySQL running out of MDK. I rarely use MySQL as a service or process in win/win98.

Actually, it's a travesty that IBM won't port or allow dual-licensing or

Wow

[Unoti]

And if all that doesn't make the problems with NULL look small, then surely nothing can.

Re:Wow

[stephenbooth]

It's not so much a technical problem (anymore than anything based on a lookup would be) as a socialiological/political one. You have to be aware that your data domains are likely to change and design with that in mind. As a local government authority we have to be seen to be more equal ops than anyone else (weather we are more equal ops is a different matter, we have to be seen to be) but also due to the sheer scale of our systems combinations that are very low probability for other organisations are virtual certainties (if not actual certanties) for us. Supposing in the general population one in 10,000 people are in some way transgendered (I suspect it'd be more common than that but it's a good enough guess to illustrate the point) that means we'd have about 500 of them in our CRM database. Also because of the nature of the services we provide it's more likely that we'd need to know. Your local supermarket doesn't care if you're transgendered, they only care about what you put in your shopping trolley each week. We have to care if it means you're more or less likely to need certain services from us or if that might influence services those around them might need.

I think designing databases has taught me more about life and human diversity than anything else I've ever done, it's something you can do in isolation.

Stephen

Something tells me...

[cr0sh]

Any system that functions on nightmarish SQL constructs

...that if you have "nightmarish SQL constructs", someone didn't do a very good job of setting up and normalizing the tables properly in the first place...

Re:If you're sensible, you're never touching raw S

[Tablizer]

Talking SQL nowadays strikes me as being like talking raw assembler. You can do it, the tools are even rather nice nowadays, but any sane developer would use a higher level wrapper.

How exactly are you measuring "high level" and "low level"? The wrappers I often see others put around SQL are a lower-level abstraction than what SQL is. I agree that SQL has flaws, but it is NOT "low level".

Some OO developers like to wrap SQL to convert it to their favorite paradigm, not necessarily change the absolute abstraction level. It is translating across paradigms, not upping abstraction necessarily.

Let's not get into a battle over whose paradigm is more higher-level than the other.

I forgot the unknown propogation...

[Ayanami Rei]

i was focusing on the non-short-circuiting nature of it, which is critical.

Or Tutorial D could emit SQL

[rhizomania]

TutorialD could, with a few additions make a good procedural language. TutorialD could easily have a SQL backend. Infact some SQL databases can have another SQL database or CSV text files as a back end!

Having used Hibernate

[Julian Morrison]

...I can reassure you that a lot of the faults you describe don't apply.

- Class-per-table, object-per-row, but the query language is smart enough to let you return only those rows you care to access. That includes when it's emulating data structures like Lists and Maps, from which you can filter a subset. In addition you can optionally use session.iterate() which transparently returns just the IDs and then selects each row seperately, so as to keep the memory usage low.

- If you don't want all the fields of the row, You can avoid creating the row objects entirely and instead instruct Hibernate to return primitive wrapper objects (String, Integer et al) representing the subset of the row you ask for.

- Hibernate is smart enough to translate joins properly into a single select of SQL. It's also smart enough you mosty don't have to think about such low-level stuff as joins at all. You just navigate data structures and references.

- Unlike many object DBs, Hibernate has a proper query language, not just query-by-example. So you can say stuff like session.find("from Person p, where p.age = ? and p.employer.country = Countries.ENGLAND", age, Hibernate.INTEGER)

Re:Having used Hibernate

[PurplePhase]

Thanks for the insights - I will definitely have to make it part of my toolset, then, and prove all of your statements!

8-PP

Moo

[Chacham]

*Shortcoming* of SQL?

You've got to be kidding./

SQL is the *only* properly written language out there. No extras words (except perhaps "INTO" after "INSERT"). The addition of the ANSI OUTER JOIN synbtax is confusing though.

Please, please, please, don't change SQL.

relational databases are passe..

[rofthorax]

Why use them, why not make most of the schema
clientside instead of serverside, via ODBC/JDBC..
OR something similar.. Maybe I don't live in the optimal universe, but relational databases are not optimal unless you know how the searches perform..
How about this, a runtime average of query times,
on a per operation set basis.. I know Oracle probably won't like that.. Anything you know about the database you would have to get from database
classes.. If ever that's compromised many DBA's will lose their jobs..

Getting rid of NULLS by normalization.

[Kardamon]

Suppose you have a table like this:

PK Value_1 Value_2
-- ------- -------
01 abc def
02 efg hij
03 klm NULL
04 ghi jkl

You can normalize it like this:

Table_1 Table_2
PK Value_1 PK Value_2
-- ------- -- -------
01 abc 01 def
02 efg 02 hij
03 klm 04 jkl
04 ghi

Re:Getting rid of NULLS by normalization.

[jdray]

Wouldn't that run your index count way up, ultimately resulting in far, far higher storage and memory requirements?

Re:Getting rid of NULLS by normalization.

[Kardamon]

Thats why the relationists put so much importance in the distinction between the physical and the logical data model: the logical model can be normalized (without NULLs) and the physical model can put everything in one table containing NULLs for internal representation only.

High level / low level

[Julian Morrison]

The higher "level" a tool is, the more it operates in terms of the problem's abstractions. The lower, the more it operates in terms of its own internal featureset. So SQL with its task-irrelevant chatter about indexes and tables and whatnot is lower level than a wrapper layer which hides SQL behind purpose-designed data objects.