Subquery And Null

Last week, I completed a four-day Teradata Advanced Sql course offered by Teradata Australia Pty Ltd and sponsored by my employer. In this series, I will document some of the insights that I gained and the tricks of the trade that I learnt. Today, I will write about some gotchas associated with subqueries.

The following is a garden variety subquery that I write all the time:

SELECT *
FROM t1
WHERE some_col NOT IN (SELECT a_col FROM t2);

The above works as long as the subquery does not return a NULL in what is effectively a comma separated list of values within the pair of parenthesis. However, things go pear-shaped if the subquery returns one or more NULLs in the list. When that happens, the outer query returns nothing.

The outer query compares some_col with each value returned by the subquery.  Say, the subquery returns val1, val2 and NULL. For the outer query to return a row, the condition that needs to be true is this: 

some_col not equal to val1
AND some_col not equal to val2
AND some_col not equal to NULL;

Comparison with a NULL results in NULL, and so does ANDing a value with a NULL. Therefore, the preceding condition never becomes true, and the outer query does not return anything.

NULL is not an issue in the following query (it has 'IN' instead of 'NOT IN' in the WHERE clause):

SELECT *
FROM t1
WHERE some_col IN (SELECT a_col FROM t2);

In the above query, some_col needs to be equal to only one of the values returned by the subquery for the outer query to return a row. In other words, some_col gets ORed with the values returned by the subquery. Therefore, NULL is not an issue.

There are ways to circumvent the problems caused by NULLs in a subquery, and they will be discussed in the next installment of this series.

Returning At Night To Lumen Mountain

By Meng Haoran

A bell in the mountain-temple sounds the coming of night.
I hear people at the fishing-town stumble aboard the ferry,
While others follow the sand-bank to their homes along the river.
...I also take a boat and am bound for Lumen Mountain --
And soon the Lumen moonlight is piercing misty trees.
I have come, before I know it, upon an ancient hermitage,
The thatch door, the piney path, the solitude, the quiet,
Where a hermit lives and moves, never needing a companion.

Can Israel Folau Beat the 10k Rule?

Malcolm Gladwell, author of the international bestseller The Tipping Point (2000), pored through reams of research data compiled by economists, psychologists and social scientists to construct a simple formula for becoming a genius in his 2008 book Outliers: The Story of Success. Gladwell's own genius lies in teasing out previously unsuspected patterns and relationships from jumbles of arcane academic data, and weaving them into simple narratives accessible to the lay audience. His insights into the makings of a genius can be summarised as follows:

• True mastery in any complicated field requires 10,000 hours of conscious practice


• Geniuses such as Bill Gates, Bill Joy and the Beatles received their apprenticeships through a combination of good fortune and conscious effort


• The popular myth of a genius as a solitary figure fighting alone against the prevailing currents of society is just that, a myth. Most geniuses receive family support and community patronage during their formative years

A couple of weeks back, at the height of the media frenzy sparked by National Rugby League (NRL) superstar Israel Folau's six-million dollars defect to the rival Australian Football League (AFL), Channel 9's sports program Wide World of Sports re-played Folau's interview from a year back.

Many NRL fans regard Folau, a towering, powerfully-built young centre-winger whose gravity-defying jumps and electrifying bursts near the try line captured the imagination of NRL fans and excited the predatory instincts of AFL poachers, as a natural talent and born athlete. In the interview, Folau bristled at this notion, reminiscing about getting up at 5am in the mornings as a kid and being driven to trainings by his dad when most kids his age would have been sleeping like angels.

If Folau is not a natural talent, then I suppose no one is.

Another famous 'natural' talent, at least in the minds of sports fans, was Michael Jordan, who could perhaps teach even Folau a thing or two about gravity-defying jumps. However, many sports fans are unaware that Jordan could not even get picked for his high school basketball team. What Jordan did after being snubbed thus was practise, practise and practise until he had presumably clocked over 10,000 hours of conscious practice.

The operative word here is 'conscious'. Practice must be informed by a conscious, deliberate effort to improve, which more often than not entails seeking critical appraisals from mentors, coaches and peers, creating a positive feedback loop. This perhaps explains why many clubhouse chess players get stuck in a rut despite many years of weekend skirmishes, or most aspiring footy players fall by the wayside,  never graduating from park footy.

According to research, this 10k hours rule is quite general and applies across diverse non-trivial human pursuits such as music, mathematics, computer programming and writing.

Of British-Jamaican heritage, Gladwell, who currently writes for The New Yorker, is on the record saying that he completed his 10k writing apprenticeship working as a journalist, in which role he would have received feedback from experienced copy editors on his craft. He closes Outliers with his own chequered family history to buttress his thesis that family and the wider community play a vital role in the development and emergence of a genius.

Given that any non-trivial skills set requires 10k hours of practice to master, University of Sydney's David Anderson, a skills acquisition specialist who was quoted in a Sydney Morning Herald article on June 5-6, cast serious doubts on the ability of Folau to transfer his NRL skills to AFL, which is governed by a completely different set of rules and patterns of play.

Athletes with brute physical strength and ability to run short distances at explosive pace tend to thrive in NRL. AFL is more of an endurance sports requiring kicking skills absent in NRL and the temperament of a long distance runner. Then, there is that undefinable knack for reading the game, which separates elite players from merely good ones.

"They (NRL players) can run fast and they're big and strong, they can pass and catch and kick, but what they can't do is read the patterns of play and that puts them at a massive disadvantage," Anderson was quoted as saying. The article also pointed out the obvious: Folau and his fellow AFL convert Carmichael Hunt will simply not have enough time to log 10k hours of practice, which roughly takes around 10 years in real life, before the show begins.

Will Folau defy the 10k rule and prove his doubters wrong to have a productive AFL career, or will he, too, join the rank of other code-switching NRL stars such as Wendell Sailor, Lote Tuqiri and Timana Tahu, all of whom came back to NRL after disappointing stints at another rival footy code?  Only time will tell.

City2Surf 2010: Running for Fun & Charity

I am taking part in this year's City2Surf fun run to be held on Sunday, August 8. The run starts at Sydney's Hyde Park at the heart of the city and ends 14.1km later at Bondi Beach in the eastern suburbs. This will be my third participation in this event, which is regarded as the world's largest fun run. More than 75,000 people took part in it last year.

This year, I am raising funds for RSPCA, which is Australia's only truly national animal welfare organization. If you can, please help me raise funds for RSPCA via my HeroPage here. Your tax-deductible contributions will help RSPCA accept and take care of unwanted animals and investigate cases of animal cruelty.

Three Chinese Poets

After one of my friends made a favourable comment on the Tang poem posted in the previous entry, I picked up from my modestly-stocked bookshelf Vikram Seth's Three Chinese Poets, a translation of poems by the three major Tang Dynasty (618-907) poets, Wang Wei, Li Bai and Du Fu. I have this habit of signing and dating books after I buy them, and this is how I know that I purchased this hardcover edition on March 1, 1993, probably from one of my three favourite bookshops in Kathmandu.

When I visited the small Himalayan Book Centre opposite the old bus park at Bagh Bazaar, Kathmandu, in 2007 after an 8-year-long self-imposed antipodean exile, I was pleasantly surprised and moved when the elderly bookstore owner with thick glasses and slick oiled hair recognized me and enquired where I had been for so long!

Seth, who spent eleven years at Stanford University from 1975 to 1986 researching an economic doctoral thesis that he never completed, went to China to collect field data. While there, he honed his Mandarin. This must have inspired and emboldened him to translate the three premier classical Chinese poets later on.

Seth introduces the three near contemporary poets thus:  "The standard trichotomy of Wang Wei as Buddhist recluse, Li Bai as Taoist immortal and Du Fu as Confucian sage has been rejected by some critics as unsubtle and artificial, but it can act as a clarifying approximation for those approaching Chinese poetry of this period for the first time.

"Wang Wei's typical mood is that of aloneness, quiet, a retreat into nature and Buddhism. What one associates with him are running water, evening and dawn, bamboo, the absence of men's voices. The word 'empty' is almost his signature."

Enough talk. Here is a famous poem by Wang Wei as translated by Seth that distills the essence of the contemplative state of mind of a Buddhist hermit:

Birdsong Brook

Idly I watch cassia flowers fall.
Still is the night, empty the hill in Spring.
Up comes the moon, startling the mountain birds.
Once in a while in the Spring brook they sing.

The theme of aloneness and emptiness that Seth associates with Wang Wei is expressed masterfully in the following verse. In the introduction to the translations, Seth draws readers' attention to the contrast of meaning between lines three and four, and five and six, which was required by the strictures of the regulated form of the octet.

Living in the Hills: Impromptu Verses

I close my brushwood door in solitude
And face the vast sky as late sunlight falls.
The pine trees: cranes are nesting all around.
My wicker gate: a visitor seldom calls.
The tender bamboo's dusted with fresh powder.
Red lotuses strip off their former bloom.
Lamps shine out at the ford, and everywhere
The water-chestnut pickers wander home.

In terms of poetic sensibilities, Li Bai was the opposite of Wang Wei. In Seth's words, "Li Bai's poetry sparkles with zest, impulsiveness, exuberance, even at the risk of bombast and imbalance. Sword, horse, wine, gold, the moon, the Milky Way and impossibly large numbers are recurring features of his work. He attempts alchemically to transmute life through the intoxication of poetry or music or wine into delight and forgetfulness." This is what Seth means:

The Waterfall at Lu Shan

In sunshine, Censer Peak breathes purple mist.
A jutting stream, the cataract hangs in spray
Far off, then plunges down three thousand feet --
As if the sky had dropped the Milky Way.

'Bromance' was a staple of classical Chinese poetry long before Matt Damon and Ben Affleck pioneered it in Hollywood. Just like everywhere at the time, in classical China, travels were ponderous, long and fraught with many dangers, and, when friends parted, they could not be sure if they would ever see each other again. After one such parting, Li Bai wrote the following:

Parting at a Wineshop in Nanjing

Breeze bearing willow-cotton fills the shop with scent.
A Wu girl, pouring wine, exhorts us to drink up.
We Nanjing friends are here to see each other off.
Those who must go, and those who don't, each drains his cup.
Go ask the Yangtze, which of these two sooner ends:
Its waters flowing east - the love of parting friends.

Du Fu was born into an impoverished, cadet branch of the royal family, and, despite his prodigious poetic talents, did not pass the imperial civil service exams after repeated attempts (Empress Wu, a concubine of the founding Tang Dynasty Emperor Tai Zong, introduced verse composition into civil service exams during her 15 year reign, prompting a critic to observe that the Chinese thought poets made the best administrators). This effectively hobbled his Confucian aspirations to high office. He spent the rest of his life in penury and desperation, holding minor official posts and suffering horribly during a rebellion from which the Tang Dynasty never recovered.

Typically, the rebellion was started by a 'barbarian' general named An Lushan, who had managed to gain the trust of Emperor Ming Huang's favorite concubine Yang Guifei. When An Lushan marched on the imperial capital Changan, the court fled the capital. The emperor was powerless to save Yang Guifei from the murderous rage of court officials, who strangled her to death with a silk scarf. Whenever I mentioned this episode to my Chinese friends at university, all of them would automatically assume that I had learnt about it by watching Chinese TV soaps on the topic.

Seth writes: "Du Fu's poetry is informed by deeply suggestive and often sad reflections on society, history, the state and his own disturbed times, all central concerns of Confucianism. But what especially endears him to the Chinese is his wry self-deprecation combined with an intense compassion for the oppressed or dispossessed people of every kind in a time of poverty, famine and war."

The following poem is interpreted either as the universe's indifference to human suffering or life's capacity for renewal and growth in the face of wars and tragedies.

Spring Scene in Time of War

The state lies ruined; hills and streams survive.
Spring in the city; grass and leaves now thrive.
Moved by the times the flowers shed their dew.
The birds seem startled; they hate parting too.
The steady beacon fires are three months old.
A word from home is worth a ton of gold.
I scratch my white hair, which has grown so thin
It soon won't let me stick my hatpin in.

Du Fu was too poor to keep his family in the capital Changan. In the following poem, he pines for his family in a moonlit night in Changan:

Moonlit Night

In Fuzhou, far away, my wife is watching
The moon alone tonight, and my thoughts fill
With sadness for my children, who can't think
Of me here in Changan; they're too young still.
Her cloud-soft hair is moist with fragrant mist.
In the clear light her white arms sense the chill.
When will we feel the moonlight dry our tears,
Leaning together on our window-sill?

On Climbing Orchid Mountain In The Autumn To Zhang

By Meng Haoran

On a northern peak among white clouds
You have found your hermitage of peace;
And now, as I climb this mountain to see you,
High with the wildgeese flies my heart.
The quiet dusk might seem a little sad
If this autumn weather were not so brisk and clear;
I look down at the river bank, with homeward-bound villagers
Resting on the sand till the ferry returns;
There are trees at the horizon like a row of grasses
And against the river's rim an island like the moon
I hope that you will come and meet me, bringing a basket of wine --
And we'll celebrate together the Mountain Holiday.